Results 1 to 5 of 5

Thread: Date Variable In SQL Statement. Going Nuts!

  1. #1
    4AMFriday Guest

    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?

  2. #2
    John Guest

    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?

  3. #3
    Frank Kwong Guest

    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?

  4. #4
    Brian Guest

    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?

  5. #5
    Indra Guest

    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
  •