CODEWITHSUNDEEP
c#.netsql
Trey Copeland
Trey Copeland

Reputation: 3527

Month/Year SQL to correct format

I have a textfield that users input the date using a modified jquery datepicker. The textfield has the Month and Year such as 'July 2011'

I need to run a query to search for results between July 1, 2011 and July 31, 2011. My date in the database is a smalldatetime, so I need my query to look like this:

select * from members where signupdate between 'july 1 2011' and 'july 31 2011'

How can I get the user inputted date of July 2011 converted to 'July 1 2011' and 'July 31 2011'?

EDIT

I'm only getting a 0 value from InvalidCount but I know I have one record in there as a test. Why isn't it being counted?

MY PROC:

SELECT
(SELECT COUNT(*) FROM dbo.Members m WHERE m.memberID = @pMemberID AND m.SignUpDate BETWEEN @pDate AND DATEADD(MONTH, 1, @pDate)-1) AS 'SignUpDate',
COALESCE(SUM(m.ValidCount), 0) AS ValidCount,
COALESCE(SUM(m.InvalidCount), 0) AS InvalidCount

FROM
dbo.Members m

INNER JOIN 
dbo.MemberStats ms ON m.MemberID = ms.MemberID  

WHERE 
m.SignUpdate BETWEEN @pDate AND DATEADD(MONTH, 1, @pDate)-1

Upvotes: 0

Views: 266

Answers (4)

Vasea
Vasea

Reputation: 5333

Firstly, you must convert the string representation to a valid DateTime struct object on the server. You can use var date = DateTime.ParseExact("july 1 2011", "MMMM dd yyyy", CultureInfo.CurrentUICulture) or DateTime.TryParse. Then you pass this into your SqlCommand as parameters. Never use strings when querying, especially when it comes from user input.

Upvotes: 0

MatBailie
MatBailie

Reputation: 86715

The exact syntax depends on the SQL Engine, but if you start with the 1st of the month, then add 1 month, and finally subtract 1 day; you get the end of the month.

(I'll assume MS SQL Server to match your C# tag)

SELECT
  *
FROM
  members
WHERE
  signupdate BETWEEN @param AND DATEADD(MONTH, 1, @param) - 1

Upvotes: 1

Austin Salonen
Austin Salonen

Reputation: 50225

If the between isn't required, you can use DatePart.

Untested example:

where DATEPART(yyyy, SignupDate) = 2011 and DATEPART(m, SignupDate) = 7

Upvotes: 1

Arjun Shetty
Arjun Shetty

Reputation: 1585

convert varchar to appropiate format you want and then compare check the list of formats. Hope this helps dude.

convert date

Upvotes: 0

Related Questions