convert string to date

Question

hi
I have a date in below format
'2020-08-17T16:28:46' as string

want to convert in date

please help
i am trying as below

convert(DATETIME,format(insta_map_date,'dd-MMM-yyyy'),103))

the error is Argument data type varchar is invalid for argument 1 of format function. Severity 16

please help

but with error

Answer 1

It's a simple as this:

SELECT convert(datetime, '2020-08-17T16:28:46')

This is one of the safe formats, which will always be interpreted the one and the same, no matter the setting for DATEFORMAT.

Answer 2

convert(DATETIME,format(insta_map_date,'dd-MMM-yyyy'), 103 ))

Dateformat parameter 103 = British is wrong, use 126 = ISO8601 instead

select convert(datetime, '2020-08-17T16:28:46', 126)  

See CONVERT => Date and Time styles

Answer 3

Based on your question, I believe insta_map_date is a varchar field. and based on the source data sample you provided, the date format matches with ISO8601. So you can use 126 or 127 as the style while converting to datetime.

Try the following.

convert(DATETIME,insta_map_date, 126)

For more information about SQL Server Date Formats, refer : https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15