Solved

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

Posted on 2012-03-22
6
260 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
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 51

Expert Comment

by:HainKurt
ID: 37754695
maybe this:

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

Assisted Solution

by:HainKurt
HainKurt 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 49

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

821 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