views:

1146

answers:

9

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?

A: 

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.
+1  A: 

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
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
I agree don't use 101, use 112 aka ISO or 102 ANSI.
DBAndrew
I agree - i generally use 112 for development, but 101 generally looks more readable in the output when running sample code.
Scott Ivey
updated answer to use the ISO date to make it more friendly outside of the US.
Scott Ivey
A: 

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
A: 

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
+6  A: 
CONVERT(varchar(8), @ParamDate, 112)    -- Supported way

CAST(FLOOR(CAST(@ParamDate AS float)) AS DATETIME)   -- Unsupported way
GSerg
I use the "unsupported way" here all the time. What's with the 112 magic number on the first line?
Jeffrey
It is the format specifier for the date. See http://msdn.microsoft.com/en-us/library/ms187928.aspx
David McEwing
Correct. And please note only 112 is correct because it's an ISO format (as opposed to locale-dependant formats).
GSerg
why varchar(8) as an input to convert ?
Saobi
Saobi: It's destination data type, not input.
GSerg
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
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
+6  A: 
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
Bing
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
+2  A: 

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
+1 for reading more than just the title *heeh*
Andomar
A: 

Strip the time, and cast it to date:

select cast(left(yourstring, 10) as datetime)
Andomar
+1  A: 
SELECT CAST(CONVERT(VARCHAR,GETDATE(),102) AS DATETIME)

SELECT CAST(CONVERT(VARCHAR(10),'2009-06-24 09:52:43.000',102) AS DATETIME)
DBAndrew