Solved

Julian Date Conversion - DB2 Export -> SQL Server

Posted on 2003-11-11
32
4,487 Views
Last Modified: 2007-12-19
I have a date field called SDDQRJ which stores dates in 5 digit Julian form yyddd. so 01214 is the 214th day of year 01. any idea how to a conversion of this date to say mm/dd/yyyy using sql or vb script. I am trying to implement this conversion in DTS module of SQL Server. Thanks.
0
Comment
Question by:timokeeffe
  • 13
  • 11
  • 4
  • +2
32 Comments
 
LVL 22

Expert Comment

by:CJ_S
ID: 9721881
Play a little with this statement:

select convert(datetime, 01214 , 103)
You might need to substract or add some fixed number to your julian date.
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 9721902
Small mistake:

declare @juliandate varchar(5)
set @juliandate = '01001'

select dateadd(yy, convert(int, substring(@juliandate, 1, 2)), convert(datetime, convert(int, substring(@juliandate, 3, 3)), 101))
0
 
LVL 6

Expert Comment

by:lausz
ID: 9721926
Try this

--CONVERT to Julian date
CREATE FUNCTION [dbo].[ToJulDate] (@month smallint,@day smallint,@year smallint)  
RETURNS INT
AS  

BEGIN
DECLARE @jdate int
SELECT @jdate = (@year - 1) * 365 - @year/100 + @year/400 + (@year - 1 ) / 4
 IF @month > 2 AND ( ( @year%100 != 0 AND @year%4 = 0 ) OR @year%400 = 0 )
 SELECT @jdate = @jdate + 1
 SELECT @jdate = @jdate + 31 * ( @month - 1 ) + @day

IF ( @month > 2 )
 SELECT @jdate = @jdate - 3
IF ( @month > 4 )  
 SELECT @jdate = @jdate -  1      
IF ( @month > 6 )
 SELECT @jdate = @jdate - 1
IF ( @month > 9 )
 SELECT @jdate = @jdate - 1
IF ( @month > 11 )
 SELECT @jdate = @jdate - 1
RETURN @jdate

END


--CONVERT FROM Julian date
CREATE FUNCTION [dbo].[JulDate] (@Date int)  
RETURNS datetime AS  
BEGIN
      RETURN(DATEADD(DAY,@Date - 722815,'1/1/80'))
END


0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9721947
0
 

Author Comment

by:timokeeffe
ID: 9722296
Lauz,

I have tried this function you wrote:

CREATE FUNCTION [dbo].[JulDate] (@Date int)  
RETURNS datetime AS  
BEGIN
     RETURN(DATEADD(DAY,@Date - 722815,'1/1/80'))
END
 I executed in my SQL window and the function ran successfully, yet no columns changed. I have one column SDDRQJ which contains the 5 digit julian dat such as:

01214

How do I point you function at this column? Also is this function meant to be run right in the SQL window, or stored somewhere and called in sql? Thanks for the help.
0
 

Author Comment

by:timokeeffe
ID: 9722438
Lauz another point it seems that you are calling to convert all datetime fields in your function. I cannot do this as my data item comes in as varchar or it original sate as a decimal. if it is stored as datetimke the date conversion occurs and looks like this:

01214 listed above, coverts to:

2/9/2177
0
 
LVL 6

Expert Comment

by:lausz
ID: 9722455
I have this sample...and It works well, I have to investigate about your five digits..

A Julian date
is the number of days since noon,
Universal Time on January 1, 4713 BCE.
Some applications use Julian dates, but
with a different base date. My
employer's accounting system uses 1
A.D. as the base. December 31, 2000 was
730485. Since the dates we work with
are within datetime's range, DATEADD
can do the conversion.

DECLARE @j
INT
SET @j = 730636
SELECT @j AS
'Julian',
DATEADD(D,@j-730485,'12/31/2000') AS
'Gregorian'

0
 

Author Comment

by:timokeeffe
ID: 9722806
Lauz I have actually a six digit field when it comes over from DB2, But I am told the first digiot always a 1 is attached to the field for no specific purpose so I can delete the first char to get the five digit julian.
0
 
LVL 6

Expert Comment

by:lausz
ID: 9723062
I am trying to help you ..but ...

See this article

http://www.databasejournal.com/features/mssql/article.php/1442021 (inside search julian)
0
 
LVL 6

Expert Comment

by:lausz
ID: 9723084
0
 
LVL 22

Expert Comment

by:CJ_S
ID: 9723114
timokeeffe,
did you look at my sample?
0
 

Author Comment

by:timokeeffe
ID: 9723217
Yes the sample above:

DECLARE @j
INT
SET @j = 730636
SELECT @j AS
'Julian',
DATEADD(D,@j-730485,'12/31/2000') AS
'Gregorian'

How do I use this as a query? I import the data as I said as a six digit julian 101214, but could not find a reference to six digit julian. can you run this 101214 through your function and tell me what it converts to? Am I too put this function in SQL?

I could also try to delete the first char so we have a five digit julian. thanks again for the help.


0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9723683
If your column is numeric, I think the query below will convert the data as you need.

DECLARE @SDDQRJ INT
SET @SDDQRJ = 101214  -- 1(or any other digit) + juldate
SELECT DATEADD(DAY, (@SDDQRJ % 1000) - 1, CAST('20' +
      RIGHT('0' + CAST((@SDDQRJ % 10000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME))


This assumes that all dates are yr 2000 or later; if you need an adjustment for 1900 (or earlier?) dates, let me know.

If possible try to avoid calling a function because of the overhead involved.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9723754
I know that looks rather complex but in reality it's somewhat simpler because it doesn't rely on any date "tricks".  

The @SDDQRJ % 1000 - 1 pulls out the julian day - 1 (since the days will be added to Jan 1, have to subtract 1 to account for 1 day already used).

The rest of the calculation builds a string containing '20' [century] and a two-character year, '01' in this case, followed by '-01-01', that is, Jan 1 of whatever year is in the original date, then converts it to a date so that the jul days - 1 (in this case 213) can be added to it by SQL Server.  SQL by default will use a MM/DD/YYYY format when showing the date (at least in America :-) ), but you could also use a CONVERT around the whole final expression to force a specific date sytnax.
0
 

Author Comment

by:timokeeffe
ID: 9724235
ScottPletcher, thanks for this helpful code. How do I implement it? I placed it in my SQL window on sql server 2000 and it ran successfully but I saw no change to the SDDRQJ (sorry I mispelled earlier, fixed in your code). How do I use this code aslo with 1900 year dates? thanks for this help.
0
 

Author Comment

by:timokeeffe
ID: 9724512
Sorry scott pletcher this query above worked. it converted 101214 to the proper date 8/2/2001. any idea how to use this in coversion on SQL server? prhaps in DTS?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:timokeeffe
ID: 9724534
Scott my question now is how do I pass a variable to your query, liek the name of my column that needs to be converted my table is F4211, so could I pass f4211.SRDDQJ in for your harcoded 101214:

DECLARE @SDDRQJ INT
SET        @SDDRQJ = F4211.SDDRQJ
SELECT     DATEADD(DAY, (@SDDRQJ % 1000) - 1, CAST('20' + RIGHT('0' + CAST((@SDDRQJ % 10000) / 1000 AS VARCHAR(2)), 2)
                                                  + '-01-01' AS SMALLDATETIME))
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9724651
Sorry, that was just meant as a "proof" example that it would work.

To view the data in the table, you can do this:

SELECT     DATEADD(DAY, (SDDRQJ % 1000) - 1, CAST('20' + RIGHT('0' + CAST((SDDRQJ % 10000) / 1000 AS VARCHAR(2)), 2)
                                                  + '-01-01' AS SMALLDATETIME))
FROM yourTableName


To update it, you can do something like this (assuming that this column is int (numeric), not a datetime type):

UPDATE yourTableName
SET SDDRQJ = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(DAY, (@SDDRQJ % 1000) - 1, CAST('20' +
     RIGHT('0' + CAST((@SDDRQJ % 10000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME)), 120), '-', '') AS INT)
WHERE SDDRQJ <= 199999


This would put a value of YYYYMMDD into a INT column, for example, 20010802.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9724689
CORRECTION below (the 10000 changed to 100000), and coded to use '19' for years 50-99 and 20 for years 01 thru 49, naturally adjust as needed.


SELECT CAST(REPLACE(CONVERT(CHAR(10), DATEADD(DAY, (@SDDRQJ % 1000) - 1, CAST(
      CASE WHEN (@SDDRQJ % 100000) / 1000 >= 50 THEN '19' ELSE '20' END +
      RIGHT('0' + CAST((@SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME)), 120), '-', '') AS INT)
0
 

Author Comment

by:timokeeffe
ID: 9724769
Scott this query below worked great to update, but I was wondering if I could update this to a small datetime 8/2/2001, as you did in the query above this update query listed below. thanks


UPDATE yourTableName
SET SDDRQJ = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(DAY, (@SDDRQJ % 1000) - 1, CAST('20' +
     RIGHT('0' + CAST((@SDDRQJ % 10000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME)), 120), '-', '') AS INT)
WHERE SDDRQJ <= 199999
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9725044
What is the datatype of the SDDRQJ column?  I'm assuming it's numeric, not smalldatetime, but if you want to update another column with smalldatetime, then use something like this, including the correction and century check:

UPDATE yourTableName
SET dateColumn = DATEADD(DAY, (SDDRQJ % 1000) - 1, CAST(
     CASE WHEN (SDDRQJ % 100000) / 1000 >= 50 THEN '19' ELSE '20' END +
     RIGHT('0' + CAST((SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME))
WHERE SDDRQJ <= 199999  -- remove or change as needed
0
 

Author Comment

by:timokeeffe
ID: 9725136
I tried the code above, I was using an INT type, it told me to use Convert to excute the query.

I then switched the type to Numeric as you expected above and it failed:
Operand data type numeric is invalid for modulo operator.

Perhaps you could re-do the query as a convert and then I could use INT as my type. your call. thanks for the help
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9725277
No, INT should be fine.

What is the datatype of SDDRQJ ????
0
 

Author Comment

by:timokeeffe
ID: 9725336
SDDRQJ is currently set to INT. When I run the query you listed, it tells me:

Disallowed Implicit conversion from data type smalldatetime to data type int, table CSData.dbo.f4211, column SDDRQJ. Usse the CONVERT function to run this query.


any ideas how to fix this to work with INT?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 9725437
Are you talking about the UPDATE?

UPDATE yourTableName
SET dateColumn = DATEADD(DAY, (SDDRQJ % 1000) - 1, CAST(
     CASE WHEN (SDDRQJ % 100000) / 1000 >= 50 THEN '19' ELSE '20' END +
     RIGHT('0' + CAST((SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME))
WHERE SDDRQJ <= 199999  -- remove or change as needed

Note that I used a dateColumn to receive the value of the UPDATE, not SDDRQJ.  

You can't convert an INT datatype column to a smalldatetime type column in one step.  You will have to add a new smalldatetime column temporarily.  First, update the new column to hold the converted values of SDDRQJ (as shown above).  Then change the datatype of SDDRQJ to smalldatetime (you will probably have to use a "dummy" calculation to allow the conversion).  Next, copy the new column over SDDRQJ, and, finally, remove the new column.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9725455
If you wanted to save a converted value back into the original INT column, that was the point of this earlier post:

SELECT CAST(REPLACE(CONVERT(CHAR(10), DATEADD(DAY, (@SDDRQJ % 1000) - 1, CAST(
     CASE WHEN (@SDDRQJ % 100000) / 1000 >= 50 THEN '19' ELSE '20' END +
     RIGHT('0' + CAST((@SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME)), 120), '-', '') AS INT)

as in:

UPDATE yourTableName
SET SDDRQJ = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(DAY, (SDDRQJ % 1000) - 1, CAST(
     CASE WHEN (SDDRQJ % 100000) / 1000 >= 50 THEN '19' ELSE '20' END +
     RIGHT('0' + CAST((SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME)), 120), '-', '') AS INT)

Then you will end up with YYYYMMDD in SDDRQJ, for example:
20010802 from an original value of 101214
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9725472
Btw, if you use the convert-in-place approach, in case some values are missed or you need to re-run it, add a WHERE clause:

UPDATE yourTableName
SET SDDRQJ = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(DAY, (SDDRQJ % 1000) - 1, CAST(
     CASE WHEN (SDDRQJ % 100000) / 1000 >= 50 THEN '19' ELSE '20' END +
     RIGHT('0' + CAST((SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME)), 120), '-', '') AS INT)
WHERE SDDRQJ <= 999999
0
 

Author Comment

by:timokeeffe
ID: 9725838
This has go me very confused. I cannot convert this column as is in the table?

So I ran this:
UPDATE F4211
SET SDDRQJ = CAST(REPLACE(CONVERT(CHAR(10), DATEADD(DAY, (SDDRQJ % 1000) - 1, CAST(
     CASE WHEN (SDDRQJ % 100000) / 1000 >= 50 THEN '19' ELSE '20' END +
     RIGHT('0' + CAST((SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME)), 120), '-', '') AS INT)

Which converted the dates to 20010802 form stored in SDDRQJ as an INT. Then I used the Export DTS functionality to export the data to Second table called called F4211N which has same data defs. except SDDRQJ is now SmallDatetime.

I received a coversion error: Destination Overflowed. any ideas???
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9725894
You cannot go directly from an int to a smalldatetime.  

You need to do something like this:

SELECT otherCols, ..., CONVERT(SMALLDATETIME, STUFF(STUFF(CAST(SDDRQJ AS VARCHAR(8)), 5, 0, '-'), 8, 0, '-'), 120)

That first changes it to a format of yyyy-mm-dd, which *can* be converted to a smalldatetime.
0
 

Author Comment

by:timokeeffe
ID: 9725964
Is it possible to update the new table F4211N which has smalldatetime type for SDDRQJ in one query by using the F4211 (old table with INT). I guess not, so what I will do is make copy column then copy all to new table and dump that into new table. my effort below, thanks so much for help.


UPDATE    F4211N
SET              F4211N.SDDRQJ = DATEADD(DAY, (F4211.SDDRQJ % 1000) - 1, CAST(CASE WHEN (F4211.SDDRQJ % 100000)
                      / 1000 >= 50 THEN '19' ELSE '20' END + RIGHT('0' + CAST((F4211.SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME))
WHERE     F4211.SDDRQJ <= 199999
0
 

Author Comment

by:timokeeffe
ID: 9726058
ScottPlethecher the query below works fine. I have the copy issue fixed as well.

UPDATE yourTableName
SET dateColumn = DATEADD(DAY, (SDDRQJ % 1000) - 1, CAST(
     CASE WHEN (SDDRQJ % 100000) / 1000 >= 50 THEN '19' ELSE '20' END +
     RIGHT('0' + CAST((SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME))
WHERE SDDRQJ <= 199999  -- remove or change as needed

This query will handle 1950- on dates corect? I assume becuase of then else staement. thanks for the help.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 9726097
If you have some way to join the tables, you could update from an INT on one table to a SMALLDATETIME on the other, for example:

Is it possible to update the new table F4211N which has smalldatetime type for SDDRQJ in one query by using the F4211 (old table with INT). I guess not, so what I will do is make copy column then copy all to new table and dump that into new table. my effort below, thanks so much for help.


UPDATE    F4211N
SET              F4211N.SDDRQJ = DATEADD(DAY, (F4211.SDDRQJ % 1000) - 1, CAST(CASE WHEN (F4211.SDDRQJ % 100000)
                      / 1000 >= 50 THEN '19' ELSE '20' END + RIGHT('0' + CAST((F4211.SDDRQJ % 100000) / 1000 AS VARCHAR(2)), 2) + '-01-01' AS SMALLDATETIME))
WHERE     F4211.SDDRQJ <= 199999
FROM F4211N
INNER JOIN F4211 ON F4211N.keyCol1 = F4211.keyCol1 AND F4211N.keyCol2 = F4211.KeyCol2


Just replace keyCol1 and, if needed, keyCol2 with the column(s) to do the join on.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now