We help IT Professionals succeed at work.

help converting varchar to date

Chris Jones
Chris Jones asked
on
278 Views
Last Modified: 2018-04-06
Hello,

I am trying to convert a varchar to a date here is my basic SQL


Select cast( CONVERT(VARCHAR(10), CreateTime, 101) as date)
from ME_API_Requests_History

Open in new window

Comment
Watch Question

select convert(stringvalue, DATETIME, 101) as date from blahblah...;

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
check this...

http://www.sqlusa.com/bestpractices/datetimeconversion/

there are tons of format that you should consider...

some samples

-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM
PRINT CONVERT(datetime,'07-10-2012',110)        -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'2012/07/10',111)        -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'20120710',  112)        -- Jul 10 2012 12:00AM  

Open in new window

Chris JonesLead Application Web Developer

Author

Commented:
i made a few modifications but seeing another error


select cast(convert(varchar, CreateTime, 101) as date ) as date
from ME_API_Requests_History

Open in new window

Chris JonesLead Application Web Developer

Author

Commented:

check this...

http://www.sqlusa.com/bestpractices/datetimeconversion/

there are tons of format that you should consider...

some samples

-- SQL convert date string to datetime - time set to 00:00:00.000 or 12:00AM
PRINT CONVERT(datetime,'07-10-2012',110)        -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'2012/07/10',111)        -- Jul 10 2012 12:00AM
PRINT CONVERT(datetime,'20120710',  112)        -- Jul 10 2012 12:00AM  

Select all
 
Open in new window


here is my sql

select CONVERT(datetime,CreateTime,111)   
from ME_API_Requests_History

Open in new window



here is the error
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.
Chris JonesLead Application Web Developer

Author

Commented:
@Jestertoo

here is my SQL from your example
select convert(datetime, CreateTime, 101) as date 
from ME_API_Requests_History

Open in new window


Error
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
what is result of this

select top 10 CreateTime from ME_API_Requests_History
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
101 means

SELECT convert(datetime, '10/23/2016', 101) -- mm/dd/yyyy

is your data in this format?
What does your raw varchar field look like?
Chris JonesLead Application Web Developer

Author

Commented:
what the field looks like

1488161306593
1488161135675
1488159555193
1488157561340
NULL
1488132344943
1488127626047
1488095962468
1488068391695
1488060301387
1488053604526
1487980578120
1487974386083

Open in new window

This looks like a timestamp... not a datetime value.  Can you confirm that?  WHere did the value come from?  Can you identify the month, day, year, time components it may contain?
Chris JonesLead Application Web Developer

Author

Commented:
i think this is a UNIX time stamp that comes from an API request from a software tool that we have.
Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
HainKurtSr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Chris JonesLead Application Web Developer

Author

Commented:
Thaks @Huseyin KAHRAMAN this worked great i just could not get the multiple conversions down.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*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.