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?

The question should be edited to ask a more general question for converting from an expected input format to new output format. And the answers would then cover his but also everyone else's queries. While it's implicitly asked it should be asked explicitly. As to not cause unnecessary discussion about the actual question. — user 88 91, Oct 09 '20 at 11:19

score 349 · Accepted Answer · edited Mar 20 '22 at 12:10

349

Try this

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

and

Convert(DATETIME, '7/7/2011', 101)

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

edited Mar 20 '22 at 12:10

xxx

1,153
1
11
23

answered Aug 24 '11 at 12:26

gauravg

3,546
2
15
2

17

This is the right way to do it, and should be marked as the correct answer. Note that `Cast('2011-07-07' as datetime)` also works, and eliminates the ambiguity over month-and-day order. – Joe DeRose Apr 06 '15 at 20:50
1

This doesn't work when month is > 12. The formatting expects mm/dd/yyyy format – Chakri May 15 '17 at 10:10
2

Use Convert(varchar(30),'7/7/2011',103) when converting from dd/mm/yyyy – Matias Masso May 25 '17 at 16:01
6

@Chakri if your dates are in dd/mm/yyyy use `SET DATEFORMAT dmy` before your query – Nathan Griffiths Sep 27 '17 at 02:33
Never use `cast` when string date formats are involved, as its expected format depends on your SQL instance, the `convert` solution is okay though. – luiscla27 Dec 02 '21 at 17:23
The first parameter of convert is actually the return type, not the type you are passing to the function. So the example here doesn't actually work. Also, the format 102 is for yy.mm.dd, it works somehow for '7/7/2011' but it is not the correct one. The right way woul be, assuming the intended format is mm/dd/yyyy: Convert(DATETIME, '7/7/2011', 101) – Pablo Feb 10 '22 at 12:46

score 58 · Answer 2 · edited Jan 21 '15 at 23:29

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)'

Aaron Bertrand · Answer 3 · 2012-10-31T19:03:11.383

In SQL Server Denali, you will be able to do something that approaches what you're looking for. But you still can't just pass any arbitrarily defined wacky date string and expect SQL Server to accommodate. Here is one example using something you posted in your own answer. The FORMAT() function and can also accept locales as an optional argument - it is based on .Net's format, so most if not all of the token formats you'd expect to see will be there.

DECLARE @d DATETIME = '2008-10-13 18:45:19';

-- returns Oct-13/2008 18:45:19:
SELECT FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss');

-- returns NULL if the conversion fails:
SELECT TRY_PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME);

-- returns an error if the conversion fails:
SELECT PARSE(FORMAT(@d, N'MMM-dd/yyyy HH:mm:ss') AS DATETIME);

I strongly encourage you to take more control and sanitize your date inputs. The days of letting people type dates using whatever format they want into a freetext form field should be way behind us by now. If someone enters 8/9/2011 is that August 9th or September 8th? If you make them pick a date on a calendar control, then the app can control the format. No matter how much you try to predict your users' behavior, they'll always figure out a dumber way to enter a date that you didn't plan for.

Until Denali, though, I think that @Ovidiu has the best advice so far... this can be made fairly trivial by implementing your own CLR function. Then you can write a case/switch for as many wacky non-standard formats as you want.

UPDATE for @dhergert:

SELECT TRY_PARSE('10/15/2008 10:06:32 PM' AS DATETIME USING 'en-us');
SELECT TRY_PARSE('15/10/2008 10:06:32 PM' AS DATETIME USING 'en-gb');

Results:

2008-10-15 22:06:32.000
2008-10-15 22:06:32.000

You still need to have that other crucial piece of information first. You can't use native T-SQL to determine whether 6/9/2012 is June 9th or September 6th.

I think the question was how to convert a string into a datetime, not a datetime into a string. — David Hergert, Oct 31 '12 at 18:58
TRY_PARSE was perfect. We had an issue with parsing a date 'Thu Sep 22 2016', thanks for sharing! — Simon, Sep 14 '16 at 10:51

score 32 · Answer 4 · answered Oct 29 '08 at 20:33

32

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.

answered Oct 29 '08 at 20:33

Philip Kelley

39,426
11
57
92

38

there is such an algorithm, Oracle has already implemented it, and SQL Server's lack of an equivalent is a constant pain. – matao Oct 09 '12 at 01:07
20

@matao so please enlighten us, how does Oracle magically determine whether a user who typed `9/6/12` meant September 6th 2012, June 9th 2012, December 6th 2009, or something else? – Aaron Bertrand Dec 24 '12 at 16:09
16

