datetime convert issue - 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

General SQL Server Forums

New to SQL Server Programming

datetime convert issue

Author

Topic

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-28 : 03:29:42

query : select distinct

cast(zedat+'-'+zedaj+'-'+zedam as smalldatetime )Data_of_Issue ,

from rhir.dbo.lqdandebrz

varchar(2)
zedat- day
zedam- month
zedaj- year

either way I switch those 3 column it give's me the over-flow error ... or when I get it right then the day column becomes the year value and I get a date like : 2027-06-08 ... and the day becomes the year...
If I switch the columns between them then it overflows..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 03:37:59

[code]select convert(smalldatetime, zedaj + zedam + zedat)[/code]

KH
[spoiler]Time is always against us[/spoiler]

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-28 : 04:09:22

quote:
Originally posted by khtan

select convert(smalldatetime, zedaj + zedam + zedat)

KH
[spoiler]Time is always against us[/spoiler]

Msg 296, Level 16, State 3, Line 2
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

like this it doesn't in either way i switch the columns :(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 04:13:06

looks like it is your data. The concatenation of these columns forms an invalid date. Check your data.

KH
[spoiler]Time is always against us[/spoiler]

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-28 : 04:14:35

the data appears to be valid ... there are no invalid entries... like 29.02.2009 ... or 31.06.2008 ....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 04:19:15

use ISDATE() to determine which rows give you the error


select *
from   yourtable
where  ISDATE(zedaj + zedam + zedat) <> 1

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-28 : 04:23:50

[code]
select
zedaj,
zedam,
zedat,
other_column
from rhir.dbo.lqdandebrz
where isdate(select convert(smalldatetime, zedaj + zedam + zedat))=0
[/code]

to check for invalid data

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-28 : 04:24:26

too late

welcome back from a long holiday khtan!

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 04:27:08

quote:
Originally posted by webfred

too late

welcome back from a long holiday khtan!

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks. I hope i was on holiday

. Just too much work, too little time

KH
[spoiler]Time is always against us[/spoiler]

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-28 : 04:38:57

select isdate (cast(zedam+'-'+zedaj+'-'+zedat as smalldatetime ))
from lqdandebrz

it returns only 1 ... so the values are valid... but when i select them... it returns values like 2026-03-06 .... so the select takes wrong values from the wrong column.... but if I change the order in the select ... it returns over-flow msg... :( ... I can't take it .... I can't even check the data if its valid ... because If i change the order it over-flows AGAAAAAAAIN !!! :(
pls help :|

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 04:42:37

1. don't case to datetime before using isdate
2. form the date in YYYMMDD ISO format. It is not dependent on your locale


select zedaj, zedam, zedat, isdate (zedaj + zedam + zedat)
from   lqdandebrz 
where  isdate (zedaj + zedam + zedat) <> 1

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-28 : 04:46:17

u should follow the yyyymmdd format or mmddyyyy format

SELECT CAST('2009'+'-'+'03'+'-'+'23' AS DATETIME)

SELECT CAST('03'+'-'+'26'+'-'+'2009' AS DATETIME)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-28 : 04:50:03

Also note that isdate() is not fully reliable

select isdate(2000), isdate('2000'),isdate(35000/10)

Madhivanan

Failing to plan is Planning to fail

kote_alex
Posting Yak Master

112 Posts

Posted - 2009-04-28 : 05:08:11

quote:
Originally posted by madhivanan

Also note that isdate() is not fully reliable

select isdate(2000), isdate('2000'),isdate(35000/10)

Madhivanan

Failing to plan is Planning to fail

damn u are so right .... is date doesn't work... I've done it ... I had one entry... 2004.06.31 .... stupid meeeeeee :D

thanks a lot u guys !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-28 : 05:18:30

quote:
Originally posted by kote_alex

quote:
Originally posted by madhivanan

Also note that isdate() is not fully reliable

select isdate(2000), isdate('2000'),isdate(35000/10)

Madhivanan

Failing to plan is Planning to fail

damn u are so right .... is date doesn't work... I've done it ... I had one entry... 2004.06.31 .... stupid meeeeeee :D

thanks a lot u guys !

You mean isdate() returns '2004.06.31' as a valid date ? well not my machine. . .

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources