Access use just date in date/time field

Murray Brown
Murray Brown used Ask the Experts™
on
Hi. In the following Access query I want to get just the date from the CreatedDTG field. I tried using DateValue(CreatedDTG) but that didn't work

Select ArmType As [Arm Type], ActionType as [Action Type], Manufacturer, Country, Calibre1 As Calibre, SerialNo As [Serial Number], Source From t_Arms Where CreatedDTG >= #01 Jan 1900# And CreatedDTG <= #10 Jan 2020#
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
What is the value of CreatedDTG ?
Try this
CDate(Formatdatetime(CreatedDTG ,vbShortDate))

Open in new window

Ryan ChongSoftware Team Lead

Commented:
try this instead:

Select ArmType As [Arm Type], ActionType as [Action Type], Manufacturer, Country, Calibre1 As Calibre, SerialNo As [Serial Number], Source From t_Arms Where CreatedDTG is not null And CreatedDTG <= #10 Jan 2020#

Open in new window


assuming CreatedDTG is a date /time field
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
CreateDTG  has a value like 9/01/2020 7:33:19 PM
John TsioumprisSoftware & Systems Engineer

Commented:
Try this then
Cdate(format(" 9/01/2020 7:33:19 PM","Short Date"))

Open in new window

What data type is CreatedDTG ? Is it a date field? It sounds like it might be a text field.

When you say didn't work, please be more specific. Didn't work can mean everything from "It ran but returned unexpected results" to "It crashed my computer"
And I do believe you need to use the syntax mm/dd/yyyy so 20th of january would be #01/20/2020#
John TsioumprisSoftware & Systems Engineer

Commented:
If you want to "match" against the example probably you want
Select ArmType As [Arm Type], ActionType as [Action Type], Manufacturer, Country, Calibre1 As Calibre, SerialNo As [Serial Number], Source From t_Arms Where #" &  Cdate(format(CreatedDTG ,"Short Date")) & "# >= #" & CDate(Formatdatetime(StartDate ,vbShortDate))& "# And #" & CreatedDTG <= #CDate(Formatdatetime(EndDate ,vbShortDate)) & "#"

Open in new window


This for example works
Cdate(format("10/01/2020 7:33:19 PM","Short Date")) =CDate(Formatdatetime("10 Jan 2020" ,vbShortDate))

Open in new window

returning the Correct : True
Take Note that the whole process is "regional depended" ....in my case
Clipboard01.jpg
left([CreatedDTG],10)
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
CreatedDTG is a DateTime field. I should have mentioned that
clng([CreatedDTG])
cdate(clng([CreatedDTG]))
I think the issue is simply with the way you format your criteria. The SQL engine expects dates to be supplied ina  specific format.

Select ArmType As [Arm Type], ActionType as [Action Type], Manufacturer, Country, Calibre1 As Calibre, SerialNo As [Serial Number], Source 
From t_Arms 
Where CreatedDTG >= #01/01/1900# And CreatedDTG <= #01/10/2020#

Open in new window

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
A quick way of stripping off time is to use FIX(<field>).

 A Date/time is stored as a double float, with the whole number portion representing the date, and the decimal the time.   As such, it's impossible to have a date without a time, but you can make all the times the same, which with Fix(), would be midnight (00:00:00)

 Or as Ander's showed you, adjust the start/ends that you are using for criteria.

HTH,
Jim.
Distinguished Expert 2017
Commented:
I and others have mentioned this many times.  SQL ASSUMES ambiguous dates to be in the format of mm/dd/yyyy.  So whenever you use STRINGS for dates as you are doing, you MUST either use mm/dd/yyyy format or some other unambiguous format such as yyyy/mm/dd


DateValue(yourdate) WILL extract just the date portion from a datetime value and it will retain it's definition as a date datatype.  The return from this function is a date not a string..

Formatting a date ALWAYS converts it to a string so NEVER format dates unless you are aware of the ramifications of assuming how a string will be interpreted as a date.  In all situations #01/01/2020# will be LESS than #12/31/2019#  !!!  WHY? because "01" is less than "12" because strings are interpreted character by character, left to right.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial