CODEWITHSUNDEEP
sql-servert-sqldatetime
Amanda_Panda
Amanda_Panda

Reputation: 1186

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

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.

Upvotes: 0

Views: 14514

Answers (3)

Martin Brown
Martin Brown

Reputation: 25330

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

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67321

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

Upvotes: 3

Aleksey Ratnikov
Aleksey Ratnikov

Reputation: 569

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

Upvotes: 1

Related Questions