Sybase ASE: Get first or last day of previous, current or next month

Important: This post is about Sybase ASE. It will not work in iAnywhere.

If you want to provide some filter possibilities in your application showing the data stored for the previous, the current or the next month. So you basically need to figure out the first and last day of the corresponding month.
In case you cannot or do not want to do it in the application code itself, you can use simple SQL statements to get this info.

First you need to figure out the current date:

declare @today datetime
select @today=getdate()

Let’s start with the current month:

Today is January, 30th. So the first day of the month is January, 1st. Unfortunately, you cannot directly set the day to 1. But you can extract the day from the date, in our case 30 and 1 is 30 – (30 – 1), so:

declare @today datetime
select @today=getdate()
select dateadd(dd,-(day(@today)-1),@today)

This will return: Jan 1 2013 1:49PM

So basically we have the right date but for comparison purposes, we’d rather have midnight than 1:49pm. In order to do it, you need to convert it to a date string and back to a datetime:

declare @today datetime
select @today=getdate()
select convert(datetime, convert(varchar(10),dateadd(dd,-(day(@today)-1),@today),101))

Now we get: Jan 1 2013 12:00AM

if you’re only interested in a string containing the date, just drop the outer convert:

declare @today datetime
select @today=getdate()
select convert(varchar(10),dateadd(dd,-(day(@today)-1),@today),101)

Use another format than 101 if needed. The complete list of date conversion formats can be found here. For example, for the German date format, use 104 (dd.mm.yyyy).

Now let’s get the last day of the current month. This is basically the day before the first day of next month.

So first let’s get the first day of next month. This is actually just 1 month later than the first day of the current month:

declare @today datetime
select @today=getdate()
select convert(varchar(10),dateadd(mm,1,dateadd(dd,-(day(@today)-1),@today)),101)

This returns: 02/01/2013

Now let’s just substract one day and we’ll get the last day of the current month:

declare @today datetime
select @today=getdate()
select convert(varchar(10),dateadd(dd,-1,dateadd(mm,1,dateadd(dd,-(day(@today)-1),@today))),101)

This returns: 01/31/2013

Since we already have the first day of next month, let’s get the last day of next month. This is basically the same again but instead of adding 1 month, you add 2 months:

declare @today datetime
select @today=getdate()
select convert(varchar(10),dateadd(mm,2,dateadd(dd,-day(@today),@today)),101)

This returns: 02/28/2013

Now let’s tackle the previous month. The first day of last month is basically the first day of the current month minus 1 month:

declare @today datetime
select @today=getdate()
select convert(varchar(10),dateadd(mm,-1,dateadd(dd,-(day(@today)-1),@today)),101)

This returns: 12/01/2012

And then the last day of previous month. It is one day before the first day of the current month:

declare @today datetime
select @today=getdate()
select convert(varchar(10),dateadd(dd,-(day(@today)),@today),101)

This returns: 12/31/2012

9 thoughts on “Sybase ASE: Get first or last day of previous, current or next month

  1. Your last day of month has a bug in it.

    declare @today datetime
    select @today=’20120301′
    select convert(varchar(10),dateadd(mm,1,dateadd(dd,-day(@today),@today)),101)

    Will return 03/29/2012 instead of 03/31/2012

    You should instead add by 1 month and then subtract the number of days. This should cater for all months.

    e.g. select convert(varchar(10), dateadd(dd, -day(@today), dateadd(mm, 1, @today)),101)

    1. Actually the first statement in my post above (the one before I stupidly tried to optimize it).

      So this worked:
      select convert(varchar(10),dateadd(dd,-1,dateadd(mm,1,dateadd(dd,-(day(@today)-1),@today))),101)

      But this not:
      select convert(varchar(10),dateadd(mm,1,dateadd(dd,-day(@today),@today)),101)

      I’ve removed the second one from the post… Thanks for reporting this error !

  2. Thanks Henri, I needed to get the first day of next month and I used your code for this and it worked perfectly. Very helpful.

  3. How can this work for getting the first day of the current year??

    I need to do a BETWEEN “Get First Day of Current Year” AND GETDATE()

    thank you in advance

Leave a Reply

Your email address will not be published. Required fields are marked *