Solved Remove Time Stamp From Table Field

[Number11]

Hi, how do i remove the time from a date within a table? Can i do this through a query?

 

[jdraw]

You could change the default value of the field to Date rather than Now.
You could use an update query using the DateValue function.

eg
?Now()
16-Oct-20 8:42:09 AM
?Datevalue(now)
16-Oct-20

Good luck.

 

[Number11]

You could change the default value of the field to Date rather than Now.
You could use an update query using the DateValue function.

eg
?Now()
16-Oct-20 8:42:09 AM
?Datevalue(now)
16-Oct-20

Good luck.

Thanks for your reply, Sorry i did not explain, I have imported data into a new table and i have the formagting set to short date, however the data imported still has the time stamp after date, I need to remove the time as when i run a query looking for a date range the data that have the time after the date are not included in the results

 

[Minty]

Formatting the data does not change it, merely how you see it displayed.
If you really do want to delete the time information rather than simply querying it in the right fashion to get the results you want than as @jdraw suggested you will have to run a one-time update query.

Update YourTable SET MyDatefield = DateValue(MyDatefield)

Once done this can't be reversed. So make sure you really don't think you will ever need the time element.

 

[theDBguy]

Thanks for your reply, Sorry i did not explain, I have imported data into a new table and i have the formagting set to short date, however the data imported still has the time stamp after date, I need to remove the time as when i run a query looking for a date range the data that have the time after the date are not included in the results

Hi. @Minty beat me to it, but you could also just add one day to your query range.

 

[Number11]

Formatting the data does not change it, merely how you see it displayed.
If you really do want to delete the time information rather than simply querying it in the right fashion to get the results you want than as @jdraw suggested you will have to run a one-time update query.

Update YourTable SET MyDatefield = DateValue(MyDatefield)

Once done this can't be reversed. So make sure you really don't think you will ever need the time element.

So cant get this to work

Update Outcome Master SET Appointment Date = DateValue(Appointment Date) - i am getting syntax error in UPDATE statement ?

 

[theDBguy]

So cant get this to work

Update Outcome Master SET Appointment Date = DateValue(Appointment Date) - i am getting syntax error in UPDATE statement ?

Try:

Update [Outcome Master]  SET [Appointment Date] = DateValue([Appointment Date])

You should really avoid using spaces in your table and field names.

 

[Minty]

You have spaces in your field names, which is not a good idea. Welcome to bracket city, where you get to do extra typing for lots of no extra reward...

Update [Outcome Master] SET [Appointment Date] = DateValue([Appointment Date])

 

[Number11]

Try:

Update [Outcome Master]  SET [Appointment Date] = DateValue([Appointment Date])

You should really avoid using spaces in your table and field names.

worked thank you so much

 

[theDBguy]

worked thank you so much

Hi. You're welcome. @jdraw, @Minty, and I were all happy to assist. Good luck with your project.