Synapse convert nvarchar to Date_ exclute the T00:00:00

Question

Synapse convert nvarchar to Date_ exclute the T00:00:00

rdboy_9727 61

hi guys,
I don't want the part after the Time (T) in column event_day_date. How can I extract only the date.

Thanks.

ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2022-01-21T16:51:31.707+00:00

Hi @rdboy_9727 ,

Following to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

1 answer

Your answer

ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2022-01-21T16:51:31.707+00:00

Hi @rdboy_9727 ,

Following to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

Answer 1

ShaikMaheer-MSFT 38,551 Microsoft Employee Moderator

Hi @rdboy_9727 ,

Thank you for posting query in Microsoft Q&A Platform.

Use below query to get your desired output.
SELECT FORMAT(TRY_CONVERT(DATE,'2021-12-09T00:00:00.0000000',102),'yyyy-MM-dd') as event_day

Hope this will help. Please let us know if any further queries.

----------------

Please consider hitting Accept Answer button. Accepted answers helps community as well.

ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2022-01-19T14:17:24.047+00:00

Hi @rdboy_9727 ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
rdboy_9727 61 Reputation points

2022-01-22T07:50:26.073+00:00

Hi @ShaikMaheer-MSFT ,
it did not work

sample data -

09DEC2021:10:30:18.000000
13OCT2021:01:08:03.000000
rdboy_9727 61 Reputation points

2022-01-22T08:00:33.387+00:00

Hi @ShaikMaheer-MSFT ,
i did try the 102 as well but no luck
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2022-01-24T17:15:04.617+00:00

Hi @rdboy_9727 ,

Intial ask was about event_day for which my intial answer helps as shown in screenshot of it.

Regarding followup query, seems this time you are trying to convert eventtime. eventtime column data is different that event_day column. Hence we should not use 102 code for this.

Please check below link where its documented that which code numbers to use for which can of format.
https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/

Hope this will help.

Please consider hitting Accept Answer. Accepted answers helps community as well.
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2022-02-01T16:11:41.817+00:00

Hi @rdboy_9727 ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

Share via

Synapse convert nvarchar to Date_ exclute the T00:00:00

1 answer

Your answer