Difference in Date/Time

[Vadhimoo]

Dear All,

Good Evening, I have a existing records this:
Date Time
10/05/2008 10:30:28
10/05/2008 00:12:55
10/05/2008 00:00:10
10/05/2008 09:30:02
............... .............

I need to subtract (-22 seconds) from all the above records.
The output is should be like this:

Date Time New_Date New_time
10/05/2008 10:30:28 10/05/2008 10:30:06
10/05/2008 00:12:55 10/05/2008 00:12:33
10/05/2008 00:00:10 09/05/2008 23:59:48
10/05/2008 09:30:02 10/05/2008 09:29:40
............... ............. ............... ............

Please help me , how can i write a query for this situation?


Thanks in advance,
Vadhimoo

 

[John Spencer]

Use the DateAdd function in an update query.

DateAdd("s",-22,[Tablename].[DateFieldName] + [Tablename].[TimeFieldName])

That will give you the adjust date and time. Then apply the DateValue
function to return just the date and the TimeValue function to get just
the time.

It is often the best policy to store both the date and time together in
one field and then split out the date or time when needed. Sometimes it
is better to store the two separately - usually when you need to do a
searching and sorting on just the time in a large number of records.

By the way Date and Time are both bad field names since they are also
functions to return the system date and time (respectively).

UPDATE YourTable
SET New_Time = TimeValue(
DateAdd("s",-22,[Tablename].[DateFieldName] + [Tablename].[TimeFieldName]))
, New_Date = DateValue(DateAdd("s",-22,[Tablename].[DateFieldName] +
[Tablename].[TimeFieldName]))

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