Conversion error - 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)

Conversion error

Author

Topic

frankDK
Starting Member

14 Posts

Posted - 2010-06-02 : 03:01:20

hi

i get an conversion error when i try to convert a nvarchar to datetime

This is my select first statement, this works fine:


SELECT top 2 [childId]
      ,[gender]
      ,[bday]
      ,len([bday]) as lenght
      ,STUFF(STUFF(bday,3,0,'-'),6,0,'-') as dato
      ,countyid
  FROM [Sprogvurdering2].[dbo].[children]
  where countyId = 84 or countyId = 28
order by bday

result:
childId gender bday lenght dato countyid
45521 M 010107 6 01-01-07 28
60212 M 010107 6 01-01-07 84

my second select is also ok:


SELECT top 1 [childId]
      ,[gender]
      ,[bday]
      ,len([bday]) as lenght
      ,STUFF(STUFF(bday,3,0,'-'),6,0,'-') as dato
      ,convert(datetime, STUFF(STUFF(bday,3,0,'-'),6,0,'-'), 4)
      ,countyid
  FROM [Sprogvurdering2].[dbo].[children]
  where countyId = 28
  order by bday

Result:
childId gender bday lenght dato (No column name) countyid
45521 M 010107 6 01-01-07 2007-01-01 00:00:00.000 28

But when i change countyid to 84 i get a conversion error:
'The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.'

The stange thing is that i don't see any difference in the "bday" for the 2 countyid's.

Can you help me?

Frank

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-02 : 03:51:17

Which is the month and which is the day for value 01-01-07 ? And 08-06-07?

If you are using DMY (which I believe), substitute parts of your code with this

STUFF(STUFF(bday,3,0,'/'),6,0,'/') as dato
,convert(datetime, STUFF(STUFF(bday,3,0,'.'),6,0,'.'), 4)

because Style 4 for convert need dateparts separated with dot, not slash.

N 56°04'39.26"
E 12°55'05.63"

frankDK
Starting Member

14 Posts

Posted - 2010-06-02 : 04:01:22

hi

this did not do the trick:
STUFF(STUFF(bday,3,0,'/'),6,0,'/') as dato
,convert(datetime, STUFF(STUFF(bday,3,0,'.'),6,0,'.'), 4)

regarding "value 01-01-07 ? And 08-06-07?"

08-06-07 is 8 june 2007
01-01-07 is 1 jan 2007

i think the strange thing is that it works "sometimes"
childId gender bday lenght dato countyid
45521 M 010107 6 01-01-07 28
60212 M 010107 6 01-01-07 84

There is no difference in the "bday" for the two countyid's
the bday is 010107 for both......

i don't get it

frank

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-02 : 05:32:44

Are there extra spaces or unwanted characters in the column bday of the record for countyid 84?

convert(datetime, STUFF(STUFF(ltrim(rtrim(replace(bday, ' ', ''))),3,0,'.'),6,0,'.'), 4)

N 56°04'39.26"
E 12°55'05.63"

frankDK
Starting Member

14 Posts

Posted - 2010-06-02 : 05:53:16

You got it - that was the error

Thank you very much

regards
Frank

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources