having troble with Nulls and datetime

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

Transact-SQL (2000)

having troble with Nulls and datetime

Author

Topic

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2007-10-24 : 18:50:49

I have this problem that have been able to convert a field from varchar to datetime by doing the following:
SELECT CAST(LEFT([Imm_Date], 2) + '-' + SUBSTRING([Imm_Date], 3, 2) + '-' + SUBSTRING([Imm_Date], 5, 4)AS datetime)
END
FROM dbo.Trans

And this works, it puts it in datetime format just as I need. However,when it hits a null valuse it errors out. I get the following message: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Any ideas I would rally appreaciate
.

ITM

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-24 : 19:00:54

Use a CASE to do the concatenation only when NOT NULL.


SELECT CASE WHEN Imm_Date IS NOT NULL THEN CAST(LEFT([Imm_Date], 2) + '-' + SUBSTRING([Imm_Date], 3, 2) + '-' + SUBSTRING([Imm_Date], 5, 4)AS datetime)  ELSE NULL END
....

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-25 : 02:30:51

quote:
Originally posted by itmasterw

I have this problem that have been able to convert a field from varchar to datetime by doing the following:
SELECT CAST(LEFT([Imm_Date], 2) + '-' + SUBSTRING([Imm_Date], 3, 2) + '-' + SUBSTRING([Imm_Date], 5, 4)AS datetime)
END
FROM dbo.Trans

And this works, it puts it in datetime format just as I need. However,when it hits a null valuse it errors out. I get the following message: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Any ideas I would rally appreaciate
.

ITM

Always you should make sure that you express date values in YYYYMMDD HH:MM:SS format

Madhivanan

Failing to plan is Planning to fail

itmasterw
Yak Posting Veteran

90 Posts

Posted - 2007-10-25 : 09:10:17

No for some reason that is not working, I still get out of range. It is like nop matters what I try it jumps to the cast part. Because l;ike I said if I use this on a date field that iss in vachar form, it works; but if there is nulls in the field it does nto work. If you have any other ideas please let me know.
Thank you

ITM

MnTeddy
Starting Member

2 Posts

Posted - 2007-10-25 : 11:07:56

What are some sample values for Imm_Date?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources