CODEWITHSUNDEEP
sqlsybase
TheGoat
TheGoat

Reputation: 2867

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.

Upvotes: 0

Views: 515

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35593

Don't use between and the job becomes far easier. Use less than the first day of the current month which accurately locates everything before that date (instead of last day of previous month). Subtract 3 years from that same date and use >= for the starting point.

Select *
From yourtables
where datecol >= dateadd (year,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0) )
And datecol < DATEADD(mm,DATEDIFF(m,0,GETDATE()),0)

Please don't use any solution that use 23:59:59 as the end of a day. It is not the end of a day and several data types now support sub-second time precision.

If you really cannot use zero in the date functions simply use the base date of '1900-01-01' instead

SELECT
  DATEADD(YEAR, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), '1900-01-01'))
, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), '1900-01-01')
;

This should also work (it does in this demo here):

SELECT
  DATEADD(YEAR, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0))
, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)
;

Upvotes: 0

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

In your WHERE clause, you could use this condition:

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

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Your current code looks good without any error, but you can use EOMONTH() instead.

However, the difference would be in return types your code would return date-time while eomonth() would return date only.

DECLARE @date DATE
SET @date = getdate()

SELECT EOMONTH(@date, -1)

Upvotes: 0

Related Questions