I need to take two fields and make a Date field

  • randyetheridge

    SSCommitted

    Points: 1703

    my table is as follows

    YearID  2019

    PeriodIDValue  P12

    PeriodID  December

    I need to create a single field  12/31/2019

    of course if the values are 2020, P1, January I need 1/31/2020

    or if 2019,P5, May  I need 5/31/2019 etc.

    I cannot figure out how to do this.

    I was trying Case When

    i.e.

    CASE

    WHEN PeriodIDValue = 'P1' THEN '1/31'&YearID

    WHEN PeriodIdValue = 'P2' THEN '2/28'&YearID

    etc

    Else '12/31/2019' END AS DateID

    but I was getting a conversion failure on varchar and of course every 4 years the code would be wrong, and it just seemed to brute force

    any help is appreciated.

     

     

     

     

  • anthony.green

    SSC Guru

    Points: 112358

    Something like the below?

    DECLARE @TestTable TABLE (YearID INT, PeriodIDValue VARCHAR(3), PeriodID VARCHAR(10))
    INSERT INTO @TestTable VALUES
    (2019,'P12','December'),
    (2019,'P5','May'),
    (2020,'P1','January')

    SELECT *,
    --THIS DATE ADD GOES BACK TO THE LAST DAY OF THE MONTH
    dateadd(day,-1,
    --THIS DATE ADD GOES TO THE START OF THE NEXT MONTH
    dateadd(mm, datediff(mm, 0,
    --DATEFROMPARTS HERE TO BUILD THE 1ST OF THE MONTH FROM THE SAMPLE DATA
    DATEFROMPARTS (YearID,replace(PeriodIDValue,'P',''),1)
    --THEN ADD A MONTH
    ) + 1, 0)
    --THEN REMOVE A DAY
    )
    FROM @TestTable
  • randyetheridge

    SSCommitted

    Points: 1703

    ok basically that worked like a charm.  but the answer was 2019-12-31 00:00:00.000

    I need 12/31/2019

     

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    If it's for storing in a table, don't worry - it'll get stored the same way no matter how it's presented to you.  If it's for you to see in a result set, either have your front end format it for you, or use CONVERT with the appropriate date style.

    John

  • randyetheridge

    SSCommitted

    Points: 1703

    ok, so this is a view, that is used to send a TXT file to a third party application.  so I need to use CONVERT.  I will need to research. thanks

  • anthony.green

    SSC Guru

    Points: 112358

    +1 for what John said, formatting like that should be done in the application layer.

    Let SQL store it and present it as a native SQL datatype, dates should be handled as dates, not strings and should be handled in an ISO way so that all operations on them are universal.

    But the below will convert it to style 101 for the US mm/dd/yyyy format you need

    DECLARE @TestTable TABLE (YearID INT, PeriodIDValue VARCHAR(3), PeriodID VARCHAR(10))
    INSERT INTO @TestTable VALUES
    (2019,'P12','December'),
    (2019,'P5','May'),
    (2020,'P1','January')
    SELECT *,
    CONVERT(VARCHAR(10),
    --THIS DATE ADD GOES BACK TO THE LAST DAY OF THE MONTH
    dateadd(day,-1,
    --THIS DATE ADD GOES TO THE START OF THE NEXT MONTH
    dateadd(mm, datediff(mm, 0,
    --DATEFROMPARTS HERE TO BUILD THE 1ST OF THE MONTH FROM THE SAMPLE DATA
    DATEFROMPARTS (YearID,replace(PeriodIDValue,'P',''),1)
    --THEN ADD A MONTH
    ) + 1, 0)
    --THEN REMOVE A DAY
    )
    ,101)
    FROM @TestTable
  • randyetheridge

    SSCommitted

    Points: 1703

    ok so got that,

    Convert(CHAR(10),DATEADD(day,-1,DATEADD(mm,DATEDIFF(mm,0,DATEFROMPARTS(YearID,REPLACE(PeriodIDValue,'P',''),1))+1,0)),101) AS PeriodEndDate

     

    However I just realized/remembered my test server is on 2012, but my production server will not be on 2012(I.e. still on 2008) until Feb 24, 2020.

    in 2008 DATEFROMPARTS is not valid

    is there any replacement for DATEFROMPARTS that would work in 2008?

     

     

  • John Mitchell-245523

    SSC Guru

    Points: 148652

    Yes, you just need a couple more DATEADDs.   Start with a base date, say 1999-12-31, and add the appropriate number of years, then the appropriate number of months.

    John

  • Jeffrey Williams 3188

    SSC Guru

    Points: 88260

    Here is an idea:

    Declare @yearID int = 2019
    , @periodIDValue varchar(3) = 'P12';

    Select dateadd(day, -1, dateadd(month, 1, cast(@yearID * 10000 + (replace(@periodIDValue, 'P', '') * 100) + 1 As char(8))))

    For 2012 and above - you can return a DATE data type with these:

     Select eomonth(cast(@yearID * 10000 + (replace(@periodIDValue, 'P', '') * 100) + 1 As char(8)))
    , eomonth(datefromparts(@yearID, replace(@periodIDValue, 'P', ''), 1))

    And if you need it as a datetime:

     Select cast(eomonth(cast(@yearID * 10000 + (replace(@periodIDValue, 'P', '') * 100) + 1 As char(8))) As datetime)
    , cast(eomonth(datefromparts(@yearID, replace(@periodIDValue, 'P', ''), 1)) As datetime)

     

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • randyetheridge

    SSCommitted

    Points: 1703

    thanks that worked in both 2008 and 2012.  code is updated and users are happy

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply