Date query in ASP

Posted on 2004-11-03
Last Modified: 2010-04-25

I have an access database which stores date in dd/mm/yyyy format.  I am having problems retrieving data when i type this in my recordset.  I have read that this is a problem and that i have to use yyyy/mm/dd to retrieve.  How can i get the date the user types in (dd/mm/yyyy) to convert to yyyy/mm/dd before it is sent to the query.  

many thanks
Question by:jkharodia
    LVL 10

    Accepted Solution

    these should get you started

    LVL 23

    Expert Comment

    Two Options, first Change the DataType of Date/Time Column to Text , then you will be able to see whatever user typed (Not Recomended)

    2nd Would be, play around with formatDateTime Function to get what you need.

    formatDateTime(Rs("TimeColumn"), 2)

    OR1, 3, 4 as parameter....

    Good Luck
    LVL 19

    Expert Comment

    Access databases store dates as mm/dd/yyyy by default. You should use that format for queries, though just converting to a date may work.

    Anything that's submitted through a form is a string. You'll have to convert it to a date. I would parse out the pieces and recombine them, then convert to date. You can try converting to a date directly and see if it's recognized. If you specify what format it is, it should work.

    Not sure of the syntax, but it should be possible.

    I wouldn't change the format in the database -- if you do, you can't sort by date, add/subtract dates, etc., or use ANY date functions directly.  

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
    This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now