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: Today @ 3:45 PM
Points: 296, Visits: 492
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: Today @ 3:37 PM
Points: 3,374, Visits: 7,300
What's wrong with a simple CAST or CONVERT?
SELECT CONVERT( datetime, 'Jul 24 2013 8:05AM')




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Today @ 1:49 PM
Points: 1,345, Visits: 2,541
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: Today @ 3:45 PM
Points: 296, Visits: 492
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: Today @ 3:37 PM
Points: 3,374, Visits: 7,300
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.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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, June 25, 2014 8:05 AM
Points: 1,597, Visits: 1,150
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: Friday, July 25, 2014 2:51 PM
Points: 276, Visits: 1,003
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