Search code examples
sqlsybase

Pulling data from Sybase SQL database using rolling window

I wish to pull three years of data from a database but right now I have to specify the dates in a number of sections in my code using a between statement:

BETWEEN '2015-10-01' AND '2018-09-30'

Since the database only contains valid data from the previous month backwards, I wish to take the end of the last month and go back three years.

I found this tutorial where the author shows how to do this in SQL server and I've tried to adapt it but my RDBMS is throwing errors in the datediff function

----Last Day of Previous Month
SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))
LastDay_PreviousMonth

My code looks as follows but I the error I am seeing is about converting 0 to a date.

DECLARE @date DATE
SET @date = getdate()

SELECT dateadd(second,-1,dateadd(mm, DATEDIFF(m,0,GETDATE()),0))

If anyone has any suggestions I would be very grateful for your guidance.

Solution

  • In your WHERE clause, you could use this condition:

    DATEDIFF(month, [YourDateColumn], GETDATE()) BETWEEN 1 AND 36