Monday, March 26, 2012

Julian Dates

I want to derive a date from a string field where the string will have the date embeded in the following format YYJJJ, for example 02364 = Dec 30 2002. Is there a way to do this using Transact-Sql via Sql Server?-- If it where in a procedure
DECLARE @.strDate varchar(5)
DECLARE @.dtDate smalldatetime

SET @.strDate = '02364'

SET @.dtDate = DATEADD(dd, CAST(RIGHT(@.strDate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(@.strDate, 2), 3))

SELECT @.dtDate

-- Using within a query
CREATE TABLE #tmp_mydates (mydate varchar(5))

INSERT INTO #tmp_mydates VALUES ('02364')
INSERT INTO #tmp_mydates VALUES ('02264')
INSERT INTO #tmp_mydates VALUES ('02164')
INSERT INTO #tmp_mydates VALUES ('02004')
INSERT INTO #tmp_mydates VALUES ('02002')

SELECT mydate, DATEADD(dd, CAST(RIGHT(mydate, 3) AS integer) - 1, CONVERT(smalldatetime,'01/01/' + LEFT(mydate, 2), 3)) AS myconverteddate FROM #tmp_mydates|||I want to derive a date from a string field where the string will have the date embeded in the following format YYJJJ, for example 02364 = Dec 30 2002. Is there a way to do this using Transact-Sql via Sql Server?

Q1 [Is there a way to do this using Transact-Sql via Sql Server?]
A1 You may consider creating your own derived "JulianDate" user datatype; and also creating your own user functions or stored procedures to properly interpet, convert to and from, and generally handle them as you might require. (I've found the strategy helpful in handling similar customized Julian date formats for special applications) For example:
dbo.fn_MyJulianDate_Add,
dbo.fn_MyJulianDate_Diff,
dbo.fn_ConvertStandardDate_To_MyJulianDate, dbo.fn_ConvertMyJulianDate_To_StandardDate,
etc.

No comments:

Post a Comment