SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL/Date problem

    im trying to get some information out a database that by the date, the sql is below;

    Code:
    "SELECT * FROM tblsNews WHERE dateT < #" & now() & "# ORDER BY dateT DESC"
    what im trying to do is get the articles thats date has already been, and leaving the ones that are in the furture alone, but its pulling out all the articles

    the database entry looks like so

    Code:
    25/07/01 12:27:25
    thanks in advance (if this is really easy, im going to cry...)

    * forgot to say that its asp using an access 97 database

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    You may need to reformat your date coming out of the now() command. I believe the now date is usually formatted YYYYMMDD HHMMSS and we know your database date format is DD/MM/YY HH:MM:SS, so the two date formats are incompatible.

    I think there is a system function to reformat the date into another format, but I honestly don't know what it is. You could do try like this:

    Code:
    <%
    Dim NowFormat, DatabaseFormat()
    ' Get Now() value....
    NowFormat = Now()
    
    ' Convert YYYYMMDD HHMMSS to DD/MM/YY HH:MM:SS
    DatabaseFormat = cdate("" & Mid(NowFormat, 7,2) & "/" & Mid(NowFormat, 5,2) & "/" & Mid(NowFormat, 3,2) & " " & Mid(NowFormat, 9,2) & ":" & Mid(NowFormat, 11,2) & ":" & Mid(NowFormat, 13,2) & "")
    
    StrSQl = "SELECT * FROM tblsNews WHERE dateT < #" & DateFormat & "# ORDER BY dateT DESC"
    set rs = my_Conn.Execute (StrSql)
    %>
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  3. #3
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    the now() is coming out as dd/mm/yy hh/mm/ss

  4. #4
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Kentucky
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Heya dude .. The #'s you are putting around the date are only required if you are dealing with an Access Database, if it is SQL2000, mySQL etc then you should use the ' ' command ... if I were gonna do what ur doing i would do this:

    <%
    rightnow=date()

    sql="SELECT * FROM database WHERE dateT = '" & rightnow & "'"

    %>

    etc ...

    Makes it a little easier ..

  5. #5
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Kentucky
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It'd be really cool if I read the previous posts throroughly

  6. #6
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lol its ok, i jus unmade (is that even a word?) the access row a time/date and made it text, and when i need to do actual date stuff with it, i jus cDate it

    thanks all


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •