CODEWITHSUNDEEP
t-sqljulian-date
bluehiro
bluehiro

Reputation: 91

Unable to convert from Julian INT date to regular TSQL Datetime

Help me Stackoverflow, I'm close to going all "HULK SMASH" on my keyboard over this issue. I have researched carefully but I'm obviously not getting something right.

I am working with a Julian dates referenced from a proprietary tool (Platinum SQL?), though I'm working in SQL 2005. I can convert their "special" version of Julian into datetime when I run a select statement. Unfortunately it will not insert into a datetime column, I get the following error when I try:

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

So I can't setup datetime criteria for running a report off of the Stored Procedure.

Original Value: 733416 Equivalent Calendar Value: 01-09-2009

Below is my code... I'm so close but I can't quite see what's wrong, I need my convert statement to actually convert the Julian value (733416) into a compatible TSQL DATETIME value.

SELECT       
org_id, 
CASE WHEN date_applied = 0 THEN '00-00-00' 
ELSE convert(char(50),dateadd(day,date_applied-729960,convert(datetime, '07-25-99')),101) 
END AS date_applied,

CASE WHEN date_posted = 0 THEN '00-00-00'  
ELSE convert(char(50),dateadd(day,date_posted-729960,convert(datetime, '07-25-99')),101) 
END AS date_posted

from general_vw

Upvotes: 1

Views: 725

Answers (1)

Matt Mitchell
Matt Mitchell

Reputation: 41833

SELECT       
org_id, 
CASE WHEN date_applied = 0 OR date_applied < 639906 THEN convert(datetime, '1753-01-01')
ELSE dateadd(day,date_applied-729960,convert(datetime, '07-25-99'))
END AS date_applied,

CASE WHEN date_posted = 0 OR date_applied < 639906 THEN convert(datetime, '1753-01-01')
ELSE dateadd(day,date_posted-729960,convert(datetime, '07-25-99'))
END AS date_posted

from general_vw

You're casting to char but want a datetime so that's one easy fix.

You were also using '00-00-00' as your minimum date, but the minimum TSQL date is '1753-01-01'. Alternatively you could use something like ('1900-01-01') but that would need a change to the "less than" date_applied comparer.

I've added a "less than" date_applied comparer too. I calculated this as "SELECT 729960 + datediff(day,convert(datetime, '07-25-99'), convert(datetime,'1753-01-01'))". Any number less than this would cause a date underflow.

Upvotes: 1

Related Questions