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.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Transform data to another value

Author  Topic 

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-08-21 : 04:39:54
Hi,

My friend gave me the data in weird format as follow,
tblTrx
TrxDate
------------
108183
108185
108203
...
...

1. From his explanation, 108203 is a combination of 108 and 203.
2. 108 means 2008
3. 203 means 203 day in 2008. It's also mean 22 July.
4. 108203 means 22 July 2008.

So far, i use select DATEADD (Day,203, '01-01-2008') to know the exact date in dd/mm/yyyy.

I need somebody help to make Update Statement can apply the whole row in tblTrx.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-21 : 05:04:58
declare @date varchar(10)
set @date=108203
select dateadd(day,right(@date,3)*1,cast('01 jan '+substring(@date,2,2) as datetime))


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-21 : 05:17:41
also this:-
select dateadd(dd,right(TrxDate,3)*1,dateadd(yy,left(TrxDate,3)*1,0))
Go to Top of Page

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-08-21 : 09:45:21
Another option:
select dateadd(d, TrxDate % 1000, dateadd(yy, TrxDate / 1000, 0))


Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Go to Top of Page

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2008-08-21 : 11:01:09
tq very much. all info really great.
Go to Top of Page
   

- Advertisement -