SQL Date comparison problem...

I'm grabbing today's date with some ASP.net code:

Dim Today As Date
Today = Microsoft.VisualBasic.Today()

I'm then trying to run a SQL query with the following WHERE statement:

WHERE postdate <= " & Today & "

But it's pulling up records that have a postdate in the future.

A sample:

Today (when written to screen) = 3/23/2005 12:00:00 AM
In the SQL table postdate field = 3/28/2005

Anyone see something wrong with what I have that would be causing this? Is
it because in my code I'm grabbing the time, while SQL is only storing the
date?

-Darrel

re: SQL Date comparison problem...

You need to put single quotes around the date.
Otherwise it sees your numbers and slashes as some kind of mathematical
equation.
WHERE postdate <= '" & Today & "'"

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net



"darrel" <notreal@hotmail.com> wrote in message
news:%23v$Mda$LFHA.576@TK2MSFTNGP15.phx.gbl...[color=blue]
> I'm grabbing today's date with some ASP.net code:
>
> Dim Today As Date
> Today = Microsoft.VisualBasic.Today()
>
> I'm then trying to run a SQL query with the following WHERE statement:
>
> WHERE postdate <= " & Today & "
>
> But it's pulling up records that have a postdate in the future.
>
> A sample:
>
> Today (when written to screen) = 3/23/2005 12:00:00 AM
> In the SQL table postdate field = 3/28/2005
>
> Anyone see something wrong with what I have that would be causing this? Is
> it because in my code I'm grabbing the time, while SQL is only storing the
> date?
>
> -Darrel
>
>
>[/color]

re: SQL Date comparison problem...

[color=blue]
> You need to put single quotes around the date.
> Otherwise it sees your numbers and slashes as some kind of mathematical
> equation.
> WHERE postdate <= '" & Today & "'"[/color]

I had tried that as well...same result.

-Darrel

re: SQL Date comparison problem...

It must be your defaul formating on the box running the aspnet code.
Instead of sending it the web server date, send it the db server date
If this is a SQL Server box the SQL syntax is:

"Select * from table Where postdate <=GetDate()"

No single quotes around this function.

Joseph

darrel wrote:[color=blue][color=green]
> > You need to put single quotes around the date.
> > Otherwise it sees your numbers and slashes as some kind of[/color][/color]
mathematical[color=blue][color=green]
> > equation.
> > WHERE postdate <= '" & Today & "'"[/color]
>
> I had tried that as well...same result.
>
> -Darrel[/color]

re: SQL Date comparison problem...

> It must be your defaul formating on the box running the aspnet code.[color=blue]
> Instead of sending it the web server date, send it the db server date
> If this is a SQL Server box the SQL syntax is:
>
> "Select * from table Where postdate <=GetDate()"[/color]

That makes a lot more sense. Still, same problem though.

I know have this:

WHERE postdate <= GetDate() AND pulldate > GetDate()

But it's still grabbing records with a postdate field of next monday for
some reason.

-Darrel

re: SQL Date comparison problem...

What is the db engine and datatypes of post and pulldate? varchar or
datetime?

re: SQL Date comparison problem...

> What is the db engine

MS SQL
[color=blue]
> and datatypes of post and pulldate? varchar or
> datetime?[/color]

datetime

-Darrel