qns about Date field

Question

Hi,
There is a Debit card expirationdate column in a field. In a storedProc, something like

Cast(Convert(varchar, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,sExpirationDate)+1,0)),101) + ' 12:00pm' as DateTime)
&
Cast(Convert(varchar, DateAdd(Day,-1,c.sally_ExpirationDate),101) + ' 12:00pm' as DateTime)

is used.

I am not what are these two means? can you please help me on this?

Answer 1

Can you provide the sample values for sExpirationDate and c.sally_ExpirationDate?

I assumed the dates as - 2020-08-15

First query returns the the last day of that Month and second gives the previous da

select Cast(Convert(varchar, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,'2020-08-15')+1,0)),101) + ' 12:00pm' as DateTime)
-- Output - 2020-08-31 12:00:00.000 - Last day of month
select Cast(Convert(varchar, DateAdd(Day,-1,'2020-08-15'),101) + ' 12:00pm' as DateTime)
-- Output - 2020-08-14 12:00:00.000 - Previous day 

This can also be done with below simple queries, just that you need handle data types and formats

select EOMONTH('2020-08-15')
select DATEADD(day,-1,'2020-08-15')

---

Please don't forget to Accept Answer and Up-vote if the response helped -- Vaibhav