Date

  • Hello all. I need to get the last day of a month based on the months name. I dont want to have to do a case statement i would like to use, if possible, (SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))),DATEADD(mm,1,getdate())),101)) which i use to get the last day of the current month. I just cant wrap my head around how to pass the datename or even the date number such as 1 for january and have the query return the last day of january. To add a dimension i also have the year so i would pass something like January 2012 or 1-2012...is this possible?

    Thank you for your help.

  • Figured it out. Had to do some string manipulation but i got it:) Would have been easy if i wasnt working with data that came from Unix.

    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,Cast(Field1 as varchar(10))+'/1/'

    + Case When Field2 = 'L' THEN '2012'

    When Field2 = 'T' THEN '2013' End))),DATEADD(mm,1,Cast(Field1 as varchar(10))+'/1/'

    + Case When Field2 = 'L' THEN '2012'

    When Field2 = 'T' THEN '2013' End)),101)

    from dbo.GJORHIST

  • zulmanclock (5/10/2013)


    Figured it out. Had to do some string manipulation but i got it:) Would have been easy if i wasnt working with data that came from Unix.

    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,Cast(Field1 as varchar(10))+'/1/'

    + Case When Field2 = 'L' THEN '2012'

    When Field2 = 'T' THEN '2013' End))),DATEADD(mm,1,Cast(Field1 as varchar(10))+'/1/'

    + Case When Field2 = 'L' THEN '2012'

    When Field2 = 'T' THEN '2013' End)),101)

    from dbo.GJORHIST

    There may be a better way if you would provide us with the information needed. Your original post was vague and your second post appears to be using data from a table.

Viewing 3 posts - 1 through 2 (of 2 total)

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