T-SQL YTD query SSMS into IBM DB2

[fka0006]

Hi,
I am trying to build a YTD query for my reporting solution the following query in DB2, and the following query is from SSMS. Can anyone help me writing this qury in DB2? I will appreciate any help and guidance.

For example if the report runs on April 7th, 2016, so it will take the data from Jan 1st, 2016 to March 31st, 2016.
Moreover, when the [highlight #FCE94F]report run on Jan 7th, 2017[/highlight], so it will [highlight #FCE94F]take the data from Jan 1st, 2016 to Dec 31st, 2016[/highlight].
When it will [highlight #FCE94F]run on Feb, 7th 2017[/highlight], so it will take the data from [highlight #FCE94F]Jan 1st, 2017 to Jan 31st, 2017[/highlight].

--First of Every Month
where date >= case when month(getdate())=1 then convert(varchar(4),year(getdate())-1)+ '/01/01'
else convert(varchar(4),year(getdate()))+ '/01/01'end


--end of every month
where date < case when month(getdate())=1 then convert(varchar(4),year(getdate())-1)+ '/01/01'
else convert(varchar(4),year(getdate()))+ '/' +convert(varchar(2),month(getdate()))+'/01' end

Thanks and Regards,
Fka0006

 

[MarcLodge]

Hi fka006,
I've not been to this site in absolutely ages so was pleased to find a nice problem on my return!

The main problem you have with writing this in SQL is that the end day of the previous month can change from 31, to 30 or 28 and 29. What you therefore need to do is build a date of the 1st day of the run month, then subtract 1 day from it. This will give you your end date. In the following example I've used 7th Jan 2017 as a hard coded literal, this will need to change to your run date variable or the system variable CURRENT_DATE:

DATE(SUBSTR(CHAR('2017-01-07'),1,7)||'-01') - 1 DAY

Once you have this date, you can work on it further to extract the year of that run month and concatenate it with the 1st of January of that year (whatever it happens to be, so:

DATE(YEAR(DATE(SUBSTR(CHAR('2017-01-07'),1,7)||'-01') - 1 DAY)||'-01-01')

If you then stitch these two dates into a BETWEEN statement, you should get the data you are after.

Hope this helps.
Marc