Pass mm/dd/yyyy to yyyy-mm-dd date format-CodePudding

I would like to know if there is a function or a simple way to pass the format date mm/dd/yyyy

For example: 11/01/2022 (November 1st 2022)

To this format yyyy-mm-dd

For example: 2022-11-01

Nowadays, I am slicing the date as a string, getting the day, month and year individually and put them together as I want, but I'd like to know if there is a better way to do it.

CodePudding user response：

Assuming you're storing a date in a TEXT field? That is bad, but if that's indeed the case, the first step is to first parse it into a DATETIME object:

PARSE('11/01/2022' AS datetime USING 'en-US')

Now that it's in a DATETIME, use FORMAT() to format it to your desired format:

SELECT  FORMAT( PARSE('11/01/2022' AS datetime USING 'en-US') , 'yyyy-MM-dd')

Returns:

2022-11-01

CodePudding user response：

This can be done using PARSE which has the [USING culture] option:

PARSE ( string_value AS data_type [ USING culture ] )

In your example

SELECT PARSE('11/01/2022' AS datetime2 USING 'en-US');

Output:

2022-11-01

The above gives you date type which should be good for most tasks but if you need to get a string in a preferred format you can use FORMAT.

A. Culture specific output:

DECLARE @d DATE = PARSE('11/01/2022' AS datetime2 USING 'en-US');
SELECT FORMAT( @d, 'd', 'en-US' ) 'US English'  
      ,FORMAT( @d, 'd', 'en-gb' ) 'British English'
      ,FORMAT( @d, 'd', 'en-au' ) 'Australian' 
      ,FORMAT( @d, 'd', 'de-de' ) 'German'  
      ,FORMAT( @d, 'd', 'zh-cn' ) 'Chinese Simplified (PRC)';

US English  British English     Australian  German      Chinese Simplified (PRC)
11/1/2022   01/11/2022          1/11/2022   01.11.2022  2022/11/1

(Please note some countries always use two digits for day and month and some don't)

B. Fixed format output:

DECLARE @d DATE = PARSE('11/01/2022' AS date USING 'en-US');
SELECT FORMAT( d, 'yyyy-MM-dd') 'yyyy-MM-dd';

2022-11-01