-
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?
-
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?
-
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?
-
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?
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|