Date Conversion - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Date Conversion

Author

Topic

QNAB
Starting Member

2 Posts

Posted - 2011-04-26 : 12:54:28

My JD Edwards accounting system stores dates in the following numeric format: CYYDDD.

C is century indicator (19xx = 0, 20xx = 1), YY is the 2 digit year and DDD is the number of days since the start of the year. 01 Jan 2011 is 111001 and 26 Apr 2011 is 111116. I need to convert these dates in a SELECT query into a format I can use in Excel - say in the DD MMM YYYY format. Is there a SQL Server 2005 function which can perform this conversion?

Thanks

Q

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 12:57:08

scrubs...

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-26 : 13:00:25

[code]DECLARE @x INT;
SET @x = 111116;
SELECT DATEADD(dd,@x%1000-1,DATEADD(yy,@x/1000,0))[/code]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-26 : 13:03:14

yuk - something like
convert(varchar(11),dateadd(dd,dte%1000,dateadd(yy,(dte/1000)%100,dateadd(yy,100*(dte/100000),0))),113)

s.b. a -1 on the day

convert(varchar(11),dateadd(dd,dte%1000-1,dateadd(yy,(dte/1000)%100,dateadd(yy,100*(dte/100000),0))),113)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 13:27:31

[CODE]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[udf_JDEdwards_Dt_To_SQLServer_Dt]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[udf_JDEdwards_Dt_To_SQLServer_Dt]
GO

CREATE FUNCTION [dbo].[udf_JDEdwards_Dt_To_SQLServer_Dt](@JDE_Dt char(6))
RETURNS datetime
AS
BEGIN
DECLARE @SQLServer_Dt datetime, @d varchar(25)

SELECT @d =
CASE WHEN SUBSTRING(@JDE_Dt,1,1) = 0 THEN '19' ELSE '20' END
+ SUBSTRING(@JDE_Dt,2,2)
+ '01'

SELECT @SQLServer_Dt = DATEADD(d,CONVERT(INT,SUBSTRING(@JDE_Dt,4,3)),@d)

RETURN(@SQLServer_Dt)
END
GO

SELECT dbo.[udf_JDEdwards_Dt_To_SQLServer_Dt]('111116')

[/CODE]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-04-26 : 13:30:10

or...what they said...but I still see a difference in a day between 26 and 27

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-04-26 : 13:52:50

[code]select
a.JDEDate,
[DateTime] = dateadd(yy,(a.JDEDate/1000),0)+((a.JDEDate%1000)-1)
from
( -- Test data
select JDEDate = 011001 union all
select JDEDate = 011116 union all
select JDEDate = 012116 union all
select JDEDate = 111001 union all
select JDEDate = 111116 union all
select JDEDate = 111365 union all
select JDEDate = 112001 union all
select JDEDate = 112366
) a
order by
a.JDEDate[/code]
Results:
[code]JDEDate DateTime
----------- -----------------------
11001 1911-01-01 00:00:00.000
11116 1911-04-26 00:00:00.000
12116 1912-04-25 00:00:00.000
111001 2011-01-01 00:00:00.000
111116 2011-04-26 00:00:00.000
111365 2011-12-31 00:00:00.000
112001 2012-01-01 00:00:00.000
112366 2012-12-31 00:00:00.000[/code]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources