DatePart problem... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DatePart problem...

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2007-08-14 : 08:54:35

Hi I'm trying to write a query to identify dates which lie on a Sunday or Saturday.

set Dateformat DMY
SELECT Date
FROM TABLE_1
WHERE isdate(date) = 1 and datepart(dw,cast(Date as datetime)) in ('1','7')

Error:
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

My Date field is of type varchar(30) but is in this format: 22/02/2005 11:54:24

When I check for non-dates, I only return NULL and 'Y'

i.e. select * from TABle where isdate(date) = 0

It seems that my query using isdate(date) = 1 is still picking up the value Y hence the error....

Any ideas??

Thanks!

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-14 : 09:08:25

Try something like:

SET DATEFORMAT DMY
SELECT [Date]
FROM Table_1
WHERE [Date] LIKE '[0-3][0-9]/[0-1][0-9]/[0-9][0-9][0-9][0-9] [0-2][0-9]:[0-6][0-9]:[0-6][0-9]'
    AND DATEPART(dw, CAST([Date] AS datetime)) in ('1','7')

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-14 : 09:50:35

[code]
set dateformat DMY

select
a.DATE
from
TABLE_1 a
where
case
-- Exclude invalid dates
when isdate(a.DATE) is null or isdate(a.DATE) <> 1
then 0
-- Exclude Monday to Friday
when datediff(DD,-53690,a.DATE)%7 < 5
then 0
else 1
end = 1

[/code]

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-08-14 : 09:53:53

1 Always use proper DATETIME datatype to store dates

2 ISDATE() is not always reliable
SELECT ISDATE('2005'),ISDATE(2005)

3 Create a new column with DATETIME datatype and update that column from your varchar column and use the new column for all your date calculations

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources