Results 1 to 3 of 3

Thread: Julian Dates

  1. #1
    Join Date
    Nov 2002
    Location
    North West Pennsylvania
    Posts
    1

    Unanswered: 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?

  2. #2
    Join Date
    Nov 2002
    Location
    England
    Posts
    4

    Talking Enjoy

    -- 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

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Re: 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?
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •