17

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?

Stu
  • 15,675
  • 4
  • 43
  • 74
Saobi
  • 16,121
  • 29
  • 71
  • 81
  • Does this answer your question? [How can I truncate a datetime in SQL Server?](https://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server) – GSerg May 12 '23 at 09:31

12 Answers12

19

For SQL Server 2005 and below:

CONVERT(varchar(8), @ParamDate, 112)    -- Supported way

CAST(FLOOR(CAST(@ParamDate AS float)) AS DATETIME)   -- Unsupported way

For SQL Server 2008 and above:

CAST(@ParamDate AS DATE)

For SQL Server 2022 and above:

DATETRUNC(d, @ParamDate)
GSerg
  • 76,472
  • 17
  • 159
  • 346
  • 1
    I use the "unsupported way" here all the time. What's with the 112 magic number on the first line? – Jeffrey Jun 24 '09 at 20:12
  • 4
    It is the format specifier for the date. See http://msdn.microsoft.com/en-us/library/ms187928.aspx – David McEwing Jun 24 '09 at 20:14
  • 2
    Correct. And please note only 112 is correct because it's an ISO format (as opposed to locale-dependant formats). – GSerg Jun 24 '09 at 20:39
  • why varchar(8) as an input to convert ? – Saobi Jun 24 '09 at 20:43
  • Saobi: It's destination data type, not input. – GSerg Jun 24 '09 at 20:48
  • Your supported way dose not leave the data in the correct format that Saobi asked for. Also your unsupported way uses too many system functions which wastes resources. Its like using a sledge hammer to drive a screw. – DBAndrew Jun 24 '09 at 21:05
  • 1
    DBAndrew: Supported way isn't mine, it's Microsoft's. I did not bother with explicit conversion here as Saobi wanted to insert the result into a datetime column in which case that varchar(8) would be implicitly (and properly, what's most important) converted to datetime. Unsupported way also isn't mine, it's sorta "well known" basing on datetime storage format specs. Despite number of functions used, it's much faster as it doesn't involve any string parsing. – GSerg Jun 24 '09 at 21:45
  • What he is saying is 112 is yyyymmdd. 121 is yyyy-mm-dd hh:mm:ss.mmm which is what the guy asked for. – pilavdzice May 30 '12 at 22:24
14
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 Newman
  • 2,437
  • 1
  • 15
  • 12
  • This is The cool and elegant solution... but it only works if the input value is in fact a datetime. If you start with a string (varchar), you'd have to first convert it to a datetime, and doing that with 100% accuracy is one of the biggest headaches in programming. – Philip Kelley Jun 25 '09 at 13:45
7

I have found that casting as a date, then to a datetime to be very efficient and intuitive. 

 SELECT CAST(CAST(GETDATE() as date) as datetime)
Tyler Facker
  • 71
  • 1
  • 1
6
SELECT CAST(CONVERT(VARCHAR,GETDATE(),102) AS DATETIME)

SELECT CAST(CONVERT(VARCHAR(10),'2009-06-24 09:52:43.000',102) AS DATETIME)
DBAndrew
  • 6,860
  • 2
  • 20
  • 17
3

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)

sanBV
  • 45
  • 5
2

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.

TimF
  • 39
  • 1
  • 3
1

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)
Scott Ivey
  • 40,768
  • 21
  • 80
  • 118
  • 4
    Guys, why do you all use the 101 format? It's locale-dependant, it's only fine under EN-US locale. Under EN-GB it's already not as in the UK it's dd/mm/yyyy, not mm/dd/yyyy. Use the 112, it doesn't depend on anything. – GSerg Jun 24 '09 at 20:38
  • I agree don't use 101, use 112 aka ISO or 102 ANSI. – DBAndrew Jun 24 '09 at 21:06
  • I agree - i generally use 112 for development, but 101 generally looks more readable in the output when running sample code. – Scott Ivey Jun 24 '09 at 21:21
  • updated answer to use the ISO date to make it more friendly outside of the US. – Scott Ivey Jun 24 '09 at 21:33
1

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.

James Conigliaro
  • 3,809
  • 19
  • 22
0

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.
  • 2,566
  • 2
  • 23
  • 34
0

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
Jeremy Smyth
  • 23,270
  • 2
  • 52
  • 65
0

SELECT CAST('07/10/2022 09:00:00' AS DATE)
will give you

before - '07/10/2022 09:00:00' after - '07/10/2022 00:00:00'

Atharwa Kamble
  • 9
  • 1
-2

Strip the time, and cast it to date:

select cast(left(yourstring, 10) as datetime)
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • This relies on the formatting of time in a date first format. You cannot assume this to be true in all string datetime formats. – Wayne Arthurton Oct 09 '11 at 23:57