Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date Expand / Collapse
Author
Message
Posted Friday, May 10, 2013 11:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 2:31 PM
Points: 227, Visits: 353
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.
Post #1451703
Posted Friday, May 10, 2013 12:10 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 12, 2014 2:31 PM
Points: 227, Visits: 353
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

Post #1451708
Posted Friday, May 10, 2013 1:35 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 20,857, Visits: 32,877
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1451728
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse