asked on 5/19/2020

Converting date with a Z to format 101 format

Imported excel table to sql server, Dispatch_Date column shows as. Its data type is varchar(50) on SQL table. I am trying to convert it to date column, 101 and a time column

2018-08-03T12:57:25Z
2018-08-03T14:58:15Z

I tried select convert(varchar(50),Dispatch_Date,101) shows 2018-08-03T12:57:25Z,
I tried select convert(datetime,'Dispatch_Date',101) throws error "Conversion failed when converting date and/or time from character string."

Microsoft SQL Server SQL

Last Comment

Fred

5/19/2020

Scott Pletcher

5/19/2020

Format 101 is mm/dd/yyyy. Just CAST the value as the type you want:

SELECT CAST('2018-08-03T12:57:25Z' AS datetime)

Máté Farkas

5/19/2020

select convert(varchar(10), cast('2018-08-03T12:57:25Z' as datetime), 101)

Open in new window

ASKER CERTIFIED SOLUTION

Scott Pletcher

5/19/2020

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

Fred

5/19/2020

ASKER

Scott, I appreciate your feed back. They wanted to see Dates without the T and Z format. Also Dispatch time is to be compared to Notification time, roll time, arrival time.

Fred

5/19/2020

ASKER

select convert(varchar(10), cast('2018-08-03T12:57:25Z' as datetime), 101), this works well for a single row only. When trying a column "Conversion failed when converting date and/or time from character string". My point is trying to set up all the columns as the customer wants and show the differences in times in a format they want and understand