converting a date to a date but without the time element

[colmkav]

How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?

My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

[Deanna Earley]

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

I use either DateValue() or Int().
I have had unexpected issues with DateValue not working properly with
Date values (as it expects a String, and it got day/month backwards) so
most of my code now uses Int().

[colmkav]

Post by Deanna Earley

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

I use either DateValue() or Int().
I have had unexpected issues with DateValue not working properly with
Date values (as it expects a String, and it got day/month backwards) so
most of my code now uses Int().
--
i-Catcher Development Teamhttp://www.icode.co.uk/icatcher/
iCode Systems
(Replies direct to my email address will be ignored.
Please reply to the group.)

seem to get overflow error now when I use INT. Not quite sure why.

Previously when I use contextdate = cdate(now()-1) to set my
contextdate and then use in my SQL query ">= [contextdate]" I get the
results requested but I want to be able to just select the contextdate
and not all those after it.

[Deanna Earley]

Post by colmkav

Post by Deanna Earley

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

I use either DateValue() or Int().
I have had unexpected issues with DateValue not working properly with
Date values (as it expects a String, and it got day/month backwards) so
most of my code now uses Int().

seem to get overflow error now when I use INT. Not quite sure why.

Int() or CInt()?
Cint() converts to an Integer type (which will overflow). Int() just
removes the decimal (time) part.

Post by colmkav
Previously when I use contextdate = cdate(now()-1) to set my
contextdate and then use in my SQL query ">= [contextdate]" I get the
results requested but I want to be able to just select the contextdate
and not all those after it.

If doing it in SQL, you'll also need to make sure that the value in the
SQL comparison is also just a date. How depends on the SQL dialect your
server is using.

[colmkav]

Post by Deanna Earley

Post by colmkav

Post by Deanna Earley

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

I use either DateValue() or Int().
I have had unexpected issues with DateValue not working properly with
Date values (as it expects a String, and it got day/month backwards) so
most of my code now uses Int().

seem to get overflow error now when I use INT. Not quite sure why.

Int() or CInt()?
Cint() converts to an Integer type (which will overflow). Int() just
removes the decimal (time) part.

Post by colmkav
Previously when I use contextdate = cdate(now()-1) to set my
contextdate and then use in my SQL query ">= [contextdate]" I get the
results requested but I want to be able to just select the contextdate
and not all those after it.

If doing it in SQL, you'll also need to make sure that the value in the
SQL comparison is also just a date. How depends on the SQL dialect your
server is using.
--
i-Catcher Development Teamhttp://www.icode.co.uk/icatcher/
iCode Systems
(Replies direct to my email address will be ignored.
Please reply to the group.)- Hide quoted text -
- Show quoted text -

I used INT in the vba code. Tried different things in the SQL code
also but nothing is working yet. I am using ACCESS 2007.

[Karl E. Peterson]

Post by colmkav

Post by Deanna Earley

Post by colmkav
seem to get overflow error now when I use INT. Not quite sure why.

Int() or CInt()?
Cint() converts to an Integer type (which will overflow). Int() just
removes the decimal (time) part.

I used INT in the vba code. Tried different things in the SQL code
also but nothing is working yet. I am using ACCESS 2007.

Time to "show us the code." I just did this, in Access 2010's
Immediate window:

?application.version
14.0
?now
6/7/2012 11:47:33 AM
?int(now)
6/7/2012

Works fine. I *suspect* you used CInt, which *does* overflow. But
CLng doen't!

?clng(now)
41067

So, what are you /really/ doing? Actual code.

[Helmut_Meukel]

Post by colmkav

Post by Deanna Earley

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

I use either DateValue() or Int().
I have had unexpected issues with DateValue not working properly with
Date values (as it expects a String, and it got day/month backwards) so
most of my code now uses Int().

seem to get overflow error now when I use INT. Not quite sure why.
Previously when I use contextdate = cdate(now()-1) to set my
contextdate and then use in my SQL query ">= [contextdate]" I get the
results requested but I want to be able to just select the contextdate
and not all those after it.

