| Author |
Topic |
|
fmardani
Constraint Violating Yak Guru
433 Posts |
 Posted - 2004-08-25 : 10:58:56
|
| assuming you have a varchar field which is holding a date, i.e. 2005219Notice thatthe first four letters indicate the year and the last 3 indicate the day of the year.Using this how can I convert this into a datetime field value?Thanks |
|
|
fmardani
Constraint Violating Yak Guru
433 Posts |
Posted - 2004-08-25 : 11:01:07
|
| Fouond the answer.Thanks |
 |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-08-25 : 11:03:43
|
| heres a problem...it could be 2005|2|19 => 2/19/2005or2005|21|9 => 9/21/2005Something to think about.Corey |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2004-08-25 : 11:04:30
|
| or 219...being a Julian date |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-25 : 11:07:20
|
| fmardani: post the answerhe probably meant that 219 was the 219th day of the year.Go with the flow & have fun! Else fight the flow :) |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-08-25 : 11:11:32
|
He did mean it was the day of the year.It's exactly what he posted.quote:
the last 3 indicate the day of the year.
Duane. |
|
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-08-25 : 11:16:13
|
ahhh... my bad then!  Corey |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-25 : 11:21:33
|
| well any ways this should work:here the first day of the year is marked as 0 so its zero based. 1.1. = day 0select substring('2005219', 1,4) as year, datepart(mm, cast(substring('2005219', 5,3) as int)) as month, datepart(dd, cast(substring('2005219', 5,3) as int)) as dayi just wonder if he did the same.Go with the flow & have fun! Else fight the flow :) |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2004-08-25 : 11:29:09
|
| He wanted it in one datetime field - so I'm guessing he did something like this:select cast(substring('2005219', 1,4) + '-' + CAST(datepart(mm, cast(substring('2005219', 5,3) as int)) as VARCHAR(2)) + '-' + CAST(datepart(dd, cast(substring('2005219', 5,3) as int)) as VARCHAR(2)) as DATETIME) as MyDateTimeField...........But we will never know if he doesn't post it.Duane. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2004-08-25 : 11:31:56
|
| altough i'm wondering why would someone have dates stored like this?? i mean where are the benefits???Go with the flow & have fun! Else fight the flow :) |
|
|
|
|
date