Share via

How to query based on current date time

Carlton Patterson 741 Reputation points
Jun 6, 2023, 2:25 PM

I need help composing a query that will filter on table based on current_date() function

For example I have the following sample table

User's image

I would like a query that will retrieve the as_of_date based on on the current_date.

So todays date is 06/06/2023 so a query to:

Retrieve Last Month End based on todays date would be:

User's image

Retrieve Last Quarter End based on todays date would be:

User's image

Retrieve Last Year End based on todays date would be:

User's image

Last Month End but one

User's image

Last Quarter End but one

User's image

Last Year End but one

User's image

I think I'm going to need a CASE WHEN statement.

Any thoughts

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,243 questions
{count} votes

Accepted answer
  1. Erland Sommarskog 114.8K Reputation points MVP
    Jun 6, 2023, 9:13 PM

    Last month end:

    SELECT eomonth(dateadd(MONTH, -1, sysdatetime()))
    

    Last year end:

    SELECT datefromparts(YEAR(sysdatetime()) - 1, 12, 31)
    

    Last quarter end (this is the ugly one):

    SELECT eomonth(dateadd(quarter, -1,
                   datefromparts(year(sysdatetime()), (month(sysdatetime()) / 4 + 1) * 3, 1)))
    
    

    The but-ones are left as an exercise to the reader. (They should be easy to do, given the above).

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. PercyTang-MSFT 12,506 Reputation points Microsoft Vendor
    Jun 7, 2023, 2:43 AM

    Hi @Carlton Patterson

    You can take a look at this.

    Last Month End

    select * from #tmpTable where as_of_date = convert(varchar(100),dateadd(day,-day(getdate()),getdate()),103);
    

    Output:

    User's image

    Last Quarter End

    select * from #tmpTable where as_of_date = convert(varchar(100),dateadd(quarter,datediff(quarter,0,getdate()),-1),103);
    

    Output:

    User's image

    Last Year End

    select * from #tmpTable where as_of_date = convert(varchar(100),dateadd(year,datediff(year,0,getdate()),-1),103);
    

    Output:

    User's image

    Best regards,

    Percy Tang


    If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Carlton Patterson 741 Reputation points
    Jun 7, 2023, 3:52 PM

    I have accepted Erland response as the best answer because his answer retrieves the correct result if the as_of_date field was a DateType.

    It's my fault for posting the original sample table with the as_of_field as an string/varchar, which lead Percy to do carry out conversion on the as_of_date field, but thanks anyway - I am grateful for the help

    Thanks

    P.S.

    If someone could help with the Last Quarter End but one that would be most appreciated.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.