Simple date extraction question

[dman]

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.

 

[BruceM]

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.

 

[dman]

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
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.

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.

 

[Pat Hartman \(MVP\)]

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.

 

[Jamie Collins]

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.

--

 

[Guest]

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.

 

[dman]

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.

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.

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.

 

[dman]

solution...

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

Thanks all.

 

[Pat Hartman \(MVP\)]

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...

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

Thanks all.

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.

 

[dman]

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:
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...

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

Thanks all.

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.

 

[Pat Hartman \(MVP\)]

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
strings.

DateValue will work nicely here.
Thanks.
.

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...

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

Thanks all.

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.