[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Select if between dates dynamic month and year

Posted on 2009-05-01
3
Medium Priority
?
688 Views
Last Modified: 2012-05-06
I have a table with a column of dates called COMPDATE
I need to select where is is the current month

So

SELECT COUNT(*) FROM COMPLETEDVISITS WHERE COMPDATE IS > [FIRST OF THE CURRENT MONTH] AND < [31 OF THE CURRENT MONTH]

how do i  do this in sql
0
Comment
Question by:AVONFRS
3 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24279534
SELECT COUNT(*) FROM COMPLETEDVISITS
WHERE date(year,COMPDATE) = datepart(year, getdate())
and date(month,COMPDATE) = datepart(month, getdate())
0
 
LVL 19

Expert Comment

by:erikTsomik
ID: 24279544
you need to use between function

SELECT COUNT(*) FROM COMPLETEDVISITS WHERE COMPDATE BETWEEN [FIRST OF THE CURRENT MONTH] AND  [31 OF THE CURRENT MONTH]
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 1000 total points
ID: 24279570
This will get you records where COMPDATE is after 2009-05-01 00:00:00.000 but before 2009-06-01 00:00:00.000 which should be every record in the current month.
SELECT COUNT(*) 
FROM COMPLETEDVISITS 
WHERE COMPDATE >= DATEADD(month, 0, DATEDIFF(day, 0, GETDATE()-DAY(GETDATE())+1)) AND COMPDATE < DATEADD(month, 1, DATEDIFF(day, 0, GETDATE()-DAY(GETDATE())+1))

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

834 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