Insert converted varchar into datetime sql
If you must use a string-based date format, you should pick one that is safe and works in every SQL Server instance, regardless of date format, language and regional settings.
That format is known as ISO-8601 format and it's either
YYYYMMDD (note: **NO** dashes!)
or
YYYY-MM-DDTHH:MM:SSS
for a DATETIME
column.
So instead of
Convert(datetime, '28/11/2012', 103)
you should use
CAST('20121128' AS DATETIME)
and then you should be fine.
If you're on SQL Server 2008 - you could also look into using DATE
(instead of DATETIME
) for cases when you only need the date (no time portion). That would be even easier than using DATETIME
and having the time portion always be 00:00:00
el shorty
Updated on July 05, 2022Comments
-
el shorty almost 2 years
I need to insert a
varchar
in my table. The type in the table is adatetime
so I need to convert it. I didn't think this would be to big of a problem however it keeps inserting1900-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')
But that just gives the same problem. Anyone that sees what I'm doing wrong?
Thx
-
zapping over 11 yearsHow come you are using the same table in the select and insert statements?
-
Damien_The_Unbeliever over 11 yearsMight there be a trigger on the table?
-
Jester over 11 yearsYou are inserting a constant string or field value?
-
el shorty over 11 yearsWhat I am doing is actually calling this query from visual studio and giving the dates in dynamically. I pass the query as a string with the values entered. But at the moment I am just testing the query in sql server cause as long it doesn't work there it is useless to use it in my application. I am using the same table because I need to copy items from one date to another. So the item needs to be exactly the same just on 2 different days
-
Damien_The_Unbeliever over 11 yearsSide note - when you're passing the values from visual studio (presumably from a .NET program in VS), you ought to be passing them as
DateTime
s anyway, not converting back and forth to strings. -
el shorty over 11 yearsYes but I need to pass the whole query as a string including the dates already inside the string. So I have to have a string. Not the easiest way but it's how it has to be done
-
-
el shorty over 11 yearsIt still gives me the exact same thing. I used CAST('20121128' AS DATETIME) but the inserted date keeps 1900-01-01 00:00:00.000. I cannot use date because in the future the hours might come in handy.