Problems using Date formatting. (1 Viewer)

MikeCouling

New member
Local time
Today, 13:15
Joined
Feb 14, 2020
Messages
7
I'm processing moth records and am intereted in the earliest & latest appearnces of each species. I'm getting a problem when formatting the date - it seems to subtract a day. I'm using the following SQL :-
SELECT
Taxa.Taxon,
Records.Date,
Format([Date],"ddmmmyyyy") AS RecordDate,
DatePart("y",[date]) AS DayOfYear,
Format(DatePart("y",[date]),"mmmdd") AS DateOfYear
FROM Records INNER JOIN Taxa ON Records.[*Taxon] = Taxa.[_guk]
WHERE (((Taxa.Taxon)="Agriopis leucophaearia") AND ((Records.Date)=[dateto]))
ORDER BY DatePart("y",[date]);

This gives the following :-
1581692708447.png


Formatting the date seems to have subtracted 1 from its value. Could this be that the date is stored as a floating point number (it's a long integer in the Records table) and there's rounding going on, or am I over-thinking things.
Suggestions gratefully received.
 

Minty

AWF VIP
Local time
Today, 13:15
Joined
Jul 26, 2013
Messages
10,354
I thin you can simplify this
Code:
Format(DatePart("y",[date]),"mmmdd") AS DateOfYear
to
Code:
Format([date],"mmmdd") AS DateOfYear
No idea why you would need to deal with year part?
 

MikeCouling

New member
Local time
Today, 13:15
Joined
Feb 14, 2020
Messages
7
I thin you can simplify this
Code:
Format(DatePart("y",[date]),"mmmdd") AS DateOfYear
to
Code:
Format([date],"mmmdd") AS DateOfYear
No idea why you would need to deal with year part?
Hi Minty, thanks for the reply.
I want to find the earliest and latest dates within each year that the moths have ever appeared. So is the earliest ever March 1st or Feb 28th for example? Similarly for latest. I can then see if records reported this year are exceptional or not.
Format just changes the appearance so although I wouldn't see the year anymore, it would still be in there and the records wouldn't sort in the way that I want.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 28, 2001
Messages
26,998
Could this be that the date is stored as a floating point number

Dates ARE floating-point numbers. They are what is called a "TYPECAST" of DOUBLE. That TYPECAST means, in practical terms, they are stored one way but interpreted in another way. Just like Boolean is actually a BYTE integer stored as either 0 or -1 even though we see it as FALSE or TRUE. However, there is adequate precision in DOUBLE for any date and time you will see in your lifetime unless your name is Methuselah. So I didn't think that was your problem.

I did a quickie experiment in the VBA Immediate Window and got this:

Code:
debug.Print format( 43466, "y" )
1
debug.Print format( 43466, "mmmdd" )
Jan01
debug.Print format( 43466, "ddmmmyyyy" )
01Jan2019
debug.Print datepart("y", 43466 )
 1
debug.Print format( datepart( "y", 43466 ), "mmmdd" )
Dec31

In particular, note that the last step gives the same oddity you reported, and I used integers throughout. So I'm thinking there is a bit of a flaw in the logic of what you did, and after a moment of reflection, I have an answer. Just so you could see where I'm coming from, I got a link for you. In this article, there is a significant sentence.


A date value of 0 represents December 30, 1899.

In your nested call, you have the year in the DatePart (i.e. 2019 is implied by 43466). But the result of that DatePart function is 1 (see second-to-last line of my experiment) and you feed that 1 into another date-formatter routine. According to the Microsoft article, a date value of 1 is December 31, 1899. So what happened is that you formatted the wrong date with that nesting of format(datepart()). You obfuscated the real date by taking a function of a function.

Minty's suggestion will fix your problem by removing the obfuscating layered function.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:15
Joined
Feb 19, 2013
Messages
16,553
it seems to subtract a day
I think you will find it is because of day zero. Dates are stored as numbers (today is 43875) which is the number of days since 31/12/1899 (which is day 1 (ergo day 0 is 30/12/1899) - so 1/1/1900 is 2, not 1.

Format just changes the appearance
the format property does, the format function does not - it converts it to a string - and with strings, Apr01 will come before Mar31.

I suspect what you actually need is a number - mmdd so 331 (first zero not required) and 401 which you can get from the formula

note that date is a reserved word (it is a function that returns today), using it as a field name can cause unexpected results and errors
 

MikeCouling

New member
Local time
Today, 13:15
Joined
Feb 14, 2020
Messages
7
That's what I get for assuming Access Day 1 was a Jan 1st !!
Thanks to Minty for the correct code - does just what I want and has cleared up my misunderstanding of Format - as has CJ's post.
Thanks Doc_Man for the link explaining Day 0 was a Dec 30th - I expect there is a good reason for this (?) and for the detailed explanation.
I take the point about using "Date" as a field name - not my choice unfortunately.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:15
Joined
Oct 29, 2018
Messages
21,357
That's what I get for assuming Access Day 1 was a Jan 1st !!
Thanks to Minty for the correct code - does just what I want and has cleared up my misunderstanding of Format - as has CJ's post.
Thanks Doc_Man for the link explaining Day 0 was a Dec 30th - I expect there is a good reason for this (?) and for the detailed explanation.
I take the point about using "Date" as a field name - not my choice unfortunately.
Hi Mike. Glad to hear you got it sorted out. I was a little late to the party and was doing a little experiment like what Doc did. The only additional thing I tried was use the CDate() function to actually see what Access sees. (see attached image below). I forgot about the Access epoch date, so I'm glad Doc reminded us of it. Good luck with your project!

date.PNG
 

MikeCouling

New member
Local time
Today, 13:15
Joined
Feb 14, 2020
Messages
7
Hi Mike. Glad to hear you got it sorted out. I was a little late to the party and was doing a little experiment like what Doc did. The only additional thing I tried was use the CDate() function to actually see what Access sees. (see attached image below). I forgot about the Access epoch date, so I'm glad Doc reminded us of it. Good luck with your project!

View attachment 79118
Glad I wasn't being a total wally DBguy. Dates are always a minefield whatever the platform. I got to find out about the Immediate window as well!!
 

isladogs

MVP / VIP
Local time
Today, 13:15
Joined
Jan 14, 2017
Messages
18,186
Just for info, the Excel and Access teams clearly didn't talk to each other when developing the two programs.
Although day zero in Access in 30 Dec 1899, in Excel its 31 Dec 1899. Sigh!
Both are internally consistent and I've never yet seen an issue exchanging date data between the two programs...though other members may tell you differently.
 

moke123

AWF VIP
Local time
Today, 09:15
Joined
Jan 11, 2013
Messages
3,849
another just for info, Day 0 of a month gives you the last date of the previous month
?dateserial(2020,5,0)
4/30/2020
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 28, 2001
Messages
26,998
I expect there is a good reason for this (?)

Microsoft has a good reason? <pffft><choke><chuckle>

I know why UNIX has a date in 1970 as its epoch date - has to do with the year of the first UNIX.

I know why OpenVMS has 17-Nov-1858 as its epoch date - has to do with Smithsonian time after a widely observed astronomical event.

Here is a little reference to advise you of why certain other dates were chosen.


The one which explains why Dec. 31, 1899 was chosen will amuse you. However, we have been calling it the wrong date all this time. According to the article, that is REALLY Jan 0, 1900. (No kidding - read the article. It's a 13-slide slideshow.)
 
Last edited:

MikeCouling

New member
Local time
Today, 13:15
Joined
Feb 14, 2020
Messages
7
Microsoft has a good reason? <pffft><choke><chuckle>

I know why UNIX has a date in 1970 as its epoch date - has to do with the year of the first UNIX.

I know why OpenVMS has 17-Nov-1858 as its epoch date - has to do with Smithsonian time after a widely observed astronomical event.

Here is a little reference to advise you of why certain other dates were chosen.


The one which explains why Dec. 31, 1899 was chose will amuse you. However, we have been calling it the wrong date all this time. According to the article, that is REALLY Jan 0, 1900. (No kidding - read the article. It's a 13-slide slideshow.)
Entertaining stuff! Nothing in there about why Access uses Dec 30 1899. I've always had the impression that the Excel & the Access teams didn't get on but to disagree on that for no better reason would seem a bit extreme.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 28, 2001
Messages
26,998
Perhaps because Access and Excel weren't developed by Microsoft - they were purchased or absorbed or however you want to put it and then adapted to the office model. Bill Gates was very big about legally acquiring small companies to obtain rights to their products that he thought would be good. So they have different epoch dates due to having different origins.
 

Users who are viewing this thread

Top Bottom