Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert Varchar to Date?? Expand / Collapse
Author
Message
Posted Tuesday, July 30, 2013 9:00 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:15 PM
Points: 299, Visits: 497
Hi

I have a date field stored in a varchar as "Jul 24 2013 8:05AM"

I would like to convert to a date field to do calculations like this "2013-07-24 00:00:00.000"

Time is not important..

Thanks in Advance
Joe
Post #1479054
Posted Tuesday, July 30, 2013 9:08 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 3,476, Visits: 7,514
What's wrong with a simple CAST or CONVERT?
SELECT CONVERT( datetime, 'Jul 24 2013 8:05AM')




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1479058
Posted Tuesday, July 30, 2013 9:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:02 PM
Points: 1,373, Visits: 2,580
CAST will accomplish what you are looking for.

select cast(cast('Jul 24 2013 8:05AM' as date) as datetime)



Bob
-----------------------------------------------------------------------------
How to post to get the best help
Post #1479059
Posted Tuesday, July 30, 2013 9:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, August 14, 2014 12:15 PM
Points: 299, Visits: 497
Thanks so much....
I bit of a brain crap ... or just being a SPAZZ!!

I was testing with

DECLARE @Date varchar(8)
set @Date='Jul 24 2013 8:05AM'


SELECT CONVERT( datetime, @date)


select cast(cast('Jul 24 2013 8:05AM' as date) as datetime)


wondering why I was coming up with 2002-07-24......

BTW While I'm asking stupid questions....

How do I color code my code in replies?

Thanks Again and have a great date
Post #1479082
Posted Tuesday, July 30, 2013 10:11 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:52 PM
Points: 3,476, Visits: 7,514
There are some IFCode tags available to the left of your screen when you're replying. For SQL you need to use [ code="sql"][ /code] (without space).


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1479093
Posted Wednesday, July 31, 2013 1:21 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 5:16 PM
Points: 1,597, Visits: 1,151
I prefer this method (sql 2008 + )

SELECT CONVERT(DATE,'Jul 24 2013 8:05AM')

Gives the result:
2013-07-24


There's no kill switch on awesome!
Post #1479317
Posted Wednesday, July 31, 2013 8:26 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:09 AM
Points: 281, Visits: 1,035
Your varchar is to small for the data you are putting into it, so it is only taking the first 8 characters and then trying to convert that to a date. Increase the varchar size to at least 11.
Post #1479508
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse