Solved

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

Posted on 2012-03-22
6
258 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

759 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

17 Experts available now in Live!

Get 1:1 Help Now