Getting last month end date in a sybase query

Thanks so much for replying so quickly!

A1) I didn't know Sybase had a last_day function? I know Oracle has one where i would do
Ex: select * from MyTable where MyDate <= last_day(add_months(sysdate, -1));
      --Would give me the last day of the previous month which is what i want to do the same in a sybase query.

A2) I never thought about using dateadd. Thanks! I will give this a try. Just a follow up question will this work also if I had to run this query next year feb. ... would i get jan04, dec03, nov03 ?

See this link for Sybase date functions:

http://sybooks.sybase.com/onlinebooks/group-as/asg1251e/sqlug/@Generic__BookView?DwebQuery=date+functions

Q1) You can do this

select * from MyTable
where Datepart(yy, MyDate)  =  DatePart(yy, DateAdd(mm, -1, getdate()) and
         Datepart(mm, MyDate)  <= DatePart(mm, DateAdd(mm, -1, getdate())

Q2) dateadd will work for next year too.

Would you need further help?

A1)Anyway of limiting the months return to just the last 3 months? Right now i get all of 2003, basically year to date. I'm more looking for last 3 months. Was thinking maybe adding a having clause in it. That is why i wanted to find out how to find a month end date.

Try this.

select * from MyTable
where Datepart(yy, MyDate)  =  DatePart(yy, DateAdd(mm, -1, getdate()) and
         Datepart(mm, MyDate)  <= DatePart(mm, DateAdd(mm, -1, getdate())  and
         Datepart(mm, MyDate)  >= DatePart(mm, DateAdd(mm, -4, getdate())  


HTH

this will use the index on MyDate (if you have one) and will work across year boundaries

select * from MyTable
where MyDate >=  dateadd(mm, -2, dateadd(dd, 1 - datepart(dd,getdate()), getdate()))

ie when run on 23-Dec-2003, it will show all dates from 1-Oct-2003 onwards

OR

select * from MyTable
where MyDate >=  dateadd(mm, -3, getdate())

if you want 23-Sep-2003 onwards

oops, to strip the time portion from the starting date, wrap a
          convert( varchar,  ...  , 106 ) around the value

eg
select * from MyTable
where MyDate >=  convert( varchar, dateadd(mm, -2, dateadd(dd, 1 - datepart(dd,getdate()), getdate())), 106 )

OR

select * from MyTable
where MyDate >=  convert( varchar, dateadd(mm, -3, getdate()), 106 )

This may not work in the beginning of the year.

select * from MyTable
where Datepart(yy, MyDate)  =  DatePart(yy, DateAdd(mm, -1, getdate()) and
         Datepart(mm, MyDate)  <= DatePart(mm, DateAdd(mm, -1, getdate())  and
         Datepart(mm, MyDate)  >= DatePart(mm, DateAdd(mm, -4, getdate())  

But try this

select * from MyTablle
Where datediff(m , MyDateColumn, GetDate()) <= 3

HTH

namasi_navaretnam
that still won't use an index, it will table scan then entire table

Chris,

I thought any built in function used on column forced table scan in older versions of sybase , but not on version12. Is that not true?

namasi_navaretnam
it will if the function is on the column, but in my example the functions are not on the column, they are on getdate().

so, code like:
select * from MyTable
where MyDate >=  convert( varchar, dateadd(mm, -2, dateadd(dd, 1 - datepart(dd,getdate()), getdate())), 106 )

can be fully rationised to:
select * from MyTable
where MyDate >=  '1-Oct-2003'

and this will be done before the optimiser jumps in, as you can see, this will use an index  :)   .... (if one exists on MyDate)

the effect is the same as:
declare @mydate datetime
select @mydate = convert( varchar, dateadd(mm, -2, dateadd(dd, 1 - datepart(dd,getdate()), getdate())), 106 )
select * from MyTable where MyDate >=  @mydate

Yep. You are right!!

Thanks for all your help guys. I actually started to look into this datediff() function as HTH had recommended. So far in testing it has given me the dates i look for, even if i ran it on feb next year and so on. In my code i'm getting the diff number return in negatives, but thats ok its just the same as if it was positive. so in my where clause i say return all where datediff between -3 and -1. I will keep you posted.

HTH is not his name, it stands for "Hope that helps".

glad to see you are makeing some progress

Im so sorry. Should have read that better. I apologize. I meant to say namasi_navaretnam. =)

That is OK. No need to applogize. For datediff function first parameter is the datepart. Second parameter is the start date and third parameter is the end date. If Start Date is greater than End Date then you will get negative numbers.

Look into Chris's solution as well.

To measure performance try,

Select GetDate()
go
Select Statement
go
Select GetDate()

Measure the difference in time.

HTH

Namasi

hi,
try this logic:

declare @day,@mon,@yy
@day = datepart(dd,getdate())
@mon = datepart(mm,getdate())
@yy = datepart(yy,getdate())

string @temp_date
declare @date datetime
select @mon = @mon + 1
if @mon > 12 then
      select @mon = 1
      select @yy = @yy + 1
end if
select @day = 1
select @temp_date = String(@day) + "/" + String(@mon) + "/" + String(@yy)
select @date = convert(Datetime ,@temp_date)
select @date = datediff(dd,@date, -1)

Regards,
sandhiya

Hi,

I tried the following and it works, so far. Let me knwo if my logic is wrong??

  select  *    
  from temp_div
   where datediff(mm, convert(varchar(10), getdate(), 112), convert(varchar(10), ex_date, 112)) >= -3
     and datediff(mm, convert(varchar(10), getdate(), 112), convert(varchar(10), ex_date, 112)) <= -1
order by ex_date

Thanks.

llccoo
only the fact that this query will not and can not use and index. Please read comments above.
The only time this would be acceptable in a production environment, would be if the table will NEVER have more than 100 rows in it

if the query is going to return < 50% of the rows in the table, then I would suggest that the index (on ex_date) is required.

Whether you add then index or not, I would still write the query so it could use the index if it was there.

I actually tried both today.  I found out that my ex_date has an index ... so both queries worked well in running-time. taking the varchar part out of my query thru an error cuz the ex_date was define as smalldate. I have about 35k rows give or take. I'm still testing things out. But chris ill try your suggestion again and copy that table without an index and see what changes.

Thanks so much for helping me out. I would of never had known where to start.

LLccOO