no worries, here: http://www.techonthenet.com/oracle/functions/to_date.php Obviously it has to be a consistent format that you the developer specify, but vastly more flexible than the handful of format masks MS gives you, which results in painful custom parsing. – matao Jan 23 '13 at 08:14
although sql server does have native support for PIVOT, which oracle is lacking (requiring hideious max(decode(...) constructs). so it all evens out I guess.. – matao Jan 23 '13 at 08:44
3

@JosphStyons was aware of Oracle's TO_DATE function, as shown in his sample. He wanted to know if there was a way of converting dates-as-strings **without** having to know the string's format/structure. SQL does not do that, and it certainly appears that Oracle's TO_DATE doesn't do it either. – Philip Kelley Jan 23 '13 at 15:06
@matao: Oracle has a PIVOT and UNPIVOT clause since 11g R1 (2007) - [SQL Language Reference](http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF55285) – Blama Dec 03 '13 at 15:39
26

@PhilipKelley I don't see where the OP wants to know how to do it without having to know the format. He explicitly says he knows the format and is asking if SQL Server has something equivalent to TO_DATE, i.e. something that allows the developer to enter an arbitrary format string. – neverfox Jun 12 '14 at 21:08
I too prefer Oracles Date functions. I have to keep looking up a list of pre-formated date codes. I'm not going to remember all of the date codes! The closest equivalent is SELECT FORMAT (getdate(), 'dd-MM-yy') as date. The FORMAT function takes as it's parameter a date... https://www.mssqltips.com/sqlservertip/2655/format-sql-server-dates-with-format-function/ – Code Novice Sep 17 '19 at 20:42
2

Ideally... THIS should be all you need... CONVERT('VARCHAR', 'ExpectedFormat', 'NewFormat') as in... CONVERT('2018.5.4', 'YYYY.D.M', 'MM/DD/YYYY') – Code Novice Sep 17 '19 at 20:55
1

Lordy... the OP wanted to do this in SQL Server. What's all this talk about Oracle? – Jeff Moden Jun 07 '20 at 03:45

Simone · Answer 5 · 2018-01-29T09:43:54.957

13

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.

edited Jan 29 '18 at 09:43

answered Jan 29 '18 at 09:07

Simone

1,418
15
22

score 10 · Answer 6 · answered Oct 16 '08 at 02:29

10

For this problem the best solution I use is to have a CLR function in Sql Server 2005 that uses one of DateTime.Parse or ParseExact function to return the DateTime value with a specified format.

answered Oct 16 '08 at 02:29

Ovidiu Pacurar

8,173
2
30
36

score 8 · Answer 7 · edited May 25 '21 at 17:09

8

Short answer:

SELECT convert(date, '10/15/2011 00:00:00', 101) as [MM/dd/YYYY]

Other date formats can be found at SQL Server Helper > SQL Server Date Formats

edited May 25 '21 at 17:09

luiscla27

4,956
37
49

answered Aug 23 '11 at 14:22

Scott Gollaglee

121
1
1

score 6 · Answer 8 · answered Sep 24 '19 at 20:25

Took me a minute to figure this out so here it is in case it might help someone:

In SQL Server 2012 and better you can use this function:

SELECT DATEFROMPARTS(2013, 8, 19);

Here's how I ended up extracting the parts of the date to put into this function:

select
DATEFROMPARTS(right(cms.projectedInstallDate,4),left(cms.ProjectedInstallDate,2),right( left(cms.ProjectedInstallDate,5),2)) as 'dateFromParts'
from MyTable

luiscla27 · Answer 9 · 2022-03-22T21:42:54.960

The most upvoted answer here are guravg's and Taptronic's. However, there's one contribution I'd like to make.

The specific format number they showed from 0 to 131 may vary depending on your use-case (see full number list here), the input number can be a nondeterministic one, which means that the expected result date isn't consistent from one SQL SERVER INSTANCE to another, avoid using the cast a string approach for the same reason.

Starting with SQL Server 2005 and its compatibility level of 90, implicit date conversions became nondeterministic. Date conversions became dependent on SET LANGUAGE and SET DATEFORMAT starting with level 90.

Non deterministic values are 0-100, 106, 107, 109, 113, 130. which may result in errors.

The best option is to stick to a deterministic setting, my current preference are ISO formats (12, 112, 23, 126), as they seem to be the most standard for IT people use cases.

Convert(varchar(30), '210510', 12)                   -- yymmdd
Convert(varchar(30), '20210510', 112)                -- yyyymmdd
Convert(varchar(30), '2021-05-10', 23)               -- yyyy-mm-dd
Convert(varchar(30), '2021-05-10T17:01:33.777', 126) -- yyyy-mm-ddThh:mi:ss.mmm (no spaces)

score 3 · Answer 10 · answered Oct 16 '08 at 02:53

3

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.

answered Oct 16 '08 at 02:53

tvanfosson

524,688
99
697
795

link is broken. – Be Kind To New Users Oct 14 '19 at 02:08

score 3 · Answer 11 · answered Apr 01 '12 at 15:32

3

Personally if your dealing with arbitrary or totally off the wall formats, provided you know what they are ahead of time or are going to be then simply use regexp to pull the sections of the date you want and form a valid date/datetime component.

answered Apr 01 '12 at 15:32

SyWill

61
3

score 1 · Answer 12 · answered Oct 16 '08 at 02:50

1

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.

answered Oct 16 '08 at 02:50

Will Rickards

2,776
2
19
25

If you have international dates in a single column, I absolutelyu agree that using a format number is a good idea. If you have international dates and U.S. dates all mixed in a single column, there's just no way to determine the difference between something like 7/6/2000 and 6/7/2000 unless you have a sister column that explains the format. That's why data quality at the source simply MUST be a thing. If you KNOW that you have, say, all U.S. dates, let implicit conversions do their thing. If they fail, then you know for sure that something in the column needs to be fixed. – Jeff Moden Jun 07 '20 at 04:26

user12913610 · Answer 13 · 2020-03-10T15:18:37.747

1

convert string to datetime in MSSQL implicitly

create table tmp 
(
  ENTRYDATETIME datetime
);

insert into tmp (ENTRYDATETIME) values (getdate());
insert into tmp (ENTRYDATETIME) values ('20190101');  --convert string 'yyyymmdd' to datetime


select * from tmp where ENTRYDATETIME > '20190925'  --yyyymmdd 
select * from tmp where ENTRYDATETIME > '20190925 12:11:09.555'--yyyymmdd HH:MIN:SS:MS

edited Mar 10 '20 at 15:18

answered Feb 17 '20 at 15:35

user12913610

11
2

Hi and welcome to stackoverflow, and thank you for answering. While this code might answer the question, can you consider adding some explanation for what the problem was you solved, and how you solved it? This will help future readers to understand your answer better and learn from it. – Plutian Feb 18 '20 at 09:47

score 0 · Answer 14 · answered Dec 16 '20 at 08:14

0

You can easily achieve this by using this code.

SELECT Convert(datetime, Convert(varchar(30),'10/15/2008 10:06:32 PM',102),102)

answered Dec 16 '20 at 08:14

Venugopal M

2,280
1
20
30

score 0 · Answer 15 · answered Mar 14 '23 at 22:37

I found this article useful in creating my function that converts string values from .NET Datetime.ToString(...) back into SQL datetime. Tested it on 1000s of string dates in my system. Seems to work well. Hope this saves you time.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Julian King
-- Create date: 3/14/2023
-- Description: Parses and returns a date from a variety of string formats
-- See: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/
--      Thu Jul 01 2021 00:00:00 GMT-0400 (Eastern Daylight Time)
--      2018-04-16T00:00:00
--      2021-05-04T20:13:34.897781
--      2021-11-04 20:58:57.352836
--      4/17/2020 11:02:59 AM
--      January 13, 2020
-- =============================================
CREATE FUNCTION dbo.DateConvert
(
    @datestring VARCHAR(55)
)
RETURNS DATETIME
AS
BEGIN
    -- Declare the return variable here
    SET @datestring = RTRIM(LTRIM(@datestring))

    IF @datestring IS NULL
        RETURN CAST('1753-1-1' AS DATETIME)  
    
    IF @datestring = ''
        RETURN CAST('1753-1-1' AS DATETIME)

    -- Thu Jul 01 2021 00:00:00 GMT-0400 (Eastern Daylight Time)
    IF LEN(@datestring) > 30 AND @datestring LIKE '%GMT-%'
        SET @datestring = RTRIM(LTRIM(SUBSTRING(@datestring,5,20)))


    -- 2021-05-04T20:13:34.897781
    -- 2020-11-01T00:00:00
    IF @datestring LIKE '%T%'
        SET @datestring = REPLACE(@datestring,'T',' ')

    -- 2021-05-04 20:13:34.897781
    IF LEN(@datestring) = 26
        SET @datestring = SUBSTRING(@datestring,0,20)
    
    IF TRY_CONVERT(DATETIME,@datestring,101) IS NULL 
        RETURN CAST('1753-1-1' AS DATETIME) 
    ELSE
        RETURN CONVERT(DATETIME,@datestring,101) 

    RETURN CAST('1753-1-1' AS DATETIME)  
END
GO

score -1 · Answer 16 · answered Apr 13 '21 at 08:37

-1

This code solve my problem :

convert(date,YOUR_DATE,104)

If you are using timestamp you can you the below code :

convert(datetime,YOUR_DATE,104)

answered Apr 13 '21 at 08:37

Abd Abughazaleh

4,615
3
44
53

score -4 · Answer 17 · edited Nov 20 '19 at 18:36

-4

dateadd(day,0,'10/15/2008 10:06:32 PM')

edited Nov 20 '19 at 18:36

Das_Geek

2,775
7
20
26

answered Nov 20 '19 at 14:09

Yaroslav

1

3

Welcome to StackOverflow! Please [edit your answer](https://stackoverflow.com/posts/58956298/edit) to add an explanation for your code. This question is almost *eleven years old*, and already has **many** well-explained, upvoted answers. Without an explanation in *your* answer, it's of much lower quality compared to these others and will most likely get downvoted or removed. Adding that explanation will help justify your answer's existence here. – Das_Geek Nov 20 '19 at 14:12
Yeah but the "well explained" posts, even the upvoted ones, are way too complex. The one posted here is actually one of the better ones, with or without an explanation, – Jeff Moden Jun 07 '20 at 03:49

Sql Server string to date conversion

17 Answers17

Linked

Related