[Resolved] Convert a numeric field to datetime

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 2005 Forums

Transact-SQL (2005)

[Resolved] Convert a numeric field to datetime

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-10 : 09:52:42

I am quering a db2/400 data base and inserting into a temp table.
The table (which is a JDEdwards table) contains a date field that is numeric 6.0 (this is jde standard). Example format: 107299 where 107 equates to year 2007 and 299 is the day withing the year. I've been trying to cast this field into a datetime field without success. For year I think you can add 1900 and get the correct year (107 + 1900 = 2007). Not sure about the day of year. Anyone able to give me assistance?

The field in question is: vcdgj

create table	#JDE_DisbursementTable
		(  
           jde_bol_number int,
           jde_trans_date datetime,
           jde_product_code char(10),
           jde_product_name char(30),
		   jde_gross_qty dec(15,2),
           jde_net_qty dec(15,2)
                                     )


insert into #JDE_DisbursementTable(jde_bol_number,
                                   jde_trans_date, 
                                   jde_product_code, 
                                   jde_product_name,
                                   jde_gross_qty,
                                   jde_net_qty)
select     vcdoc, 
           vcdgj,
           vcprd1,
           vcpnam, 
           cast(vcqty as decimal(15,2)),
           cast(vcnetg as decimal (15,2)) 
from       AS400SRV_IBMDA400.VGSYS400.VGITFRLIB.F55124
where      vcpltc = @Terminal and
           @Product = Null or vcprd1 = @Product and
           @JdeDateFrom = 0 or vcdgj >= @JdeDateFrom and
           @JdeDateTo = 0 or vcdgj <= @JdeDateTo

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 09:59:37

Something similiar to this?

DECLARE	@jde NUMERIC(6, 0)

SET	@jde = 107299

SELECT	@jde,
	DATEADD(DAY, @jde % 1000, DATEADD(YEAR, @jde / 1000, -1))

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 10:01:12

Also, it strikes me you are not sure about operator presedence for AND / OR.
Use paranthesises if you'er not sure.

N 56°04'39.26"
E 12°55'05.63"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-10 : 10:13:23

Peso,

This worked great. Thank you very much. I've been working on this for days and here you solve it in a few minutes!

Takk skal du ha......

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 10:17:46

Varsågod!

You are sure the ORs and ANDs are correct?

N 56°04'39.26"
E 12°55'05.63"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-10 : 10:27:25

I've put the paranthesises around the and/or as you suggested and things are working great. I'm impressed with you skill set!

Hej da....

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-10 : 10:50:33

Peso,

If you have the time, could you explain the DATEADD(DAY, @jde % 1000, DATEADD(YEAR, @jde / 1000, -1)), not sure if I totally understand. Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-10 : 12:08:29

For the inner dateadd (with year) I simply add 107 years (calculated with integer division) to the date of Dec 31st 1899.
Why? Becuase January 1st of any year is the first day of year, but counted as ZERO for sql server.

When the base date has been set, I simply take the days part (with modulo 1000) and add the days.

N 56°04'39.26"
E 12°55'05.63"

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2009-07-13 : 15:47:48

Thank you Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources