Problem with select query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Problem with select query

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-07-13 : 10:13:59

I am building a sql string in a web app. Here's my code that builds the string. Belwo I have shown the string it actually builds after selecting the parameters. My problem is that it is returning all the values for July, not just though after the current date.

Case "ThisMonth"
mySQLstr = "SELECT * from vweAvailableRooms where BD_RoomRef IN (" & Me.Session("RoomRefLogin") & ")" _
& "and Month(BD_DateRequired) = '" & Month(Me.Session("DateRequiredLogin")) & "' and Year(BD_DateRequired) = '" & Me.Session("YearLogin") & "'" _
'Check if history required")
If Me.Session("History") = "No" Then
mySQLstr = mySQLstr & " and BD_DateRequired >= " & Today.ToShortDateString
End If
mySQLstr = mySQLstr & " ORDER BY BD_RoomRef, BD_DateRequired Asc"

This is what it returns

SELECT * from vweAvailableRooms where BD_RoomRef IN ('NW0C')and Month(BD_DateRequired) = '7' and Year(BD_DateRequired) = '2006' and BD_DateRequired >= 13/07/2006 ORDER BY BD_RoomRef, BD_DateRequired Asc

mr_mist
Grunnio

1870 Posts

Posted - 2006-07-13 : 10:18:19

I'd sugges tthat the problem is that the Me.Session("History") value is not equal to no, so you're not getting that part of the where clause added in.

-------
Moo. :)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-07-13 : 10:21:39

It is being added in

SELECT * from vweAvailableRooms where BD_RoomRef IN ('NW0C')and Month(BD_DateRequired) = '7' and Year(BD_DateRequired) = '2006' and BD_DateRequired >= 13/07/2006 ORDER BY BD_RoomRef, BD_DateRequired Asc

I have also stepped through the code in debug....

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-07-13 : 10:23:33

Try to format Today.ToShortDateString
to give "MM/DD/YYYY" format
[urs is in "DD/MM/YYYY" format]

Srinika

Pinto
Aged Yak Warrior

590 Posts

Posted - 2006-07-13 : 10:39:00

I don't think it is that, because if the user choose just one day it works and that date is dd/mm/yyyy (I am in UK).

Is it because the earlier dates satisfy the rule that they are in month 7 and year 2006 ? Do I need to change it to- Month = 7 and >= today and year = 2006 and >= today

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-13 : 10:53:35

I would suggest making a stored proc, and passing in parameters instead of building a string and executing it.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-13 : 10:55:05

quote:
Originally posted by Pinto

I don't think it is that, because if the user choose just one day it works and that date is dd/mm/yyyy (I am in UK).

Is it because the earlier dates satisfy the rule that they are in month 7 and year 2006 ? Do I need to change it to- Month = 7 and >= today and year = 2006 and >= today

Try formatting your date in universal YYYYMMDD format !

Harsh Athalye
India.
"Nothing is Impossible"

mr_mist
Grunnio

1870 Posts

Posted - 2006-07-13 : 10:55:30

quote:
Originally posted by Pinto

It is being added in

SELECT * from vweAvailableRooms where BD_RoomRef IN ('NW0C')and Month(BD_DateRequired) = '7' and Year(BD_DateRequired) = '2006' and BD_DateRequired >= 13/07/2006 ORDER BY BD_RoomRef, BD_DateRequired Asc

I have also stepped through the code in debug....

Yeah, I see that in your post, but it doesn't follow, because SQL would parse that and only show you the results after 13/7. Which means that the actual thing that's getting to SQL either doesn't have that bit in, or your column isn't a datetime.

Try tracing the command in SQL Profiler.

-------
Moo. :)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-13 : 10:59:44

Also I don't find the quotes around your date...I don't know whether SQL Server can handle that...but that's the thing to try !

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources