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

How To return Year & Month in this format: 2013-06 Expand / Collapse
Author
Message
Posted Wednesday, June 19, 2013 4:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 3:48 AM
Points: 62, Visits: 212
Hi All,

I need to return the minimum date from my table and manipulate it so it starts from the first of the month

e.g 2012-06+'-'+'01+ and use this as the minimum date: '2012-06-01- instead of the 22nd

Select MIN(dtePostedToWebsiteDate) --Here's the minimum date >>> 2012-06-22 09:07:42.413
FROM dtlVacancyPostAudit

How do I achieve this?

Thanks

Teee
Post #1465066
Posted Wednesday, June 19, 2013 5:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:26 PM
Points: 2,422, Visits: 7,437
Teee (6/19/2013)
Hi All,

I need to return the minimum date from my table and manipulate it so it starts from the first of the month

e.g 2012-06+'-'+'01+ and use this as the minimum date: '2012-06-01- instead of the 22nd

Select MIN(dtePostedToWebsiteDate) --Here's the minimum date >>> 2012-06-22 09:07:42.413
FROM dtlVacancyPostAudit

How do I achieve this?

Thanks

Teee


Select DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(dtePostedToWebsiteDate)), 0)
FROM dtlVacancyPostAudit;

Or

Select CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(dtePostedToWebsiteDate)), 0) AS DATE)
FROM dtlVacancyPostAudit;




Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1465073
Posted Wednesday, June 19, 2013 5:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, June 30, 2014 3:48 AM
Points: 62, Visits: 212
Thank you so much, this works perfectly
Post #1465075
Posted Thursday, June 20, 2013 9:43 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:19 PM
Points: 276, Visits: 1,000
I like that answer, thanks for sharing. I know some may find that logic hard to follow, at least some that I know. :) We have always used the following code to get the first of the month. Of course you would replace the getdate() with the date from your table.

select DATEADD(dd, -(DATEPART(dd, GETDATE())) +1, convert(char(10), GETDATE(), 101))
Post #1465791
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse