Solved Remove Time Stamp From Table Field (1 Viewer)

Number11

Member
Local time
Today, 21:04
Joined
Jan 29, 2020
Messages
607
Hi, how do i remove the time from a date within a table? Can i do this through a query?
 

jdraw

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Jan 23, 2006
Messages
15,364
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

Member
Local time
Today, 21:04
Joined
Jan 29, 2020
Messages
607
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

AWF VIP
Local time
Today, 21:04
Joined
Jul 26, 2013
Messages
10,355
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

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
21,358
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

Member
Local time
Today, 21:04
Joined
Jan 29, 2020
Messages
607
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

I’m here to help
Staff member
Local time
Today, 14:04
Joined
Oct 29, 2018
Messages
21,358
So cant get this to work

Update Outcome Master SET Appointment Date = DateValue(Appointment Date) - i am getting syntax error in UPDATE statement ?
Try:
Code:
Update [Outcome Master]  SET [Appointment Date] = DateValue([Appointment Date])
You should really avoid using spaces in your table and field names.
 

Minty

AWF VIP
Local time
Today, 21:04
Joined
Jul 26, 2013
Messages
10,355
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

Member
Local time
Today, 21:04
Joined
Jan 29, 2020
Messages
607
Try:
Code:
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 :)
 

Users who are viewing this thread

Top Bottom