CODEWITHSUNDEEP
datesql-server-2008-r2
HollyAnne6
HollyAnne6

Reputation: 93

SQL Server 2008R2 : how to convert date for month column?

This is my request: based on Review Date, populate the month of review in MM/DD/YYYY format. E.g. if Review Date is 8/17/2016, Month of Review should populate 8/1/2016.

Originally, I had it coded 

datename(month, c.CreatedDate) as 'Month'

but requestor now wants it 8/1/2016 instead of 'August' regardless the day of the month.

Thank you

Upvotes: 0

Views: 67

Answers (1)

fred
fred

Reputation: 465

You can use dateadd and eomonth functions

dateadd(day, 1, eomonth(dateadd(month, -1, your_date)))

EDITED

Sorry, eomonth is available since SQL Server 2012

select dateadd(day, -(datepart(day, your_date))+1, CONVERT(DATE, your_date, 101))

Upvotes: 1

Related Questions