SQL date formats

Question

How can I convert this 201402110544 to date(2014-02-11) in SQL server 2008?

Answer 1

You can cast as DATE data type (https://msdn.microsoft.com/en-us/library/bb630352(v=sql.100).aspx)

SELECT CAST(datetime_value AS DATE)

SELECT CAST(GETDATE() AS DATE) --> 2015-08-18

If you have a string to cast as DATE you can use:

SELECT CAST(LEFT('201402110544', 8) AS DATE)

You trim out the time part, by taking 1st 8 chars (YYYYMMDD) and will result a valid string to cast as DATE.

Answer 2

String to date conversion sure is a weakness of SQL Server. CONVERT does that, but can only deal with a number of given formats. So you must convert your string into such a format first and then convert it to date afterwards.

One such format is 120 = 'yyyy-mm-dd hh:mi:ss' which I think comes closest to yours. Another 102 = 'yyyy.mm.dd' suffices to get the date.

convert(
  date, 
  concat(substring(datestring, 1,4), '.', 
         substring(datestring, 5,2), '.', 
         substring(datestring, 7,2)), 
  102)
from data;

SQL fiddle: http://www.sqlfiddle.com/#!3/9eecb7/3689

EDIT: I stand corrected. Horia is right; CAST supports 'yyyymmdd', so you can use it directly by merely cutting of the string's time part. This is more readable than my suggestion above, which makes CAST the better option in your case.

Answer 3

If this format is always the same, you can do this

DECLARE @d VARCHAR(20)='201402110544'
SELECT CAST(SUBSTRING(@d,0,9) as DATETIME)

Also have a look at The ultimate guide to the datetime datatypes which explains in detail about handling date-times