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 |
|
sabina_bi
Starting Member
1 Post |
 Posted - 2007-10-22 : 16:06:26
|
| Hello I have one processing date column in contiguous string format (like 20040815 represents 2004-08-15),and another column processing time where value looks like 16561877 which means16h:56m:18s:77ms.I have to combine this two column in one column and represents it in getdate() format. Any form of help will be greatly appreciated.Thanks. |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-10-22 : 16:08:34
|
| that looks like a really bad design? Is there any spefic reason for splitting the date and time part? Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
 |
|
|
Will H
Yak Posting Veteran
56 Posts |
Posted - 2007-10-22 : 16:47:21
|
| Second the bad design. You can convert this to proper datetime format using string manipulation, but its going to save a lot of processing power to put it in the correct format to begin with. One prob is checking for invalid dates - example 20040231 or 20063099. I'm just going to assume the best. If you can get the data in the form'YYYY-MM-DD HH:MM:SS:mmm'You can convert like so:SELECT CONVERT(DateTime, '2006-08-15 16:56:18.77')So, to convert just the date part, try:SELECT CONVERT(DateTime, (LEFT(@Date, 4) + '-' + SUBSTRING(@Date, 5, 2) + '-' + SUBSTRING(@Date, 7, 2)))(Replace @Date with the field name)Time would follow the same process, but again if the data format isn't correct, its going to be painful. ---------------------------------------------------------SSRS Kills Kittens. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-10-22 : 17:25:57
|
| [code]select *, Combined_DateTime = Mydate+convert(datetime,stuff(stuff(stuff(Mytime,3,0,':'),6,0,':'),9,0,'.'))from ( --Test Data select Mydate = '20071231', Mytime ='16561877' ) aResults:Mydate Mytime Combined_DateTime-------- -------- ----------------------- 20071231 16561877 2007-12-31 16:56:18.770(1 row(s) affected)[/code]CODO ERGO SUM |
|
|
|
|
|
|
|
|
date time problem