x

Update datetime by one year on sql database

Hi, I have a table in my database called "listingsdbelements" and each property has an available date which is in 'datetime' ie '1200268800'

I need to increase every one by exactly 1 year.

Within the table there is a column called "listingsdbelements_field_name" so when the value of "listingsdbelements_field_name" is "available" there is a value in the next column "listingsdbelements_field_value" with the date time value ie "1200268800".

Can this be done?

more ▼

asked Dec 04, 2009 at 09:39 AM in Default

Gav 1 gravatar image

Gav 1
11 1 1 1

What will be the desired date if you add one year to, say, 29-Feb-2008 - i.e. a leap year? Note that whatever you do (round down to 28-Feb or round up to 01-Mar) if you increment it by one year each year it won't get back to being 29-Feb in four years time - to achieve that you will need to store a Base Date and a Display Date which is N years added on (calculated from the Base Date each time)
Dec 04, 2009 at 02:22 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

Assuming this is SQL Server, and that I have understood the table / column name requirements correctly!!, you should be able to do this with

            
UPDATE U            
SET listingsdbelements_field_value = listingsdbelements_field_value + 3110400            
FROM listingsdbelements AS U            
WHERE listingsdbelements_field_name = 'available'            

Take a backup first!

more ▼

answered Dec 04, 2009 at 02:56 PM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left

What is the date value of '1200268800'?

more ▼

answered Dec 04, 2009 at 09:57 AM

Madhivanan gravatar image

Madhivanan
1.1k 1 3 6

(comments are locked)
10|1200 characters needed characters left

1200268800 is 14/01/2008

I am unsure if the date format matters but I use DD/MM/YYYY

more ▼

answered Dec 04, 2009 at 11:47 AM

Gav 1 gravatar image

Gav 1
11 1 1 1

Looks to be the number of seconds since 01-Jan-1970 - probably an Oracle / Unix datetime?
Dec 04, 2009 at 02:20 PM Kristen ♦
(comments are locked)
10|1200 characters needed characters left

Hi, I know its unix time, I would like to add 1 year ie (60x60x24x36) in seconds to each value. So is there an sql statement I can add which does this for the values and database model I mention above?

ie.

To get 1 year in front of 1200268800 I would need to add 3110400 which then translates 14/01/2008 into 14/01/2009

Im not too bothered about leap years I just need to add in BULK to the 800 properties which have a unix datetime.

more ▼

answered Dec 04, 2009 at 02:51 PM

Gav 1 gravatar image

Gav 1
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

Hi Kristin, Thanks for posting this. I ran the SQL statement and received an error back

"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM default_en_listingsdbelements AS U WHERE default_en_listingsdbelements_fie' at line 3 "

I am unsure of the exact SQL version but I know it has recently been upgraded on my server.

I added default_en_ to each statment prefix too..

more ▼

answered Dec 07, 2009 at 05:15 AM

Gav 1 gravatar image

Gav 1
11 1 1 1

Looks like you are using MySQL. This site is specifically for SQL Server.
Refer to http://ask.sqlteam.com/faq

The query Kristen posted is for Microsoft SQL Server. For your MySQL related question, try dbforums.com or mysql.com
Dec 07, 2009 at 05:22 AM Squirrel 1
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x95
x27

asked: Dec 04, 2009 at 09:39 AM

Seen: 3057 times

Last Updated: Aug 29, 2012 at 12:32 PM