#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    10
    Rep Power
    0

    Help regarding date functions


    Hi!!

    I have some problem regarding the date functions. The problem I have is, I havea column in my SQL server database table by name request_generated_date of datetime datattype and the default value set to it the server date by using the function getdate().
    I am querying this table wherein I want to check that when a user submits a form then it should check the following: the submit date (column:request_generated_date in the tabel)should be less than the server date + 24 hours (or one day). When I am writing the query as

    where request_generated_date ='"&dateadd('d',1,date())&"'"

    but the result set is empty.

    Can somebody please help in this matter. Can I use the date() function in ASP or not. or should I try to remove the time factor from the server date and then query the column..I am confused.

    Please help.

    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    138
    Rep Power
    12
    This is the output I get from
    Code:
    Select getdate();
    2003-05-19 17:41:21.827

    You probably have to trim off the hr:mins:sec.mmm
    unless you get an exact match the query will return nothing because the date actually retuns date and time.

    Try using < or > or between, convert, datepart, and datediff.
    With a combination of the above you should be able to get an answer
    --

    ngibsonau
  4. #3
  5. No Profile Picture
    I am here to share knowledge
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    India
    Posts
    83
    Rep Power
    12
    if you are using my sql as backend i would suggest you to convert the date in mysql's format before saving it
    here is the example

    sdate = Year(Date()) & "-" & Month(Date()) & "-" &Day(Date())
    then save the sdate

    bcoz mysql stores the date in yyyy-mm-dd format and asp send mm/dd/yyyy which is not compatible with mysql

    rahul
    Rahul

    Small things lead to perfection and perfection is not a small thing.
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    10
    Rep Power
    0
    can you please send me the code as to how it could be done. I am lost totally...
  8. #5
  9. No Profile Picture
    I am here to share knowledge
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    India
    Posts
    83
    Rep Power
    12
    first try this
    instead of using getdate() use curdate()
    hopefully it will resolve the issue if not mail me again i will try to sort it out
    Rahul
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    10
    Rep Power
    0
    Hi!!! Rahul,

    Thanks for the suggestion.

    But I am using MS SQL Server as my backend. The problem is when I am going to set the datatype for the column I can do it as Curdate(), but when I try to query, I have to use the Date() function which again returns the date and time. How can I remove the time function from the date().

    Thanks.
  12. #7
  13. No Profile Picture
    I am here to share knowledge
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2003
    Location
    India
    Posts
    83
    Rep Power
    12
    Hi lovelyeyes (at least put your name in the profile it will help someone to address you)

    In your first respone you wrote that you are using MySQL and in the previous one your are saying that you are using MS SQL Server. plz clear this thing bcoz every backend is having its own date format the resolution i have provided you was for MySQL Server.

    in asp date() returns only date, time() returns only time, and now() returns both

    respond me so that i can assist you
    Rahul

IMN logo majestic logo threadwatch logo seochat tools logo