Changing date only in timestamp

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Nick26
Posts: 27
Joined: Fri Aug 07, 2020 6:59 am
Location: Melbourne, Australia

Changing date only in timestamp

Post by Nick26 »

I have multiple calendar items that I want a process to copy from the current to new date, but keep the time.

So if I have

9/5/21 09:00
9/5/21 12:00

I want to change it to
12/5/21 09:00
12/5/21 12:00

Is there a way to just change the date in the timestamp across multiple calendar events, but keep the time? Obviously I can change each one individually, but it takes a long time
PointsWell
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Changing date only in timestamp

Post by PointsWell »

Nick26 wrote: Wed Sep 22, 2021 2:04 am I have multiple calendar items that I want a process to copy from the current to new date, but keep the time.

So if I have

9/5/21 09:00
9/5/21 12:00

I want to change it to
12/5/21 09:00
12/5/21 12:00

Is there a way to just change the date in the timestamp across multiple calendar events, but keep the time? Obviously I can change each one individually, but it takes a long time
P410 of the User Guide lists all the Functions.

FIND all of your calendar items using DATE_PART (p413)

Then if you are adding a specific number of days to a date use DATE_ADD (BO.AttributeName, n) where n= number of days positive or negative

Alternatively look at AS_TIMESTAMP to convert a timestamp expressed as a string into a Timestamp value. p421 if you know what the date and time are to be.
rocketman
Posts: 1239
Joined: Fri Jan 02, 2009 11:22 pm
Location: Preston UK
Contact:

Re: Changing date only in timestamp

Post by rocketman »

This assumes you - or a process - are providing a date that you want things to be moved to (Target_Date)

Try Start_Time = TIME_ADD(Start_Time,DAY_DIFFERENCE(DATE_PART(Start_Time),Target_Date)*24))

Read up on Day_Difference, Time_Add and DATE_PART in the user ref
Rocketman

V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
BobK
Posts: 544
Joined: Thu Jan 31, 2008 2:14 pm
Location: Cincinnati, Ohio, USA

Re: Changing date only in timestamp

Post by BobK »

To expand on PointsWell and rocketman answers.

The following is from the AwareIM User Guide and describes the Parameters for the TIMESTAMP function. The bold part is a sweet little gem that is easy to overlook that I just found myself:
An integer constant indicating day (1-31), an integer constant
indicating month (1-12), an integer constant indicating year, an
integer constant indicating hours (0-23), an integer constant
indicating minutes (0-59).
Alternatively a timestamp can be created from 2 parameters – one
date and one timestamp. Only time values from the second
parameter are used to create a timestamp.
Bob
rocketman
Posts: 1239
Joined: Fri Jan 02, 2009 11:22 pm
Location: Preston UK
Contact:

Re: Changing date only in timestamp

Post by rocketman »

Wow Bob, many thanks. I didn’t know that last bit
Rocketman

V8.7 Developer Edition. Server 2016 Standard edition. MySql 5.5
Nick26
Posts: 27
Joined: Fri Aug 07, 2020 6:59 am
Location: Melbourne, Australia

Re: Changing date only in timestamp

Post by Nick26 »

Thanks all- got it work!
Post Reply