Solved

Update "yyyy" to "2013"

Posted on 2013-01-10
6
245 Views
Last Modified: 2013-02-08
I have a Microsoft Access query where I am trying to update the "yyyy" "2012" to "2013".  Here is the Select query:
SELECT Format([MyDate],"mmyyyy") AS Expr1
FROM Company_Data
WHERE (((Format([MyDate],"mmyyyy"))="012012"));
0
Comment
Question by:donnie91910
6 Comments
 
LVL 29

Assisted Solution

by:IrogSinta
IrogSinta earned 100 total points
ID: 38765952
I'm not sure I understand the question.  Wouldn't  you just change it to:
SELECT Format([MyDate],"mmyyyy") AS Expr1
FROM Company_Data
WHERE (((Format([MyDate],"mmyyyy"))="012013"));
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38765961
Or did you want a query to update the dates in your table that are in 2012 to 2013?
0
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 100 total points
ID: 38765968
Try this

Update  Emp_Detail

set Emp_Detail.[JoiningDate] = CDate( Format(Emp_Detail.[JoiningDate], "MM/DD/") & "2013" )

where year(Emp_Detail.[JoiningDate]) = 2005



In your case for year
Update  Company_Data

set [MyDate] = CDate( Format([MyDate], "MM/DD/") & "2013" )

where year([MyDate]) = 2012
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 38766292
if the field is date, you just want to add 1 year:

dateadd("year", 1, your_date_field)

and eventually format from there.
if it's not datetime, it would be about time to think about getting that changed ..
0
 
LVL 61

Accepted Solution

by:
mbizup earned 100 total points
ID: 38766448
If your field is stored as TEXT (not optimal), you can use the following:

Select Query:
SELECT Replace([MyDate], "2012", "2013")
FROM Company_Data
WHERE Right([MyDate],4) = "2012"

Open in new window


Update query:
UPDATE Company_Data
SET [MyDate] = Replace([MyDate], "2012", "2013")
WHERE Right([MyDate],4) = "2012"

Open in new window

0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 100 total points
ID: 38767331
angelIII's solution should do what you want, although I think the syntax for year interval is "yyyy" -
dateadd("yyyy", 1, your_date_field)
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 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