Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

Date query in ASP


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
1 Solution
these should get you started


Saqib KhanSenior DeveloperCommented:
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
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now