August 19, 2010 at 6:00 pm
I have created a function that will take data stored in a Julian Date format and convert it to formatted date time datatype. All works but the conversion (or appending) of the seconds values. This looks like it should work, but I feel as though I am missing something (maybe just another set of eyes).
Function:
CREATE FUNCTION [dbo].[fn_JulianDateConversion](@JulianDate VARCHAR(13))
RETURNS DATETIME
AS
BEGIN
DECLARE @ConvertedDate DATETIME,
@JulianDateYear VARCHAR(4),
@JulianDateDays VARCHAR(3),
@JulianHour VARCHAR(2),
@JulianMinute VARCHAR(2),
@JulianSecond VARCHAR(2),
@ConcatInfo VARCHAR(100) --mm/dd/yyyy HH:MM:SS
SELECT @JulianDateYear=SUBSTRING(@JulianDate,1,4)
SELECT @JulianDateDays=SUBSTRING(@JulianDate,5,3)
SELECT @JulianHour = SUBSTRING(@JulianDate,8,2)
SELECT @JulianMinute = SUBSTRING(@JulianDate,10,2)
SELECT @JulianSecond = SUBSTRING(@JulianDate,12,2)
SELECT @ConvertedDate=dateadd(day,cast(@JulianDateDays AS INT)-1,cast(@JulianDateYear AS DATETIME))
SELECT @ConcatInfo=DATEADD(ss,CAST(@JulianSecond AS INT),DATEADD(mi,CAST(@JulianMinute AS INT),DATEADD(hh,CAST(@JulianHour AS INT),@ConvertedDate)))
RETURN @ConcatInfo
END
Test:
SELECT dbo.fn_JulianDateConversion('1997090101636')
Should result in "1997-03-31 10:16:36.000" BUT returns "1997-03-31 10:16:00.000" instead.
Any help would be greatly appreciated.
August 20, 2010 at 9:45 am
You're returning a varchar field. Is there some reason that you don't want to return a datetime field?
When you assign your datetime field to your varchar field, you don't do an explicit conversion, so it does an implicit conversion. The format for an implicit conversion depends on your regional settings, but mine converts it to the format 'Mar 31 1997 10:16AM' truncating the seconds. If you then convert this string back to datetime, you can't recover the seconds.
My recommendation is to return a datetime field if you are working with datetime data. If you can't return datetime data, you should do an explicit conversion.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 20, 2010 at 10:14 am
sharp analysis drew... i would not have inferred the regional settings affecting the implicit conversion.
here's my version of the procedure, which seems to return the data with seconds:
CREATE FUNCTION [dbo].[fn_JulianDateConversion](@JulianDate VARCHAR(13))
RETURNS DATETIME
AS
BEGIN
--comments used for testing
--declare @JulianDate varchar(30)
--SET @JulianDate = '1997090101636'
DECLARE
@JulianDateYear INT,
@JulianDateDays INT,
@JulianHour INT,
@JulianMinute INT,
@JulianSecond INT,
@ConcatInfo DATETIME --mm/dd/yyyy HH:MM:SS
SELECT
@JulianDateYear = CONVERT(INT,SUBSTRING(@JulianDate,1,4)),
@JulianDateDays = CONVERT(INT,SUBSTRING(@JulianDate,5,3)),
@JulianHour = CONVERT(INT,SUBSTRING(@JulianDate,8,2)),
@JulianMinute = CONVERT(INT,SUBSTRING(@JulianDate,10,2)),
@JulianSecond = CONVERT(INT,SUBSTRING(@JulianDate,12,2))
SET @JulianDateYear = @JulianDateYear - 1900
SELECT @ConcatInfo =
dateadd(second,@JulianSecond, --seconds
dateadd(minute,@JulianMinute, --minutes
dateadd(hour,@JulianHour, --hours
dateadd(day,@JulianDateDays, --days
dateadd(year,@JulianDateYear,0))))) --year
return @ConcatInfo
END
Lowell
August 20, 2010 at 2:54 pm
Keep it simple...
DECLARE@Source CHAR(13) = '1997090101636'
SELECTDATEADD(YEAR, LEFT(@Source, 4) - 1900, SUBSTRING(@Source, 5, 3) - 1) + STUFF(STUFF(RIGHT(@Source, 6), 3, 0, ':'), 6, 0, ':')
N 56°04'39.16"
E 12°55'05.25"
August 20, 2010 at 2:58 pm
Or, as an inline function
CREATE FUNCTION dbo.fn_JulianDateConversion
(
@JulianDate VARCHAR(13)
)
RETURNS DATETIME
AS
BEGIN
RETURN(
DATEADD(YEAR, LEFT(@JulianDate, 4) - 1900, SUBSTRING(@JulianDate, 5, 3) - 1)
+ STUFF(STUFF(RIGHT(@JulianDate, 6), 3, 0, ':'), 6, 0, ':')
)
END
GO
SELECT dbo.fn_JulianDateConversion('1997090101636')
N 56°04'39.16"
E 12°55'05.25"
August 20, 2010 at 3:15 pm
Shorter is not always simpler. If you're going for shortest, then this is much more elegant.
SELECT DateAdd(Day
, Substring(@Source, 5, 3) - 1
, Stuff(Stuff(Stuff(@Source, 12, 0, ':'), 10, 0, ':'), 5, 3, '-01-01 '))
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 20, 2010 at 9:12 pm
That is actually an ordinal date.
http://en.wikipedia.org/wiki/Julian_Day
"The Julian day or Julian day number (JDN) is the number of days that have elapsed since 12 noon Greenwich Mean Time (UT or TT) on Monday, January 1, 4713 BC in the proleptic Julian calendar 1. That day is counted as Julian day zero. The Julian day system was intended to provide astronomers with a single system of dates that could be used when working with different calendars and to unify different historical chronologies.
...
...
The term Julian date is also used to refer to:
Julian calendar dates
ordinal dates (day-of-year)
The use of Julian date to refer to the day-of-year (ordinal date) is usually considered to be incorrect."
If anyone is interested in getting the Julian date, this date table function returns both the Julian Date and the Modified Julian date in columns JULIAN_DATE and MODIFIED_JULIAN_DATE. The Julian Date returned is as of Noon on the date in the table. The Modified Julian date is as of midnight. The function returns the ordinal date in column YEAR_DAY_OF_YEAR.
Date Table Function F_TABLE_DATE:
August 23, 2010 at 3:05 pm
Thanks to everyone who replied and especially Thank you to drew.allen who led me in the direction that produced that result I was looking for. All I had to change was the declaration of the @ConcatInfo variable to DATETIME (from VARCHAR) and it worked.
THAT is why is is simpler to do a SP in this design rather than shortening it as mentioned within the post. I agree with the assessment that smaller is not always simpler. However, I appreciate all of the posts very very much because it taught me an approach I was not familiar with before.
Thanks Again.
JT
August 23, 2010 at 3:15 pm
I apologize for posting a solution that was performance oriented.
Most people are interested in that.
Now I realize you wanted a more maintanable query at the expense of performance.
N 56°04'39.16"
E 12°55'05.25"
August 23, 2010 at 3:19 pm
No apologies needed "SwePeso". Your post was very helpful. I will take a closer look at it as I venture into the performance part of this. The post was to help me understand what was going on and where it was broken.
August 23, 2010 at 4:15 pm
So I am looking at the "Stuff" version of this solution and discovered that the length of the submitted value MUST be 13 characters. Any shorter and an "out-of-range" error is thrown. The original post (with the modification to make it work) handles this by substituting zeros for asbent values therefore '2009309' is 2009-11-05 00:00:00.0000 (midnight or 12:00AM) which is actually acceptable to the user population utilizing this solution. Any ideas on the "Stuff" version to have it handle variable charcters (upto 13 - I have been told)?
Thank You All In Advance.
August 24, 2010 at 5:45 am
This small change to SwePeso's version will handle values shorter than 13 chars:
CREATE FUNCTION dbo.fn_JulianDateConversion
(
@JulianDate VARCHAR(13)
)
RETURNS DATETIME
AS
BEGIN
--== Ensure date is always 13 chars in length
SELECT @JulianDate = LEFT(@JulianDate + '0000000000000',13)
RETURN (
DATEADD(YEAR, LEFT(@JulianDate, 4) - 1900, SUBSTRING(@JulianDate, 5, 3) - 1)
+ STUFF(STUFF(RIGHT(@JulianDate, 6), 3, 0, ':'), 6, 0, ':')
)
END
GO
August 24, 2010 at 9:07 am
Thank You Very Much. Works Great!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply