Query to add 1 year to existing date column in access db

Question

0.00/5 (No votes)

See more:

Hi Everyone,

I have 2 columns in my db named CreatedOn and TrainingExp

I want to write a query in such a way that my TrainingExp column has to be updated by adding 1 year to the CreatedOn column

How can i do that??

Any help is kindly appreciated

Posted 19-Jan-12 20:39pm

Saumyavemula

Add a Solution

3 solutions

Solution 3

Hi Saumyavemula,
Here is the solution for you.

UPDATE [Table name] set [column name] = DateAdd("yyyy, 1, Date())

You can add where clause to edit specific row.

Thanks,
Javed

Posted 19-Jan-12 22:58pm

Jαved

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-01-19T20:48:00

Solution 1

Try:

SQL

INSERT INTO myTable (CreatedOn, TrainingExp) VALUES (GETDATE(), DATEADD(year, 1, GETDATE()))

Posted 19-Jan-12 20:48pm

OriginalGriff

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Jan-12 2:56am

Perfect.....

Saumyavemula 20-Jan-12 2:57am

Values are already inserted into table, i just need to update the column

OriginalGriff 20-Jan-12 3:10am

Then replace the INSERT statement with an UPDATE, and replace GETDATE with the appropriate existing column value...

Saumyavemula 20-Jan-12 4:37am

wrote this query but asking for parameter
Update Master set TrainingExp = dateadd("yyyy",1,[CreatedOn])

please help.....

OriginalGriff 20-Jan-12 4:42am

I just tried it - it works fine for me. Check your Table/Field names and datatypes.

Saumyavemula 20-Jan-12 4:47am

i am using access database, both columns datatype is datetime

Saumyavemula 20-Jan-12 4:48am

CreatedOn column is text and Trainingexp is datetime

OriginalGriff 20-Jan-12 5:51am

"CreatedOn column is text"
There is your problem!
If you can (and you should) change it to DateTime - it's more compact, and it can be worked with. If you use SQL Server Management Studio, then it should convert the NVARCHR field to dates if they are all valid.
If you can't, then you may have a problem parsing it to a datetime to work with it, depending on the locale of the user that entered it, but you can try CAST(CreatedOn AS DateTime) and hope like heck! :laugh:

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Accepted Answer · 2012-01-19T21:18:00

Solution 2

This is the Query you wanted -

SQL

UPDATE myTable set TrainingExp = DATEADD(year, 1, CreatedOn)

Posted 19-Jan-12 21:18pm

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

Saumyavemula 20-Jan-12 4:35am

did the same but is asking for parameter to pass, whereas i need to change the whole column

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Jan-12 4:44am

It is working fine in my case.
Which database you are using??; SQL Server or MYSQL or any other please mention it...

Saumyavemula 20-Jan-12 4:47am

i am using access database

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Jan-12 5:15am

I think the problem is "CreatedOn column is text".
So you need to convert it to datetime, then you can manipulate it.
I am searching for the same. If I find it, will let you know..

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Jan-12 5:21am

Go for the discussion -

http://p2p.wrox.com/access-vba/11341-convert-text-date-time-access.html

May you find anything useful.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 20-Jan-12 5:26am

Yes finally I found the conversion function in the link -

http://office.microsoft.com/en-us/access-help/type-conversion-functions-HA001229018.aspx

It is CDate().
Just refer the link.

I don't know about access queries, but I think the query now will look like -

Update Master set TrainingExp = dateadd("yyyy", 1, CDate([CreatedOn]))

Try it out....