default any date to end of month

[system]

Hi guys

I need to convert some dates in a table to default to the end of month… Here is what I want to do

Wxisting_Date                                                     Expected result

2015-04-01 00:00:00000                                    30/04/2015 12:00:00 AM

2017-01-01 00:00:0000                                      31/07/2017 12:00:00 AM

What function can I use to convert this to end of month dates?

cheers

[Moderator Edit: Added code formatting - Andreas]

---

nutty (BOB member since 2015-10-19)

[system]

Hi,

The syntax depends on the database that you use.

Check here in this sticky topic:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

---

Marek Chladny (BOB member since 2003-11-27)