Avatar of HKFuey
HKFueyFlag for United Kingdom of Great Britain and Northern Ireland

asked on 

SQL Syntax for previous month end

Hi,

I have this: DATEDIFF(D, dbo.SLPostedCustomerTran.DueDate, GETDATE())

I actually want the difference in days from the end of the previous month not current date.

The result would be correct if the report is run on the last day of the month but I would like it to be able to run any time in the current month.
SQL

Avatar of undefined
Last Comment
Scott Pletcher
Avatar of HKFuey
HKFuey
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

I think I have it:
DATEDIFF(D, dbo.SLPostedCustomerTran.DueDate, EOMONTH(DATEADD(month, - 1, CURRENT_TIMESTAMP)))

Credit:
https://datacatchup.com/how-to-get-last-day-of-previous-month-in-sql/
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

Try
DATEDIFF(D, dbo.SLPostedCustomerTran.DueDate, dateadd(day,-1,convert(datetime,substring(convert(varchar,getdate(),112),1,6)+'01',112)))

Open in new window

Value below is to get last day of previous month

select dateadd(day,-1,convert(datetime,substring(convert(varchar,getdate(),112),1,6)+'01',112))

Open in new window

Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>Value below is to get last day of previous month

Yes, there are MANY ways do get it and some, are way more complex than they need to be.

@Peter,
Why should yours be used over a much simpler version?
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Avatar of HKFuey
HKFuey
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Hi Scott, this is working for me but I appreciate the correction.

DATEDIFF(D, dbo.SLPostedCustomerTran.DueDate, EOMONTH(DATEADD(month, - 1, CURRENT_TIMESTAMP)))


It's working NOW (today).  It should work on May 27, 28, 29 and 30.  But, will it work correctly specifically on May 31, June 30, etc.?
Avatar of HKFuey
HKFuey
Flag of United Kingdom of Great Britain and Northern Ireland image

ASKER

Re "Will it work correctly specifically on May 31, June 30, etc.? "

If I look at an invoice due date of '2021-06-30' for a customer with EOM terms I get -61 (which is correct)

This also returns -61

DATEDIFF(D, dbo.SLPostedCustomerTran.DueDate, EOMONTH(DATEADD(month, - 1, '31/05/2021')))
Ok, but hmm, that doesn't match the statement in your original q:

"The result would be correct if the report is run on the last day of the month."

But as long as you're getting correct results, it's all good.
SQL
SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.

61K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo