DateTime erroring out - 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 2000 Forums

SQL Server Development (2000)

DateTime erroring out

Author

Topic

rkalyani
Starting Member

30 Posts

Posted - 2009-06-09 : 15:49:19

I get this error when I run this query
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

SELECT turnaround_time, Receive_Date, Sent_Date
From(Select Convert(datetime,ALSD) as receive_date,
Convert(datetime, dbo.DORD) as sent_date,
BCI_WEBII.dbo.DateDiffWeekdays(ALSD,DORD) AS turnaround_time
FROM app
WHERE ISDATE(ALSD)= 1 AND ISDATE(DORD) = 1 )t where Case when IsDate(Receive_Date)= 1 then Convert(DateTime, Receive_Date) End Between '01/01/2009' and '02/01/2009'
group by turnaround_time, Receive_Date, Sent_Date

Is there something else I should check?

Thank you,
kal30

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-06-09 : 16:06:51

try just selecting out the raw char values rather than converting them just to see what values are blowing up. isDate isn't always reliable as a guarantee that the value will convert.

Be One with the Optimizer
TG

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-10 : 00:29:13

u should have datetime in format mm/dd/yyyy or yyyy/mm//dd format for conversion of varchar field to datetime

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources