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

Datetime conversion Expand / Collapse
Author
Message
Posted Monday, October 11, 2010 5:21 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 24, 2014 3:11 PM
Points: 72, Visits: 309
Can I get some help in converting a column data formatted in (weekday,Month,date) Example:Monday, May 24th
to date time2

It gives me the following error:
Conversion failed when converting date and/or time from character string.




Thanks,
Swetha K
Post #1002500
Posted Monday, October 11, 2010 5:40 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:05 AM
Points: 6,091, Visits: 7,114
Well, for starters, it's going to want a year.

I don't have 2k8 handy to run a direct test for datetime2, but stripping off the 'day of week' at the beginning by grabbing everything after the first comma, then appending ', 2010' to the end of the result should fix the issue.

IE: CAST( RIGHT( datefield, len(datefield) - charindex( ',', datefield)) + ', 2010' AS DATETIME) should work.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1002509
Posted Tuesday, October 12, 2010 9:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Saturday, May 24, 2014 3:11 PM
Points: 72, Visits: 309
I tried,but it gives me the same error ..

Please suggest.

Swetha K
Post #1002914
Posted Tuesday, October 12, 2010 9:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 11:28 AM
Points: 6,131, Visits: 13,259
swethak13 (10/12/2010)
I tried,but it gives me the same error ..

Please suggest.

Swetha K


can you post details of the query you are using?


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1002937
Posted Tuesday, October 12, 2010 9:36 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: 2 days ago @ 5:29 PM
Points: 3,112, Visits: 11,366
Get rid of the "th" after "24".

select convert(datetime2,'May 24 2010') AS MyDate

Results:
MyDate                      
---------------------------
2010-05-24 00:00:00.0000000
Post #1002943
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse