question

antonyjoseph-5093 avatar image
0 Votes"
antonyjoseph-5093 asked EchoLiu-msft commented

qauery help for date conversion

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

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Do you have anyupdate?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @antonyjoseph-5093,

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.



image.png (2.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.