We help IT Professionals succeed at work.

Converting date with a Z to format 101 format

21 Views
Last Modified: 2020-05-19
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."
Comment
Watch Question

Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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é FarkasSQL Server Consultant
CERTIFIED EXPERT

Commented:
select convert(varchar(10), cast('2018-08-03T12:57:25Z' as datetime), 101)
Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:


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.
 

Author

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.