May 10, 2013 at 11:51 am
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.
May 10, 2013 at 12:10 pm
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
May 10, 2013 at 1:35 pm
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