Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to show purchases that were made on the last day of the month using SQL query.

Posted on 2011-10-17
3
Medium Priority
?
218 Views
Last Modified: 2012-05-12
I am trying to display all purchases made on the last day of the month in a calendar year. I know how to show purchases made on the 1st of every month but not the other way around. Here is my query for displaying all purchases made during the first of the month, anyone know how to show the last day of the month?

SELECT prod_id,
         purchase_id,
         purchase_date
FROM purchase_details
WHERE DAY(order_date) = 1
0
Comment
Question by:Jintonix415
3 Comments
 
LVL 39

Accepted Solution

by:
Aaron Tomosky earned 1600 total points
ID: 36983696
0
 
LVL 7

Assisted Solution

by:Alpha Au
Alpha Au earned 400 total points
ID: 36983727
not a clever method,
but you can have a look..
select DAYFIELD
, dateadd(d,-1,dateadd(m,1,convert(datetime,cast(year(DAYFIELD)as varchar)+'/'+ cast(month(DAYFIELD) as varchar) + '/01')))
, dateadd(m,1,convert(datetime,cast(year(DAYFIELD)as varchar)+'/'+ cast(month(DAYFIELD) as varchar) + '/01')) 
from TABLE1 
where DAYFIELD 
between dateadd(d,-1,dateadd(m,1,convert(datetime,cast(year(DAYFIELD)as varchar)+'/'+ cast(month(DAYFIELD) as varchar) + '/01')))
and dateadd(m,1,convert(datetime,cast(year(DAYFIELD)as varchar)+'/'+ cast(month(DAYFIELD) as varchar) + '/01')) 
order by DAYFIELD

Open in new window

0
 

Author Closing Comment

by:Jintonix415
ID: 36983782
aarontomosky thanks for the research this helped!

alphaau, this worked too, a bit cumbersome but did the job.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question