Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bex
Aged Yak Warrior
580 Posts |
 Posted - 2006-04-13 : 05:30:39
|
| I have to add together two dates, one with only the time portion and one with only the date(char datatype). However, on trying to convert the char to a datetime datatype, I receive the following error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The issue is because of the format of the date: 2005-07-14, where sql server is reading the date as yyyyddmm as opposed to yyyymmdd. I have tried converting, but so far have not yet been successful. Can anyone help me with this?Hearty head pats |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2006-04-13 : 05:37:22
|
| select convert(varchar(20),'2005-07-14',120)If you need to add a time to this then:select convert(datetime,convert(varchar(20),'2005-07-14' + ' 12:12:12',120)) |
 |
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-04-13 : 05:40:20
|
| you can use string functions for thatselect cast (substring( 'date in yyyy-dd-mm format ', 1, 4) + substring( 'date', 9, 2) +substring( 'date', 6, 2) as datetime) |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-04-13 : 05:41:32
|
quote:
Originally posted by shallu1_gupta
you can use string functions for that to convert date from yyyy-dd-mm to yyyymmdd format and add the datesselect cast (substring( 'date in yyyy-dd-mm format ', 1, 4) + substring( 'date', 9, 2) +substring( 'date', 6, 2) as datetime)
|
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-04-13 : 05:43:34
|
| >>I have to add together two dates, one with only the time portion and one with only the date(char datatype).How about having a single DateTime column to avoid this type of difficulties?MadhivananFailing to plan is Planning to fail |
|
|
|
Bex
Aged Yak Warrior
580 Posts |
Posted - 2006-04-13 : 06:08:04
|
| Thankyou all for your replies. Got it working now!Yep, one column would definately make it simpler. Alas, it was an outsourced database (yes, someone actually got PAID for creating that pile of rubbish) and I have simply been drafted in to help someone with a query. If I had it my way, we would scrap the whole lot and start again (its that bad)!Have a good bank holiday weekend!Hearty head pats |
|
|
|
|
|
|
|
|
Date conversion error