Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Quote Originally Posted by Old Pedant View Post
    Dates and times in Access *MUST* be surrounded by #...# instead of '...'.

    So:
    ... WHERE upload_time = #5:10 AM#
    if you omit the AM/PM, then 24-hour clock is assumed. So you could do
    ... WHERE upload_time = #7:32 PM#
    *OR*
    ... WHERE upload_time = #19:32#

    One thing to DEFINITELY watch out for: DATES in Access *MUST* be in either USA format (#mm/dd/yyyy#) or ISO format (#yyyy/mm/dd#). They can *NOT* be in UK format (#dd/mm/yyyy#) unfortunately.

    I strongly recommend using ISO format. So you can use a VBS function to help do that:
    Code:
    Function ISODate( dt )
        Dim iso 
        iso = year(dt) & "/" & month(dt) & "/" & day(dt)
        If TimeValue(dt) <> 0 Then iso = iso & " " & TimeValue(dt)
        ISODate = "#" & iso & "#"
    End Function
    
    ' and then use it in a query thus:
    sql = "SELECT * FROM tbl WHERE dtfield = " & ISODate(someVBScriptDateTime)
    Oh...and you can use DATEVALUE() and TIMEVALUE() In Access queries, as well.

    For example, if you wanted to find all records where upload_time is between 5PM and 6PM, *regardless* of what DATE they were uploaded:
    Code:
    sql = "SELECT * FROM images WHERE TIMEVALUE(upload_time) BETWEEN #5:00PM# AND #6:00PM#"
    Oh...and pleas don't tell me you have both upload_date and upload_time. No reason at all to do that. Just use upload_time (or upload_date_time, perhaps) to hold a DATETIME value which is both.
    How would I fetch all results for a certain date like you do with the time?

  2. #17
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Also, the following function won't work unless I use datetime. I only need the date in some cases.

    String:
    Code:
    24/06/2013
    Code:
    Function ISODate( dt )
    	dim iso
    	iso = year(dt) & "/" & month(dt) & "/" & day(dt)
    	If TimeValue(dt) <> 0 Then
    		iso = iso & " " & TimeValue(dt)
    		ISODate = "#" & iso & "#"
    	End If
    end function
    the ISODate function should simply turn that date into "2013/6/24" and ignore the time...

  3. #18
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Don't *EVER* treat a date/time as a *STRING*. Period.

    Only exception: When you are accepting user input from a <form>. And even then you should convert it to a DATE value as soon as you can.

    First of all, since you are working in the UK, make sure you have done
    Code:
    Session.LCID = 2057 ' specifies to use UK dates, money, etc.
    And then you should enter *CONSTANT* dates as
    Code:
    someDate = #24/06/2013# ' with #...# around them!
    and if you are processing <form> input, you should do something like:
    Code:
    someDate = CDate( Request("someDate") ) 'immediately convert to a date!
    And *THEN* you can use those date values with the ISODate function I gave you.

    ************

    To fetch all results for a certain date, when then the field in the database contains dates and times, you use DateValue( ) with Access, exactly the same as you do in VBScript.
    Code:
    SQL = "SELECT * FROM table WHERE DATEVALUE(someField) = " & ISODate(someDate)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  4. Users who have thanked Old Pedant for this post:

    martynball (06-24-2013)

  5. #19
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Note that DateValue() only exists in Access (and VBScript).

    You have to use different functions with MySQL and SQL Server, for example.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  6. Users who have thanked Old Pedant for this post:

    martynball (06-24-2013)

  7. #20
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Still won't work, the ISODate function is not returning anything.

    sdate cookie: 24/06/2013

    Code:
    getdate = Request.Cookies("sdate")
    getdate = CDate(getdate)
    				
    response.write("Here: " & ISODate("#"&getdate&"#"))

  8. #21
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    *SIGH*

    One more time: ISODate() *DOES NOT WANT AND WILL NOT ACCEPT* a string as its argument.

    When you added those "#" characters around getDate in "#"&getdate&"#" you converted the READY TO USE value in getdate to a string.

    READ WHAT I WROTE ONE MORE TIME:
    And then you should enter *CONSTANT* dates as
    Code:
    someDate = #24/06/2013# ' with #...# around them!
    and if you are processing <form> input, you should do something like:
    Code:
    someDate = CDate( Request("someDate") ) 'immediately convert to a date!
    The #...# *ONLY ONLY ONLY* is used around CONSTANT dates. *NOT* around dates that you have created using the CDATE() or DataSerial() functions (or around dates you have retrieved from database date fields).

    Don't you have *ANY* ASP reference book or web site that you are learning from? Anything at all?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  9. Users who have thanked Old Pedant for this post:

    martynball (06-24-2013)

  10. #22
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    Ooooooh I get it now thanks. And Im just learning as I go along, tbf I probably should read a ASP Book or something

  11. #23
    Regular Coder
    Join Date
    Nov 2007
    Posts
    682
    Thanks
    319
    Thanked 1 Time in 1 Post
    The one thing i'm still confused about is why this isn't working:

    Code:
    getdate = #24/06/2013#
    				
    response.write("Here: " & ISODate(getdate))
    Only this will work, basically with a time as well. What if I don't WANT a time...
    Code:
    getdate = #24/06/2013 10:47#
    				
    response.write("Here: " & ISODate(getdate))

  12. #24
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Okay, first of all, you completely whacked the code I gave you for ISODate()

    Go back to my code and use it *EXACTLY AS CODED*. Your code WILL NOT WORK.

    I will repeat it here. DO NOT CHANGE IT.
    Code:
    Function ISODate( dt )
        Dim iso 
        iso = year(dt) & "/" & month(dt) & "/" & day(dt)
        If TimeValue(dt) <> 0 Then iso = iso & " " & TimeValue(dt)
        ISODate = "#" & iso & "#"
    End Function
    Then, to answer you other question:
    Code:
    getdate = #24/06/2013 10:47#
    response.write "Here: " & ISODate( DateValue(getdate) )
    DateValue() extracts just the date part from a date+time. TimeValue() extracts just the time part.

    IMPORTANT: Do *NOT* use parentheses around the argument to Response.Write. Response.Write is a SUB. Yes, it works, for reasons I'm not going to go into (too long-winded), but all it does is slow down your code slightly.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  13. Users who have thanked Old Pedant for this post:

    martynball (06-25-2013)

  14. #25
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,554
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    If you really can't stand to see the "THEN" statement of an IF...THEN on the same line as the IF, then you *could* change that code to
    Code:
    Function ISODate( dt )
    	dim iso
    	iso = year(dt) & "/" & month(dt) & "/" & day(dt)
    	If TimeValue(dt) <> 0 Then
    		iso = iso & " " & TimeValue(dt)
    	End If
            ISODate = "#" & iso & "#"
    End Function
    NOTE CAREFULLY where the End If is. It is *NOT* where you put it.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  15. Users who have thanked Old Pedant for this post:

    martynball (06-25-2013)


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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