Wrong year when subtracting from julian dates

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

Wrong year when subtracting from julian dates

Author

Topic

krausr79
Starting Member

19 Posts

Posted - 2012-09-17 : 10:11:25

I'm taking a julian date and subtracting how long it takes a part to get delivered. Generally this is fine, but when I subtract enough days to push it into last year, the date comparisons no longer work properly. For example:

Jan 10, 2012 = 112010
Jan 5, 2012 = 112005
Dec 25, 2011 = 111359

112010-7=112003 and 112003<112005 : ok
112010-25=111985 and 111985>111359 : not ok

How do I correct for this?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-17 : 10:26:37

If you convert from Julian to SQL date/datetime and then use dateadd function, it should work. So for example:

DECLARE @julian1 INT = 112010
DECLARE @julian2 INT = 111359;

DECLARE @sqlDate1 date =  DATEADD(day, CAST(RIGHT(@julian1,3) AS int) - 1, CONVERT(datetime, LEFT(@julian1,2) + '0101', 112))
DECLARE @sqlDate2 date =  DATEADD(day, CAST(RIGHT(@julian2,3) AS int) - 1, CONVERT(datetime, LEFT(@julian2,2) + '0101', 112))

SELECT @sqlDate1,@sqlDate2;

SELECT DATEADD(dd,-25,@sqlDate1) -- subtract 25 days.

I am using the conversion formula from here:http://msmvps.com/blogs/robfarley/archive/2009/03/25/converting-to-and-from-julian-format-in-t-sql.aspx

krausr79
Starting Member

19 Posts

Posted - 2012-09-17 : 12:00:06

I do all of my sql using excel VBA statements. Am I even posting in the right forum??

I've never done any multi-line statements or statements using variables. I tried to look into variables, but my statment:

sql = " DECLARE @LookFor varchar(50)"

gets me the error: 'Token varchar not expected. Valid tokens: Dynamic, Sensitive, Insensitve, Asensitive'. I put dynamic in and it said something about cursors; which I don't want to use if I can help it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources