• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Solved Remove Time Stamp From Table Field (1 Viewer)

Number11

Member
Local time
Today, 10:13
Joined
Jan 29, 2020
Messages
180
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, 05:13
Joined
Jan 23, 2006
Messages
13,360
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, 10:13
Joined
Jan 29, 2020
Messages
180
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, 10:13
Joined
Jul 26, 2013
Messages
7,341
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, 02:13
Joined
Oct 29, 2018
Messages
12,464
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, 10:13
Joined
Jan 29, 2020
Messages
180
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, 02:13
Joined
Oct 29, 2018
Messages
12,464
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, 10:13
Joined
Jul 26, 2013
Messages
7,341
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, 10:13
Joined
Jan 29, 2020
Messages
180
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