Solved

SQL: Get records with one spesific day number (date = 10th or 20th)

Posted on 2011-02-15

4 solutions

503 Views

Last Modified: 2012-06-22

I have a table containing users. Each user contains a smalldatetime field called Created.

In my stored Procedure (spGetUsers) I have 2 input parameters: @Day, @LastDayOfMonth. The @Day parameter can be integers from 1 to 31, the @LastDayOfMonth is '0' or '1'.

I need to return all users based on what day number the Created fields is.

Example: I want to return all users where Created day is 10th. and Status='1'. I don't care about the month or year.

There is also another issue. If the Parameter called @LastDayOfMonth (bit) is "1" the procedure shall return all users where Created day >= @Day AND Status = '1'.

Thanks for all tips :)

Comment

Question by:webressurs

[X]

Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

Help others & share knowledge
Earn cash & points
Learn & ask questions

Join The Community

4 Comments

LVL 39

Accepted Solution

by:Pratima Pharande

Pratima Pharande earned 125 total points

ID: 348954812011-02-15

you can add this in Store procedure

if ( @LastDayOfMonth = 1)
Begin
Select * from users where Day(Created) >= @Day
end
else
Begin

Select * from users where Day(Created) = @Day
end

LVL 13

Assisted Solution

by:devlab2012

devlab2012 earned 125 total points

ID: 348975622011-02-15

One correction to above comment:

if (@LastDayOfMonth = 1)
Begin
Select * from users where Day(Created) >= @Day AND Status = 1
end
else
Begin
Select * from users where Day(Created) = @Day AND Status = 1
end

LVL 41

Assisted Solution

by:Sharath

Sharath earned 250 total points

ID: 349019042011-02-15

You can do like this.

SELECT * FROM users 
 WHERE (@LastDayOfMonth = 1 AND [Status] = 1 AND DAY(Created) >= @Day) 
        OR (@LastDayOfMonth = 0 AND [Status] = 1 AND DAY(Created) = @Day)

Open in new window

LVL 41

Assisted Solution

by:Sharath

Sharath earned 250 total points

ID: 349019112011-02-15

or using DATEPART

SELECT * FROM users 
 WHERE (@LastDayOfMonth = 1 AND [Status] = 1 AND datepart(day,Created) >= @Day) 
        OR (@LastDayOfMonth = 0 AND [Status] = 1 AND datepart(day,Created) = @Day)

Open in new window

Featured Post

Question has a verified solution.

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

Tableau - How to Compute and Filter Prior Business Day

Article by: Brian

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

MongoDB Through a MySQL Lens

Article by: Percona

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.

Query Syntax
SQL
Databases
MySQL Server
MongoDB
NoSQL Databases, Percona

NetCrunch network monitor live walk-through

Video by: AdRem

NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

Network monitoring: why having a policy is the best policy?

Video by: AdRem

Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…