Number to Date

Hi,
I have a legacy system that uses a strange date format, wich I'd like to
convert for datetime on a new table.
The date is in this format: 104001(2004-01-01), 104002(2004-01-02),
104271(2004-09-27)
I think that I'll have to break the problem in two parts:
The year - three first digits + 1900
the day - three last digits (dy) day of the year.
I can generate the days by using "datepart(dy,date)", but how can i make the
way back? (to transform 271 in 09-27)
Thanks for your help.
SQL Server can do this with simple CAST operations, for instance, do:
SELECT CAST( CAST( 1900 + LEFT( c, 3 ) AS CHAR( 4 ) ) + '0101' AS DATETIME )
+ CAST( RIGHT( c, 3 ) AS INT ) - 1
FROM tbl ;
-- where c is the column from the table tbl.
Anith
|||Miguel,
Anith's solution will work for dates in or after the year 2000, but it
could give the wrong answer for earlier dates, if they are stored as
integers or as strings but without a leading zero. Here's an alternate
solution that should work:
select dateadd(year,@.c/1000,-1) + @.c%1000 from yourTable
Steve Kass
Drew University
Miguel Salles wrote:

>Hi,
>I have a legacy system that uses a strange date format, wich I'd like to
>convert for datetime on a new table.
>The date is in this format: 104001(2004-01-01), 104002(2004-01-02),
>104271(2004-09-27)
>I think that I'll have to break the problem in two parts:
>The year - three first digits + 1900
>the day - three last digits (dy) day of the year.
>I can generate the days by using "datepart(dy,date)", but how can i make the
>way back? (to transform 271 in 09-27)
>Thanks for your help.
>
>
sql