SSRS Format four-digit month/year as month-day-year

SSAS and SSRS
1

I have a four-digit number representing an expiration date, i.e. 1020, which is October 2020.

I need to find out how many days from today there are before the expiration date.

Any help will be greatly appreciated!

Juniormint

2

if you are looking for how many days then one needs to know what October 2020 means. Is it the first of the month? 15th, 22nd?

3

Thanks for your response!

It's like the expiration date on a credit card. The 1st of each month can be assumed.

4

so would January 1, 2019 be 0119 or 119?

5

It would be 0119

6 
--sample data
create table #juniormint(expiration_date varchar(6))

  insert into #juniormint
  	select  '042019' union
	select '022019' union
	select '092019' union
	select '062019' union 
	select '122018' union
	select '012019' union
	select '082019' union
	select '072019' union
	select '102019' union
	select '052019' union
	select '112019' union
	select '032019' 

	;with cte
	as
	(
	select *, LEFT(expiration_date,2) + '/01/' + RIGHT(expiration_date,4) as clean_date
	   From #juniormint
	)
      --key here is datediff
	select datediff(dd,getdate(), clean_date) expires_in,*
	  from cte

	drop table #juniormint
1 Like
7

Thanks, Yosiasz. Couldn't get it to work, but will tinker with it later and let you know.

8 
WITH expiry AS
(  SELECT '0419' expdt UNION
	SELECT '0219' UNION
	SELECT '0919' UNION
	SELECT '0619' UNION 
	SELECT '1218' UNION
	SELECT '0119' UNION
	SELECT '0819' UNION
	SELECT '0719' UNION
	SELECT '1019' UNION
	SELECT '0519' UNION
	SELECT '1119' UNION
	SELECT '0319' )
SELECT expiry.expdt, DateDiff(dd,GetDate(), Cast('20'+Right(expiry.expdt, 2)+Left(expiry.expdt, 2)+'01' AS date)) DaysTillExpiry
FROM expiry;

image

9

If you are trying to do the computations using SSRS expressions, it should be something like this:
=Datediff(
"d",
Today(),
DateValue("20"+right(Fields!ExpiryDateString.Value,2) + "-"+ left(Fields!ExpiryDateString.Value,2) + "-01")
)

Crappy forum software does not format the code the way I was hoping it would! In the above, ExpiryDateString is the column in your dataset representing the expiry date as a string.