• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 718
  • Last Modified:

Add One year to SQL Expression

Hello all,

I'm trying to get a date exactly one year from a person's hire date.  I can add 365, but that only works for years that are not leap years.  (One day isn't that big of a deal, but it should be correct.)  Is there another way to add one year to a date field as an expression?  What I want to do is run an update query to populate the new field probation_end with the following date:

SELECT hire_date + 365 AS Probation End
From Tbl_main

So it would be:

update tbl_main
set probation_end = hire_date + 365

Thanks in advance,

G

0
graysoc
Asked:
graysoc
  • 5
  • 4
  • 3
  • +1
1 Solution
 
TimCotteeCommented:
Hi graysoc,

Update tbl_Main Set Probation_end = DateAdd("year",1,hire_date)

Shoud do it.

Tim Cottee
0
 
hongjunCommented:
Try this

update tbl_main
set probation_end = DateAdd('yyyy', 1, hire_date)
0
 
Rey Obrero (Capricorn1)Commented:


try this

Update tbl_Main Set Probation_end = DateAdd('yyyy',1,hire_date)

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Rey Obrero (Capricorn1)Commented:
or

Update tbl_Main Set Probation_end = DateAdd('yyyy',1,[hire_date])
0
 
graysocAuthor Commented:
Thanks for the very prompt responses gentlemen, but I had to go with the first one.  :)

G
0
 
Rey Obrero (Capricorn1)Commented:
graysoc
i don't know how you were able to get that syntax work.   you should use 'yyyy'  instead of 'year'
0
 
graysocAuthor Commented:
Oh, I didn't even look at the syntax, I just looked for the function.  I just couldn't think of the function off the top of my head, but I knew the syntax for that one already.  I didn't copy/paste because the table names and field names were greatly simplified for the post.  Sorry!!!

G
0
 
hongjunCommented:
Then you should request for a reopen of this question and accept the first correct answer.
0
 
graysocAuthor Commented:
In my view, that was the correct answer.  I wanted the dateadd function, and the first answer provided it.  The syntax was irrelevant.

G
0
 
hongjunCommented:
Alright :)
I respect your decision.


hongjun
0
 
graysocAuthor Commented:
UPDATE tbl_other INNER JOIN [Pilot Administration Table] ON tbl_other.co_id=[Pilot Administration Table].[1-CO ID] SET tbl_other.probation_end = DateAdd("yyyy",1,[pilot administration table].hiredate);


No hard feelings guys.  This is the final SQL that I got.  I just said, "Oh yeah, DUH the DateAdd function..." and kept on trucking.

Thanks!

G

PS> Those table names are NOT mine, inhereted bad structure...
0
 
hongjunCommented:
No problem.
Glad that your problem is solved.
That's the whole idea behind this sharing of knowledge.


hongjun
0
 
graysocAuthor Commented:
Hey, I'm having another problem if you want another crack at it...  I'm opening a question in two shakes.  Not about SQL though, but rather an Access form problem...

G
0

Featured Post

Independent Software Vendors: 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!

  • 5
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now