convert varchar to datetime fails on insert

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

convert varchar to datetime fails on insert

Author

Topic

elshorty
Starting Member

4 Posts

Posted - 2012-11-28 : 05:03:42

I need to insert a varchar in my table. The type in the table is a datetime so I need to convert it. I didn't think this would be to big of a problem however it keeps inserting 1900-01-01 00:00:00.000 instead of the date I want. When I do a select with my converted date it does show me the correct date.

I'll show you the code:


INSERT INTO Item (CategoryId, [Date], Content, CreatedOn)
   SELECT 
       CategoryId, Convert(datetime, '28/11/2012', 103), Content, GetDate()
   FROM 
       Item i
   JOIN 
       Category c ON i.CategoryId = c.Id
   JOIN 
       Division d ON d.Id = c.DivisionId
   WHERE 
       Date = Convert(datetime, '31/03/2005', 103) 
       AND d.Id = '142aaddf-5b63-4d53-a331-8eba9b0556c4'

The where clause works perfectly and gives me the filtered items I need, all data is correctly inserted except for the converted date. The gives like I said 1900-...

If I just do the select so:


SELECT CategoryId, Convert(datetime, '28/11/2012', 103), Content, GetDate()
FROM Item i
JOIN Category c ON i.CategoryId = c.Id
JOIN Division d ON d.Id = c.DivisionId
WHERE Date = Convert(datetime, '31/03/2005', 103) AND d.Id = '142aaddf-5b63-4d53-a331-8eba9b0556c4'

I get the correct date being: 2012-11-28 00:00:00.000. I have tried to use a different conversion like:


Convert(datetime, '20121128')
CAST('20121128' AS DATETIME)

The where clause works perfectly, the select shows me the date I want but on insert it keeps inserting: 1900-01-01 00:00:00.000

Anyone know why it is doing this? Any ideas or help would greatly be appreciated

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-11-28 : 05:30:31

What is the datatype of Item.[Date]?

Too old to Rock'n'Roll too young to die.