As others already answered CLng() or Int() would do.
If your code *really* looks like
contextdate = cdate(now()-1)
then why do you use Now() at all?
Have a look at the other related functions Date() and Time() and you'll
see Date() is what you really should use.

To me it looks like you have probably *two* problems:
1) get a contextdate value without the time part
- solved with either Int(), CLng() or Date() - and
2) compare your contextdate value with the stored values from your
db field which may or may not contain DateTime values with a time
part.
If there is a time component in vour db values, one possible solution
would be to use in your SQL query something like
">= [contextdate] AND < [contextdate]+1".

This said, you stated with ">= [contextdate]" in your SQL query you get
the requested results plus all those after it.
That's isn't correct. If contextdate contains a time part, you'll miss
all records with this date with lower time values or no time value at
all.

HTH,

Helmut.

[Barry Etheridge]

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

DateString = Now.ToShortDateString

[Deanna Earley]

Post by Barry Etheridge

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

DateString = Now.ToShortDateString

They're talking about VBA/VB6. Your reply is for VB.NET and doesn't
actually keep it as a DateTime. The closest .NET equivalent would be
fulldatetime.Date.

[DaveO]

Post by Barry Etheridge

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

DateString = Now.ToShortDateString

Yuck, that's horrible.

Firstly it does not do what was asked, it converts a date to a string and
secondly it is from the appalling .NET which is not really welcome or
helpful here.



DaveO.

[ReverendFuzzy]

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

You COULD to break it down into smaller chunks...

Private Function CompareDates(otherdate As Date, thisdate As Date) As Boolean
Dim NowYear, NowMonth, NowYear As Integer
Dim ThenYear, ThenMonth, ThenYear As Integer

' break them down
NowYear = Year(thisdate) : ThenYear = Year(otherdate)
NowMonth = Month(thisdate) : ThenMonth = Month(otherdate)
NowDay = Day(thisdate) : ThenDay = Day(otherdate)

' compare (example)
If (NowYear=ThenYear And NowMonth=ThenMonth And NowDay=ThenDay) Then
CompareDates=True
Else
CompareDates=False
End If
End Function

This would pass the two dates being compared to the function, and return
TRUE if they match, or FALSE if they don't. This probably isn't the compare you'll actually being doing, but this demonstrates how to break it down, then compare them with a DOUBLE-AND within an IF/THEN

[ReverendFuzzy]

Post by colmkav
How can I take a date provided as a time eg now() and convert it to a
date without the hours, minutes etc?
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

You COULD to break it down into smaller chunks...

Private Function CompareDates(otherdate As Date, thisdate As Date) As Boolean
Dim NowYear, NowMonth, NowDay As Integer
Dim ThenYear, ThenMonth, ThenDay As Integer

' break them down
NowYear = Year(thisdate) : ThenYear = Year(otherdate)
NowMonth = Month(thisdate) : ThenMonth = Month(otherdate)
NowDay = Day(thisdate) : ThenDay = Day(otherdate)

' compare (example)
If (NowYear=ThenYear And NowMonth=ThenMonth And NowDay=ThenDay) Then
CompareDates=True
Else
CompareDates=False
End If
End Function

This would pass the two dates being compared to the function, and return
TRUE if they match, or FALSE if they don't. This probably isn't the compare you'll actually being doing, but this demonstrates how to break it down, then compare them with a DOUBLE-AND within an IF/THEN

[Theo Tress]

Post by colmkav
My issue is that I want to compare this date with that of other dates
but at the moment they arent being evaluated as equal because of the
hours and minutes difference.

try this:

Dim D as Double
Dim L as Long
D = Now 'or any other date
L = CLng(Fix(D))
Debug.Print D, L


Fix() will truncate (and not round up) the hh:nn:ss part, and can handle
long integers, so you can easily compare two dates:

Dim SameDay as Boolean
SameDay = CLng(Fix(D1)) = CLng(Fix(D2)) 'true if D1 refers to same day as
D2