Simple date extraction question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dman - 22 Feb 2007 14:46 GMT

Hi.

How do I pull the date out of a date/time field type.

I need to know when '02/08/07 8:05:24 AM' is equal to 02/08/07.

I'm looking at the extract date function and I must have the wrong syntax.
DatePart("mm/dd/yy",[punches].[Date_Time]) = #02/08/07#

Thanks.

Reply to this Message

BruceM - 22 Feb 2007 14:59 GMT

You could format a control such as a text box to the date format of your
choice, and bind it to that field. You could also use an expression in a
query or text box, or VBA code. In any case, apply the desired date format.
DatePart is used to extract a single aspect of a date such as year, quarter,
month, day of the year, and so forth. DatePart("yyyy",[DateField]) will
return the year from the datefield. "mm/dd/yy" is not one of the choices,
and the part of the expression after the comma is the date from which the
year is to be extracted. Review Help for more information on the function.

> Hi.
>
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

dman - 22 Feb 2007 15:14 GMT

I am using VB and doing a Select on a table.

strSQL = "SELECT DISTINCT Job_Code FROM SCHEDULES " & _
"WHERE SSN = '" & UpdateRec.SSN & _
"AND Date_Time = " & format(UpdateRec.DateTime,"MM/DD/YY")

Date_Time is defined date/time in Access and contains vales like 02/08/07
8:05:24 AM
Format(UpdateRec.DateTime,"MM/DD/YY") = 02/08/07

What should the expression in my SQL look like to get a match on date?

Thanks.

> You could format a control such as a text box to the date format of your
> choice, and bind it to that field. You could also use an expression in a
[quoted text clipped - 18 lines]
>>
>> Thanks.

Reply to this Message

Pat Hartman (MVP) - 22 Feb 2007 15:14 GMT

DateValue(yourdate) will return only the date portion of the date/time field
and TimeValue(yourdate) will return only the time portion.

Access help has been hopeless for the past 10 years (since A97) but they are
finally making improvements in the search engine (not the responsibility of
the Access team) and content (which is the responsibility of the Access
team). Search for "Functions by category". It comes up as the second item
in my list. Unfortunately, DateValue() isn't listed. I pressed the Yes
response to send them a report about the missing entry. The more reports
they get of errors/omissions in help, the better it will be.

> Hi.
>
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

dman - 22 Feb 2007 15:19 GMT

Thank you. That was what I was looking for, for the last hour in help.

> DateValue(yourdate) will return only the date portion of the date/time
> field and TimeValue(yourdate) will return only the time portion.
[quoted text clipped - 19 lines]
>>
>> Thanks.

Reply to this Message

Jamie Collins - 22 Feb 2007 15:16 GMT

> How do I pull the date out of a date/time field type.

Here's a couple of ways, using the current timestamp NOW() as an
example DATETIME value:

SELECT DATEVALUE(NOW())

SELECT DATEADD('D', DATEDIFF('D', #1990-01-01 00:00:00#, NOW()),
#1990-01-01 00:00:00#)

Jamie.

--

Reply to this Message

Jerry Whittle - 22 Feb 2007 15:17 GMT

Int([punches].[Date_Time]) = #02/08/07#

It makes sense once you realize that Access stores #02/08/07# as 39121
and #02/08/07 8:05:24 AM# as 39121.3370833333.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> Hi.
>
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

dman - 22 Feb 2007 15:36 GMT

solution...

"AND DateValue(Date_Time) = #" & Format(UpdateRec.DateTime, "MM/DD/YY") &
"#"

Thanks all.

> Hi.
>
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

Pat Hartman (MVP) - 22 Feb 2007 21:07 GMT

Why are you formatting the DateTime field?
If DateTime is defined as a date/time field then:
AND DateValue(Date_Time) = DateValue(UpdateRec.DateTime)

Formatting turns a date into a string. If you are in the US and using the
standard mm/dd/yy format, you can get away with it but it will cause you
nothing but trouble if you use non-US formats. Remember a format is only
how a date is displayed. It doesn't affect how a date is stored. Once you
format a date, it becomes a string and will be compared as a string rather
than as a date. That means that "01/01/07" is less than "01/02/06" if you
compare the string value rather than the stored date value.

> solution...
>
[quoted text clipped - 14 lines]
>>
>> Thanks.

Reply to this Message

dman - 23 Feb 2007 13:46 GMT

Thanks for pointing that out. I am use to working with DB2 databases
and occasionally I use Format for certain timestamps when dealing with
strings.

DateValue will work nicely here.
Thanks.
.

> Why are you formatting the DateTime field?
> If DateTime is defined as a date/time field then:
[quoted text clipped - 26 lines]
>>>
>>> Thanks.

Reply to this Message

Pat Hartman (MVP) - 26 Feb 2007 21:30 GMT

I would recommend formatting ONLY in the final step such as a form or report
and ONLY if necessary. If I have a need to format in a query, I include an
unformatted copy if I need to sort on the formatted field.

> Thanks for pointing that out. I am use to working with DB2 databases
> and occasionally I use Format for certain timestamps when dealing with
[quoted text clipped - 34 lines]
>>>>
>>>> Thanks.

Reply to this Message