How to convert a string in the format of mm/dd/yyyy to yyyy-mm-dd in T-SQL

Question

I have a date stored a string in the form of mm/dd/yyyy that is getting passed into a SQL Server SP for inserting into a DB. How do I get it converted in the format yyyy-mm-dd so that SQL Server will actually insert it correctly into a column with a datetime type? Just keeping it as the string mm/dd/yyyy didn't seem to do anything.

You could try breaking the string into separate yyyy, mm & dd parts and re-assemble as another string in yyyy-mm-dd format. — DeanOC, Jun 29 '16 at 21:27
@bjones Your comment is culture dependant! On my german machine this would break... You might look at my answer... — Shnugo, Jun 29 '16 at 21:41
@DeanOC, There's no need to use complex string splitting. `CONVERT` will parse this format correctly with the appropriate format number... — Shnugo, Jun 29 '16 at 21:42
@Amanda_Panda I'm surprised it doesn't do anything. I would either expect it to work or give an error. If you are getting an error let us know and we will tell you what it means. Letting us see your code would also help us to answer your question. — Martin Brown, Jun 29 '16 at 22:28

score 3 · Accepted Answer · edited May 23 '17 at 12:08

3

You should avoid culture dependant date formats whenever this is possible.

Look at this:

DECLARE @StringDate VARCHAR(100)='06/29/2016';

SET LANGUAGE ENGLISH;
SELECT CAST(@StringDate AS DATE)

/*

Throws an exception!

SET LANGUAGE GERMAN;
SELECT CAST(@StringDate AS DATE)
*/

Read about TSQL: CONVERT and its formats. Your format is the number 101:

SELECT CONVERT(DATE,@StringDate,101)

Best was to use independant formats You might read this

edited May 23 '17 at 12:08

Community

1
1

answered Jun 29 '16 at 21:37

Shnugo

66,100
9
53
114

Also important to note 'CAST' is implicit while 'CONVERT' is explicit. – clifton_h Jun 29 '16 at 22:35
Thank you everyone for your answers, they really helped! – Amanda_Panda Jun 30 '16 at 16:15

score 1 · Answer 2 · answered Jun 29 '16 at 21:42

1

You may use PARSE() to create DATETIME object from culture-specific format and then convert it to required string:

select convert(varchar(20), parse('02/15/2016' as datetime2 using 'en-US'), 20);

20 is CONVERT()'s specific code for ODBC's date style (YYYY-mm-dd).

answered Jun 29 '16 at 21:42

Aleksey Ratnikov

569
3
11

As you are using `CONVERT` why not call this with the appropriate *101* instead of the extra `PARSE` with the culture "en-US"? – Shnugo Jun 29 '16 at 21:48
To avoid language switching and VARCHAR string as result. Or am I missing something? – Aleksey Ratnikov Jun 29 '16 at 21:56
*How do I get it converted in the format yyyy-mm-dd so that SQL Server will actually insert it correctly into a column with a datetime type* I take this as such there is no need for a re-convert to a varchar type... – Shnugo Jun 29 '16 at 21:59
I've missed that one, my bad! Thank you. – Aleksey Ratnikov Jun 29 '16 at 22:01

score 0 · Answer 3 · answered Jun 29 '16 at 22:25

There are various ways to do this here are three:

You can set the DATEFORMAT setting before hand:

SET DATEFORMAT mdy;
INSERT INTO Table1 (DateCol) VALUES ('09/30/2016');

Or you can use the CONVERT function:

INSERT INTO Table1 (DateCol) VALUES (CONVERT(datetime, '09/30/2016', 101))

Or lastly you can use the PARSE function, this is more flexible as it will accept other date formats like 'Friday, 30 September 2016' but is apparently a little slower:

INSERT INTO Table1 (DateCol) VALUES (PARSE('09/30/2016' as datetime using 'en-US'))

How to convert a string in the format of mm/dd/yyyy to yyyy-mm-dd in T-SQL

3 Answers3