I have a string '2009-06-24 09:52:43.000', which I need to insert to a DateTime column of a table.
But I don't care about the time, just want to insert it as 2009-06-24 00:00:00.000
How can I do that in T-SQL?
I have a string '2009-06-24 09:52:43.000', which I need to insert to a DateTime column of a table.
But I don't care about the time, just want to insert it as 2009-06-24 00:00:00.000
How can I do that in T-SQL?
Probably a cleaner and more portable way to do this, but my years old idiom is:
insert into tbl (date_column)
select convert(varchar, convert (datetime, '2009-06-24 09:52:43.000'), 101)
cast it to a date, and then you can use CONVERT to get just the date.
INSERT MyTable(Column1)
SELECT CONVERT(CHAR(8), CAST('2009-06-24 09:52:43.000' AS DATETIME), 112)
If you will always have the date in the same format, i.e. yyyy-MM-DD you can grab the first 10 characters if the value and insert that which is the equivelant of 00:00:00.0000 time for that date.
select left('2009-12-32 4:32:00',10)
This is a very efficient way to do this as it does't require converting data types HOWEVER, it does require that the date will always be formatted with a four digit year and two digit day & month.
A variety of hacks:
convert
to convert your datetime to a string using just the date portionsubstring
to chop off the endfloor
CONVERT(varchar(8), @ParamDate, 112) -- Supported way
CAST(FLOOR(CAST(@ParamDate AS float)) AS DATETIME) -- Unsupported way
declare @originalDate datetime
select @originalDate = '2009-06-24 09:52:43.000'
declare @withoutTime datetime
select @withoutTime = dateadd(d, datediff(d, 0, @originalDate), 0)
select @withoutTime
James is correct. If you're starting off with a string, and the format will always be what you say it is, then you keep it simple and efficient. Use LEFT( @StrDate, 10)
and CONVERT
that to your datetime value. Done.
If your input string could be any valid date/time format, then you have to use CONVERT(datetime, @StrDate)
first. After that you go with what Bing just said to strip off the time part.
Strip the time, and cast it to date:
select cast(left(yourstring, 10) as datetime)
SELECT CAST(CONVERT(VARCHAR,GETDATE(),102) AS DATETIME)
SELECT CAST(CONVERT(VARCHAR(10),'2009-06-24 09:52:43.000',102) AS DATETIME)