Sql Server string to date conversion
Solution 1
Try this
Cast('7/7/2011' as datetime)
and
Convert(DATETIME, '7/7/2011', 101)
See CAST and CONVERT (Transact-SQL) for more details.
Solution 2
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)'
Solution 3
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.
Solution 4
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.
Solution 5
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.
Related videos on Youtube
JosephStyons
I started out as a professional developer using Delphi and Oracle in a Win32 client-server environment for a manufacturing company. I worked for five years in consulting, implementing solutions for dozens of clients and using many disparate technologies. Since then, I've worked for and with the non-profit industry, building applications that help them move their missions forward. My bread-and-butter is VB.NET and C# against a SQL Server back-end using a SOA architecture. But I can and will use whatever tool gets the job done, and I've had fun doing so with Angular, jQuery, ASP.NET, PHP, and even my own homemade frameworks to deliver solutions against that platform.
Updated on March 23, 2022Comments
-
JosephStyons about 2 years
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?
-
hajili about 11 years
-
user 88 91 over 3 yearsThe 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.
-
-
matao over 11 yearsthere is such an algorithm, Oracle has already implemented it, and SQL Server's lack of an equivalent is a constant pain.
-
David Hergert over 11 yearsI think the question was how to convert a string into a datetime, not a datetime into a string.
-
Adir D over 11 years@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? -
matao over 11 yearsno worries, here: 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 over 11 yearsalthough sql server does have native support for PIVOT, which oracle is lacking (requiring hideious max(decode(...) constructs). so it all evens out I guess..
-
Philip Kelley over 11 years@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.
-
Blama over 10 years@matao: Oracle has a PIVOT and UNPIVOT clause since 11g R1 (2007) - SQL Language Reference
-
neverfox almost 10 years@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.
-
Joe DeRose about 9 yearsThis 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. -
Simon over 7 yearsTRY_PARSE was perfect. We had an issue with parsing a date 'Thu Sep 22 2016', thanks for sharing!
-
Chakri about 7 yearsThis doesn't work when month is > 12. The formatting expects mm/dd/yyyy format
-
Matias Masso almost 7 yearsUse Convert(varchar(30),'7/7/2011',103) when converting from dd/mm/yyyy
-
Nathan Griffiths over 6 years@Chakri if your dates are in dd/mm/yyyy use
SET DATEFORMAT dmy
before your query -
Code Novice over 4 yearsI 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... mssqltips.com/sqlservertip/2655/…
-
Code Novice over 4 yearsIdeally... THIS should be all you need... CONVERT('VARCHAR', 'ExpectedFormat', 'NewFormat') as in... CONVERT('2018.5.4', 'YYYY.D.M', 'MM/DD/YYYY')
-
Be Kind To New Users over 4 yearslink is broken.
-
Das_Geek over 4 yearsWelcome to StackOverflow! Please edit your answer 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.
-
Plutian about 4 yearsHi 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.
-
Jeff Moden almost 4 yearsLordy... the OP wanted to do this in SQL Server. What's all this talk about Oracle?
-
Jeff Moden almost 4 yearsYeah 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 almost 4 yearsIf 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.
-
JosephStyons almost 4 yearsA wicked new answer for a wicked old question. Thanks!
-
Jeff Moden almost 4 yearsThanks for the feedback, @JosephStyons.
-
luiscla27 over 2 yearsNever use
cast
when string date formats are involved, as its expected format depends on your SQL instance, theconvert
solution is okay though. -
Pablo over 2 yearsThe 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)
-
Sum almost 2 yearsthis is bad idea for any magic, try this will show why it look dumb: SELECT UsingCONVERT = CONVERT(DATETIME,'2/3/2008 10:06:32 PM') ,UsingCAST = CAST('3/2/2008 10:06:32 PM' AS DATETIME)