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
convert varchar to datetime fails on insert
Posted - 11/28/2012 : 05:03:42
