How to convert datetime to date only (with time set to 00:00:00.000)

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?

10 Answers
  1. CONVERT(varchar(8), @ParamDate, 112)    -- Supported way
    CAST(FLOOR(CAST(@ParamDate AS float)) AS DATETIME)   -- Unsupported way
    GSerg2009-06-24 20:10:07
  2. 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
    Kevin Newman2009-06-24 20:18:48
    SELECT CAST(CONVERT(VARCHAR(10),'2009-06-24 09:52:43.000',102) AS DATETIME)
    2009-06-24 20:59:17
  4. 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.

    TimF2009-06-24 20:53:43
  5. 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)
    2009-06-24 20:21:07
  6. 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)
    Eric H.2009-06-24 21:32:44
  7. 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.

    2009-06-24 20:08:49
  8. A variety of hacks:

    • Convert your string to a datetime, then back again using the optional "style" parameter to convert to convert your datetime to a string using just the date portion
    • use substring to chop off the end
    • round the datetime using floor
    2009-06-24 20:08:42
  9. An enhancement to the unsupported version: I am not sure if this may effect any performance. getdate() is an input timestamp in my query.

    select cast(cast(getdate() as DATE) as DATETIME)

    sanBV2009-06-24 20:09:10
  10. Strip the time, and cast it to date:

    select cast(left(yourstring, 10) as datetime)
    2009-06-24 20:09:25
Related Articles
You Might Also Like