changing only the year in date field

[Guest]

I want to change only the year in date field of one table. I have tried with
update query but i don't have right function or expression.

 

[Jamie Collins]

I want to change only the year in date field of one table. I have tried with
update query but i don't have right function or expression.

Jet has but one temporal data type named DATETIME. For various
reasons, Jet has no DATETIME constructor but does has DateSerial and
TimeSerial expressions which are used *together* e.g. to project to
the year 2055 (aircode):

SELECT DATESERIAL
(
2055,
DATEPART('M', test_date),
DATEPART('D', test_date)
)
+ TIMESERIAL
(
DATEPART('H', test_date),
DATEPART('N', test_date),
DATEPART('S', test_date)
) AS test_result
FROM TestTable;

Jamie.

--

 

[John Spencer]

UPDATE yourTable
SET YourDate = DateSerial(2008,Month(YourDate), Day(YourDate))
WHERE YourDate between #2007-01-01# and #2007-12-31#

You didn't post how you wanted the year changed or what you wanted the
change limited to. So the above is one idea.

Another way to make the same change
UPDATE yourTable
SET YourDate = DateAdd("yyyy",1,YourDate)
WHERE YourDate between #2007-01-01# and #2007-12-31#

The first version would change the year of every record in the database to
2008 if you dropped the criteria.
The second version would add one year to every record in the database.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[John Spencer]

I did forget to mention that using DateSerial will strip off any time you
have stored in the datefield. So if you have a time component in your date
field, you will need to use DateAdd or you will need to add the time back in
using TimeSerialor the TimeValue function.

DateSerial(..) + TimeValue(YourDate)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Example: I have this date 27/09/2017 in many racords. I want to leave 27/09
but I want to change the year in 2007. It is easier to change the year in
every record to 2007.

Thank you

UPDATE yourTable
SET YourDate = DateSerial(2008,Month(YourDate), Day(YourDate))
WHERE YourDate between #2007-01-01# and #2007-12-31#

You didn't post how you wanted th]e year changed or what you wanted the
change limited to. So the above is one idea.

Another way to make the same change
UPDATE yourTable
SET YourDate = DateAdd("yyyy",1,YourDate)
WHERE YourDate between #2007-01-01# and #2007-12-31#

The first version would change the year of every record in the database to
2008 if you dropped the criteria.
The second version would add one year to every record in the database.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to change only the year in date field of one table. I have tried
with
update query but i don't have right function or expression.

 

[John Spencer]

UPDATE YourTable
SET YourField = #2007-09-27#
WHERE YourField = #2017-09-27#

In the query grid
-- add your date field
-- set the criteria to = #2017-09-27#
-- Select Query: Update from the menu
-- Type #2007-09-27# in the UPDATE To cell
-- select Query: Run from the menu

BACKUP your data first, in case something goes wrong.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Example: I have this date 27/09/2017 in many racords. I want to leave 27/09
but I want to change the year in 2007. It is easier to change the year in
every record to 2007.

Thank you

UPDATE yourTable
SET YourDate = DateSerial(2008,Month(YourDate), Day(YourDate))
WHERE YourDate between #2007-01-01# and #2007-12-31#

You didn't post how you wanted th]e year changed or what you wanted the
change limited to. So the above is one idea.

Another way to make the same change
UPDATE yourTable
SET YourDate = DateAdd("yyyy",1,YourDate)
WHERE YourDate between #2007-01-01# and #2007-12-31#

The first version would change the year of every record in the database to
2008 if you dropped the criteria.
The second version would add one year to every record in the database.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I want to change only the year in date field of one table. I have tried
with
update query but i don't have right function or expression.