SQL "Date greater than" query

[Mark B]

Hi all,

I'm trying to find records in an SQL table that are newer than a specified
date. Vie the code.

TheDueDate = DateAdd("d",-1,FormatDateTime(Date))

Response.write TheDueDate & "<br><br>"

rs.Open "Select * From [StudentWorkDue] WHERE [DueDate] >= '" & TheDueDate &
"'", conn, 1,2

Do while not RS.EOF

Response.write rs.fields(1) & "<br>"

rs.movenext

loop

rs.close

If say, I want to display all records in my [StudentWorkDue] table newer
than 03/08/2009, stored in my SQL Server 2005 database, and the [DueDate]
column is a "smalldatetime" I'm absolutely stumped as to why this, or any
other expression I try, CONTINUALLY returns the 31/07/2009!

Notice the code is trying to return due dates that are newer than yesterday
(but including yesterday as well).

I'm aware there are difficulties doing arithmetic on dates, but I'd LOVE to
know where I'm going wrong!

Thanks

Mark

[Scott Morris]

"Mark B" <marxi...@hotmail.com> wrote in message
news:%23xYD4$OFKHA...@TK2MSFTNGP03.phx.gbl...

> Hi all,
>
> I'm trying to find records in an SQL table that are newer than a specified
> date. Vie the code.
>
> TheDueDate = DateAdd("d",-1,FormatDateTime(Date))
>
> Response.write TheDueDate & "<br><br>"
>
> rs.Open "Select * From [StudentWorkDue] WHERE [DueDate] >= '" & TheDueDate
> & "'", conn, 1,2
>
> Do while not RS.EOF
>
> Response.write rs.fields(1) & "<br>"
>
> rs.movenext
>
> loop
>
> rs.close
>
> If say, I want to display all records in my [StudentWorkDue] table newer
> than 03/08/2009, stored in my SQL Server 2005 database, and the [DueDate]

So is this August 3 or is it March 8?

> column is a "smalldatetime" I'm absolutely stumped as to why this, or any
> other expression I try, CONTINUALLY returns the 31/07/2009!
>
> Notice the code is trying to return due dates that are newer than
> yesterday (but including yesterday as well).
>
> I'm aware there are difficulties doing arithmetic on dates, but I'd LOVE
> to know where I'm going wrong!

See your answer to the prior question. It seems that the db engine
interprets your date as March 8. Read the following, especially the section
on input recommendations.

http://www.karaszi.com/sqlserver/info_datetime.asp

[Paul Shapiro]

Maybe SQLServer is interpreting your date using US format, mm/dd/yyyy and
sees 03/08/2009 as March 8? Try something like:
strDueDate = Format$(TheDueDate, "yyyy.mm.dd") and using the string version
in your query:
sQuery="Select * From [StudentWorkDue] WHERE [DueDate] >= '" & strDueDate &
"'"

"Mark B" <marxi...@hotmail.com> wrote in message
news:%23xYD4$OFKHA...@TK2MSFTNGP03.phx.gbl...

[Erland Sommarskog]

Mark B (marxi...@hotmail.com) writes:
> rs.Open "Select * From [StudentWorkDue] WHERE [DueDate] >= '" & TheDueDate

> "'", conn, 1,2

No, that is *not* how you submit queries!

cmd = "Select * From StudentWorkDue WHERE DueDate > ?"
cmd.Parameters.Append _
cmd.CreateParameter("@DueDate", adDateTime, adParamInput,, TheDueDate

You should *never* interpolate parameters into the query string. *Always*
use parameterised statements.

There are three vbery important reasons for this:

1) Protection against SQL injection. What if the user types
"20000101' DROP TABLE ImportantTable" in the input field?
2) Better use of the SQL Server cache, you get a query plan that can
be reused for other dates.
3) No headache of handling dates, as the date will be passed as a
binary string, and any date string will be interpreted according
to the user's regional settings.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Mark B]

Erland, I am totally going to check your suggestions out. I had no idea
commands could be issued in such a way (My teacher is Google).

To the other fellows, I discovered the problem (and I have NO IDEA why this
is so).

If you open the table in the SQL Management Console, it will display the
date as 4082009 12:00:00 AM, being 4th of August 2009.

But when the date comparison and 'greater than' is applied, it reads the
date in the SQL table as 2009084 12:00:00 AM.

Obviously, there is a checkbox in the MS SQL server 2005 to do something in
a particular way that I'm not aware of (I'm not aware of a bunch of things,
quite frankly. I just plod along.)

Not to worry, and I will be looking in to Erlund's suggestions.

Thanks guys!

Mark