qauery help for date conversion

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

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

{count} votes

3 answers

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

    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.

    No comments

  2. answered 2021-04-12T09:38:42.797+00:00
    Viorel 82,286 Reputation points

    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
    
    No comments

  3. answered 2021-04-12T11:15:29.843+00:00
    Olaf Helper 25,476 Reputation points

    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
    No comments