Date Format

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

I am working with a government database that has a number field that contains
date information formatted to read 20080623. I am trying to get the
difference between two dates, but it's showing the difference between
20080313 and 20080417 as 104 days. I've tried a number of fixes shown for
others, but I keep getting a message that the fix is too complicated. Any
ideas on how to fix this so that a beginner can figure it out?
 
Convert the number into a true date/time field like this:
DateSerial(Left([d],4), Mid([d],5,2), Right([d],2))
where d represents the name of your numeric field.
 
Jim said:
I am working with a government database that has a number field that
contains date information formatted to read 20080623. I am trying to
get the difference between two dates, but it's showing the difference
between 20080313 and 20080417 as 104 days. I've tried a number of
fixes shown for others, but I keep getting a message that the fix is
too complicated. Any ideas on how to fix this so that a beginner can
figure it out?
Click to expand...
You need to convert them to dates and then use DateDiff().

I believe I have a function in my library to convert these integers to dates
.... let's see ... yes, here it is:

Public Function ConvIntToDate(ByVal num As Long) As Date
'Converts integers that represent dates in yyyymmdd or yymmdd format
'to actual dates
'Returns 12/31/9999 if there's an error

On Error GoTo Err_Handler
Dim s as string
s=CStr(num)
If num < 19000000 Then
ConvIntToDate = Cdate(Mid(s, 3, 2) & "/" & _
Right(s, 2) & "/" & Left(s, 2))
Else
ConvIntToDate = DateSerial(Left(s, 4),Mid(s, 5, 2), _
Right(s, 2))
End If

Exit_Func:
Exit Function

Err_Handler:
ConvIntToDate = #12/31/9999#
End Function

Just copy this into a module in your database. Then, in your query, use it
like this:

DateDifference: DateDiff("d",ConvIntToDate([Date1]),ConvIntToDate([Date2]))
 
And how many days are actually between these two dates ... ?
Fewer than 104 I would imagine ... :-)
 
I'm not sure I understand your point.
You seem to be doing the same thing I do in my function.
As presented, the numbers are in yyyymmdd format.
Using Allen Browne's example:

x = 20080313
x = dateserial(left(x, 4), mid(x,5,2), mid(x, 7,2))
y = 20080417
y = dateserial(left(y, 4), mid(y,5,2), mid(y, 7,2))
? x
3/13/2008
? y
4/17/2008
? datediff("d", x, y)
35

Bob
I am working with a government database that has a number field that
contains date information formatted to read 20080623. I am trying to
Click to expand...
[quoted text clipped - 3 lines]
too complicated. Any ideas on how to fix this so that a beginner can
figure it out?
Click to expand...
You need to convert them to dates and then use DateDiff().

I believe I have a function in my library to convert these integers
to dates ... let's see ... yes, here it is:

Public Function ConvIntToDate(ByVal num As Long) As Date
'Converts integers that represent dates in yyyymmdd or yymmdd format
'to actual dates
'Returns 12/31/9999 if there's an error

On Error GoTo Err_Handler
Dim s as string
s=CStr(num)
If num < 19000000 Then
ConvIntToDate = Cdate(Mid(s, 3, 2) & "/" & _
Right(s, 2) & "/" & Left(s, 2))
Else
ConvIntToDate = DateSerial(Left(s, 4),Mid(s, 5, 2), _
Right(s, 2))
End If

Exit_Func:
Exit Function

Err_Handler:
ConvIntToDate = #12/31/9999#
End Function

Just copy this into a module in your database. Then, in your query,
use it like this:

DateDifference:
DateDiff("d",ConvIntToDate([Date1]),ConvIntToDate([Date2]))
Click to expand...
Click to expand...
 
Ah! You replied to the wrong post ... I see now.
Should have been more concise. Was attempting to clear
up this misconception.

When I subtract 313 from 417, I get 104.

Bob said:
I'm not sure I understand your point.
You seem to be doing the same thing I do in my function.
As presented, the numbers are in yyyymmdd format.
Using Allen Browne's example:
Click to expand...
[quoted text clipped - 50 lines]
DateDifference:
DateDiff("d",ConvIntToDate([Date1]),ConvIntToDate([Date2]))
Click to expand...
Click to expand...
Click to expand...
 
You can also return a true date/time value with:

CDate(Format([TheDateField],"0000-00-00"))

YYYY-MM-DD is the ISO standard for date notation, so returning your number
in this format allows the CDate function to use it as an argument to return a
value of date/time data type, with which you can then do normal date
arithmetic.

In fact the DateDiff function will accept the string expressions without
converting them to true date/time values. To try it out enter the following
as a single line in the debug window:

? DateDiff("d",Format(20080313,"0000-00-00"),Format(20080417,"0000-00-00"))

Ken Sheridan
Stafford, England
 
Seriously? How many days are there between March 13 and April 17?
More like 36, isn't it?
Or are you arguing that those numbers might not represent those dates ... ?
 
Jeff said:
If the difference is showing as described, the OP isn't doing "date"
math.
Click to expand...

Exactly! He WANTS to do date math,

"I'm trying to get the difference between two dates, but ..."

but he could not figure out how to convert his numbers to proper dates so he
could do date arithmetic with them..>
Let's check back with the OP to see if the field is a Date/Time
field, or if the numbers are just numbers...
Click to expand...

No need. He told us exactly what the data type was:

" ... database has a number field that contains date information formatted
to read 20080623"

Come on, Jeff: time to let this go. Everyone else in the thread understood
the problem.
 
Forest for the trees ...

Thanks, Bob!

Jeff

Bob Barrows said:
Exactly! He WANTS to do date math,

"I'm trying to get the difference between two dates, but ..."

but he could not figure out how to convert his numbers to proper dates so he
could do date arithmetic with them..>


No need. He told us exactly what the data type was:

" ... database has a number field that contains date information formatted
to read 20080623"

Come on, Jeff: time to let this go. Everyone else in the thread understood
the problem.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Click to expand...
 
Back
Top