Sql Server string to date conversion

Question

I want to convert a string like this:

'10/15/2008 10:06:32 PM'

into the equivalent DATETIME value in Sql Server.

In Oracle, I would say this:

TO_DATE('10/15/2008 10:06:32 PM','MM/DD/YYYY HH:MI:SS AM')

This question implies that I must parse the string into one of the standard formats, and then convert using one of those codes. That seems ludicrous for such a mundane operation. Is there an easier way?

Answer 1

This page has some references for all of the specified datetime conversions available to the CONVERT function. If your values don't fall into one of the acceptable patterns, then I think the best thing is to go the ParseExact route.

Answer 2

If you want SQL Server to try and figure it out, just use CAST CAST('whatever' AS datetime) However that is a bad idea in general. There are issues with international dates that would come up. So as you've found, to avoid those issues, you want to use the ODBC canonical format of the date. That is format number 120, 20 is the format for just two digit years. I don't think SQL Server has a built-in function that allows you to provide a user given format. You can write your own and might even find one if you search online.

Answer 3

Try this

Cast('7/7/2011' as datetime)

and

Convert(varchar(30),'7/7/2011',102)

See CAST and CONVERT (Transact-SQL) for more details.

Answer 4

Run this through your query processor. It formats dates and/or times like so and one of these should give you what you're looking for. It wont be hard to adapt:

Declare @d datetime
select @d = getdate()

select @d as OriginalDate,
convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,12),12,'yymmdd'
union all
select @d,convert(varchar,@d,112),112,'yyyymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

Answer 5

SQL Server (2005, 2000, 7.0) does not have any flexible, or even non-flexible, way of taking an arbitrarily structured datetime in string format and converting it to the datetime data type.

By "arbitrarily", I mean "a form that the person who wrote it, though perhaps not you or I or someone on the other side of the planet, would consider to be intuitive and completely obvious." Frankly, I'm not sure there is any such algorithm.

Answer 6

Use this:

SELECT convert(datetime, '2018-10-25 20:44:11.500', 121) -- yyyy-mm-dd hh:mm:ss.mmm

And refer to the table in the official documentation for the conversion codes.