Date Variable In SQL Statement. Going Nuts!

Printable View

02-19-2002, 12:45 PM
4AMFriday

Date Variable In SQL Statement. Going Nuts!

Hey Guys. I really hope someone can help me here as I am ready to pull my hair out. This is driving me totally nuts.

Why can't I get any records returned when I compare a date field in my database to a date formated variable in my asp script. For instance, assume the field "AppointDate" = 2/19/2002.

The following is just for the sake of argument.
CalDate = formatDateTime(Date(), 2)

The line above should return a variable that equals "02/19/2002"
If I just used the "Date()" function by itself, the function would return the same exact date "02/19/2002."

The following SQL statement returns nothing.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = " & CalDate

The following SQL statement returns the correct count.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = Date()"

In my script, the variable "CalDate" holds different dates that change within a loop command, which is why I can't just use the "Date()" command with my SQL statement. Maybe I am just stupid or something, but can you use a variable formated as a date with in an SQL statement, or just the "Date()" or "Now()" functions? What gives?
02-19-2002, 01:15 PM
John

Date Variable In SQL Statement. Going Nuts! (reply)

Hi,

Check out the SQL forums out here for a better chance at some help.

Thanks,
John

------------
4AMFriday at 2/19/2002 12:45:25 PM

Hey Guys. I really hope someone can help me here as I am ready to pull my hair out. This is driving me totally nuts.

Why can't I get any records returned when I compare a date field in my database to a date formated variable in my asp script. For instance, assume the field "AppointDate" = 2/19/2002.

The following is just for the sake of argument.
CalDate = formatDateTime(Date(), 2)

The line above should return a variable that equals "02/19/2002"
If I just used the "Date()" function by itself, the function would return the same exact date "02/19/2002."

The following SQL statement returns nothing.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = " & CalDate

The following SQL statement returns the correct count.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = Date()"

In my script, the variable "CalDate" holds different dates that change within a loop command, which is why I can't just use the "Date()" command with my SQL statement. Maybe I am just stupid or something, but can you use a variable formated as a date with in an SQL statement, or just the "Date()" or "Now()" functions? What gives?
02-20-2002, 07:19 AM
Frank Kwong

Date Variable In SQL Statement. Going Nuts! (reply)

one thing to check is to see whether both dates have HH:MM:SS attached. If one has it then the chance of a match is NONE.

------------
John at 2/19/2002 1:15:39 PM

Hi,

Check out the SQL forums out here for a better chance at some help.

Thanks,
John

------------
4AMFriday at 2/19/2002 12:45:25 PM

Hey Guys. I really hope someone can help me here as I am ready to pull my hair out. This is driving me totally nuts.

Why can't I get any records returned when I compare a date field in my database to a date formated variable in my asp script. For instance, assume the field "AppointDate" = 2/19/2002.

The following is just for the sake of argument.
CalDate = formatDateTime(Date(), 2)

The line above should return a variable that equals "02/19/2002"
If I just used the "Date()" function by itself, the function would return the same exact date "02/19/2002."

The following SQL statement returns nothing.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = " & CalDate

The following SQL statement returns the correct count.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = Date()"

In my script, the variable "CalDate" holds different dates that change within a loop command, which is why I can't just use the "Date()" command with my SQL statement. Maybe I am just stupid or something, but can you use a variable formated as a date with in an SQL statement, or just the "Date()" or "Now()" functions? What gives?
02-27-2002, 02:48 PM
Brian

Date Variable In SQL Statement. Going Nuts! (reply)

I think you have to convert both the database value and the query parameter to date values and then do the comparison. The Date values will actually differ in incriments of 1 = 1 day...

------------
Frank Kwong at 2/20/2002 7:19:23 AM

one thing to check is to see whether both dates have HH:MM:SS attached. If one has it then the chance of a match is NONE.

------------
John at 2/19/2002 1:15:39 PM

Hi,

Check out the SQL forums out here for a better chance at some help.

Thanks,
John

------------
4AMFriday at 2/19/2002 12:45:25 PM

Hey Guys. I really hope someone can help me here as I am ready to pull my hair out. This is driving me totally nuts.

Why can't I get any records returned when I compare a date field in my database to a date formated variable in my asp script. For instance, assume the field "AppointDate" = 2/19/2002.

The following is just for the sake of argument.
CalDate = formatDateTime(Date(), 2)

The line above should return a variable that equals "02/19/2002"
If I just used the "Date()" function by itself, the function would return the same exact date "02/19/2002."

The following SQL statement returns nothing.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = " & CalDate

The following SQL statement returns the correct count.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = Date()"

In my script, the variable "CalDate" holds different dates that change within a loop command, which is why I can't just use the "Date()" command with my SQL statement. Maybe I am just stupid or something, but can you use a variable formated as a date with in an SQL statement, or just the "Date()" or "Now()" functions? What gives?
02-27-2002, 11:30 PM
Indra

Date Variable In SQL Statement. Going Nuts! (reply)

Try this:
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = '" & CalDate & "'"

Good luck!

------------
4AMFriday at 2/19/2002 12:45:25 PM

Hey Guys. I really hope someone can help me here as I am ready to pull my hair out. This is driving me totally nuts.

Why can't I get any records returned when I compare a date field in my database to a date formated variable in my asp script. For instance, assume the field "AppointDate" = 2/19/2002.

The following is just for the sake of argument.
CalDate = formatDateTime(Date(), 2)

The line above should return a variable that equals "02/19/2002"
If I just used the "Date()" function by itself, the function would return the same exact date "02/19/2002."

The following SQL statement returns nothing.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = " & CalDate

The following SQL statement returns the correct count.
SQL = "SELECT Count(*) As AppDate From Guests WHERE AppointDate = Date()"

In my script, the variable "CalDate" holds different dates that change within a loop command, which is why I can't just use the "Date()" command with my SQL statement. Maybe I am just stupid or something, but can you use a variable formated as a date with in an SQL statement, or just the "Date()" or "Now()" functions? What gives?