Remove Time from a date/time field

[Trevor G]

Is it possible to loop through a table that is holding around 600,000 records to remove the time in field and leave the date in there.

The Field is called TimeStamp (but only needs to hold the date), someone is using a query to append records but have include the Now() Function rather than Date(). Some times the query is run several times in the same day so different times are being placed in the field (not a good practice).

Which has now caused some issues with reporting.

In the table the field TimeStamp has the data type of date/time and in the format it has dd/mmm/yyyy

 

[JamesMcS]

If you use the now() function to stamp the record, I think Access will store the time with the date no matter what. If you want to report on a particular day, all you have to do is specify a date range in your query using Between - it seems to take it from 00:00:00 on date A up to 23:59:59 on date B.

 

[Guus2005]

a datetime field is in fact a double. convert it to a long and the time will be gone or use the Date() function to get the date without the time.

HTH

 

[JamesMcS]

Can you store a date in a long field? Or the date's serial number? Which of course includes the time.....

 

[Guus2005]

The date's "serial number" is in fact the number of days counting from 12-31-1899 which equals 0

In your immediate window:
?cdate(0)
12-31-1899
?clng(date())
40332


Enjoy!

 

[JamesMcS]

That's what I mean - if it's a long, and a now() is stored in there, it'll come out as the number of days since NYE 1899 and a fraction for the time.

I thought the OP was asking for a way to ONLY store the date, which I don't think Access does. They'll have to use Between in the query to pick up all records in a date range, regardless of the time.

 

[Guus2005]

I agree that Access stores both date and time but using a query you can disregard the time when you convert the field to a long and compare it to a date without the time.

select * from table1 where clng(dtm) = date()

In Trevor's case i'd create a query and add the converted field:

select *, clng(dtm) as DateField from table1

The reports must be based on the query rather then the table. Which IMO is better to start from.

HTH

 

[Trevor G]

It seems that I have missed the threads replies, thank you all for your input.

I kind of figured that I could use a select query to use an expression to adjust he field then an update query to put the result back. So what I have recommended to the user is as follows:

Create a query and add the following in the SQL view

SELECT [Slips YTD].[Time Stamp], Int([time stamp]) AS DateOnly
FROM [Slips YTD];

Then save the query with a name qrySelectTimeStamp

Then use an update query with the following:

UPDATE [Slips YTD] SET [Slips YTD].[Time Stamp] = Int([Time Stamp]);

Save the query and name it qryUpdateTimeStamp

Then in the Macro which someone else created to run the process they need add an open query for the update.

I have been told it works now.

But you are never to young/old to learn new ways and listen (read) to other peoples ideas and ways around things.

So thank you all again.

 

[Brianwarnock]

If you use the now() function to stamp the record, I think Access will store the time with the date no matter what. If you want to report on a particular day, all you have to do is specify a date range in your query using Between - it seems to take it from 00:00:00 on date A up to 23:59:59 on date B.

If you don't specify a time it will always default to 00:00:00

Brian

 

[Brianwarnock]

To change the field in the table just run an update query

update to datevalue([Timestamp])

then change the design to a properties of date only to hide the 00:00:00

Brian

 

[Trevor G]

Hi Brian,

I have had a further look into the Table design and found the issue, the designer of the database used CDate(Now()) in the properties of the Time Stamp field I have amended it to CDate(Date()) and it works fine.

Thank you for your advice.