CODEWITHSUNDEEP
sqlsql-serverdate
GabyLP
GabyLP

Reputation: 3781

sql- move each date to 1st day of the month

I need to take every date to the first day of the month. For example if I have: 20140103 I need to have 20140101 I thought a good idea could be loaddate - difference between loaddate and 1st date and I wrote:

loaddate- DATEdiff(day, day(loaddate),loaddate)

But the result is wrong. How can I solve this???

Thanks

Upvotes: 0

Views: 310

Answers (1)

Habib
Habib

Reputation: 223247

For SQL Server You can do:

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(loaddate)-1),loaddate),101)

For:

SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(GETDATE())-1),GetDATE()),101)

You will get back: 09/01/2014

Upvotes: 1

Related Questions