Solved

How do you convert 31/12/2011 to 12/31/2011

Posted on 2012-03-22
6
265 Views
Last Modified: 2012-03-26
I have a SQL statement that I run in MS Access 2010 with a WHERE clause on the date.  The date is passed into the module in the format of 31/12/2011, however I need it to be in the format of 12/31/2011.

How do I cahnge the value to 12/31/2011 prior to running the SQL ?
0
Comment
Question by:upobDaPlaya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 167 total points
ID: 37754681
There is not a lot of supporting info here...

Try this:
cdate(format([YourDate],"mm/dd/yyyy"))
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37754695
maybe this:

where mycol = mid(param,4,5) & "/" & left(param,2) & "/" & right(param, 4)
0
 
LVL 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 166 total points
ID: 37754711
i dont think cdate solves the issue
if you pass "05/07/2011" cdate will not fix it... it will give you wrong date, and format does not help after wrong conversion... so, you should use mid, right, left combination...
is the param always in this format dd/mm/yyyy with zero padded, 10 char all the time?
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37754748
HainKurt,

Yeah, that was just a shot in the dark...
...wanted to see if it would work without breaking up the date...


Jeff
0
 

Author Comment

by:upobDaPlaya
ID: 37755562
One important variable I left out is that my PC is set to European Format for the regional settings and for the purpose of other work I need to keep it that way..is there a way within VBA to programatically change my settings to US prior to importing my data that contains the dates into my Access table.  

The flow is the date values are imported into MS Access via a MS Acess module from MS Excel.  After the import is completed I run my SQL which has a WHERE clause on the date value.

If not I will try the mid,left,right combo.
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 167 total points
ID: 37756196
>  The date is passed into the module in the format of 31/12/2011

Don't know what that means; a date value doesn't carry a specific format, that's something you apply to have it displayed.
If you have the date as a string "31/12/2011", then all you need is to convert this to a date value and then build a formatted string date expression for SQL where the ISO format is preferred:

strDate = "31/12/2011"
strDateSQL = Format(DateValue(strDate), "yyyy/mm/dd")

strSQL = ' Your select statement
strSQL = strSQL & " WHERE [YourDateField] = #" & strDateSQL & "#"

/gustav
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

740 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