qauery help for date conversion

antony joseph 61 Reputation points
2021-04-12T09:29:05.21+00:00

Dear Experts

Please help to convert the below data and time format as given below

Date_col

2021-04-02 14:33:18.1166667
2021-04-05 18:47:21.9200000
2021-04-03 10:54:36.5333333
2021-04-03 21:25:21.8166667

SELECT FORMAT(TRY_CONVERT (DATETIME, DATE_COL, 103), 'yyMMdd') from shipping_tb;

I am getting null value here instead i need to get result as below

210402
210405
210403
210403

Please help

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,547 questions

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-04-12T09:38:29.473+00:00

    Hi @antony joseph ,

    Welcome to the microsoft TSQL Q&A forum!
    Please refer to: 

    declare @table table(Date_col datetime2)  
insert into @table values('2021-04-02 14:33:18.1166667'),('2021-04-05 18:47:21.9200000')  
                         ,('2021-04-03 10:54:36.5333333'),('2021-04-03 21:25:21.8166667')  
  
select FORMAT(Date_col, N'yyMMdd') from @table

    Output：
    86817-image.png

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Viorel 111.7K Reputation points
    2021-04-12T09:38:42.797+00:00

    If Date_col is a text (e.g. varchar(max)), then try this query: 

    SELECT FORMAT(TRY_CONVERT (DATETIME2, DATE_COL), 'yyMMdd') from shipping_tb
    0 comments
  3. Olaf Helper 40,656 Reputation points
    2021-04-12T11:15:29.843+00:00

    Two points:

    • You use format parameter 103, that's British format, but your source data has format 120 = ODBC format
    • The source data exceed the precise of data type datetime; use datetime2() or date instead => ;with test as (select '2021-04-02 14:33:18.1166667' as string union all select '2021-04-05 18:47:21.9200000' union all select '2021-04-03 10:54:36.5333333' union all select '2021-04-03 21:25:21.8166667') select *, try_convert(datetime2(7), string, 103) as fails, try_convert(datetime2(7), string, 120) as works, try_convert(date, string, 120) as worksToo from test
    0 comments