Date Format

Hi,

I have a table which has a date/time field. I am storing them as follows :-

01/01/2005 11:25
01/01/2005 19:44
02/01/2005 05:04

re: Date Format

On Wed, 12 Jan 2005 16:03:07 -0000, Macca wrote:
[color=blue]
> Hi,
>
> I have a table which has a date/time field. I am storing them as follows :-
>
> 01/01/2005 11:25
> 01/01/2005 19:44
> 02/01/2005 05:04
> .
> .
> .
>
> I have stored them in DD/MM/YYYY format.
>
> However when i write a query in Access i notice it changes the format of
> the date in the query to american format, MM/DD/YYYY.
>
> Can anyone tell me if it is possible to do this using the UK date format of
> DD/MM/YYYY.
>
> Also what is the most efficient way to store a datetimestamp in Access?
>
> Thnaks In Advance
> Macca[/color]

Perhaps this will help you.
Dates (in a Date datatype field) or not stored with any particular
date format. They are stored as a double number, with an integer
portion representing the date and a decimal value representing the
time.

You can test this for yourself.
Open the debug window and type:
?cdbl(#1/1/2005 11:25 AM#)
1/1/2005 11:25 AM is stored as 38353.4756944444

The integer value (38353) represents the number of days since
12/31/1899, the date used as a start date.
The decimal value (.4756944444) represents the percentage of a 24 hour
day that has elapsed since 00:00:00 AM (midnight).
Noon on 1/1/2005 would be represented as 38353.5.

This permits easy and accurate date math, as you are simply adding or
subtracting numbers.

It is displayed using the system format, in your case dd/mm/yyyy etc.,
or in any other date format you wish by changing the Format property
of the control.


However, in a Query SQL, even though the date field value is stored as
a number, when entering dates as criteria you MUST use the U.S. format
of mm/dd/yyyy.
That's just the way it is.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.

re: Date Format

Macca wrote:[color=blue]
>
> I have a table which has a date/time field. I am storing them as
> follows :-
>
> 01/01/2005 11:25
> 01/01/2005 19:44
> 02/01/2005 05:04[/color]

No you're not. You are using format properties to *display* them that way.
DateTimes are always *stored* exactly the same way.
[color=blue]
> However when i write a query in Access i notice it changes the
> format of the date in the query to american format, MM/DD/YYYY.[/color]

When using explicit date strings in an Acess query you have to use American
format, ISO format (yyyy-mm-dd), or a format where the month uses letters
for the month (Jan-12-2005).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: Date Format

Thanks for the reply.

Does this mean that i have to store data in MM/DD/YYYY format when storing
data in database through SQL and when i do a query to get data i have to use
the american format then convert it into UK format for presentation?

This seems very inefficient especially for a large number of record.

I want to present data in an excel spreadsheet as DD/MM/YYYY HH:MM:SS. I do
this by doing a query on the database and then storing the resulting data in
an excel spreadsheet for presentation.

Also how do i determine how the data is presented in the database? i.e if i
open up access what will data look like in the date/time field?

Thanks
Macca

So does this mean that
"fredg" <fgutkind@example.invalid> wrote in message
news:1wqn9snlqypqa$.1w1no0jib277b.dlg@40tude.net.. .[color=blue]
> On Wed, 12 Jan 2005 16:03:07 -0000, Macca wrote:
>[color=green]
> > Hi,
> >
> > I have a table which has a date/time field. I am storing them as follows[/color][/color]
:-[color=blue][color=green]
> >
> > 01/01/2005 11:25
> > 01/01/2005 19:44
> > 02/01/2005 05:04
> > .
> > .
> > .
> >
> > I have stored them in DD/MM/YYYY format.
> >
> > However when i write a query in Access i notice it changes the format[/color][/color]
of[color=blue][color=green]
> > the date in the query to american format, MM/DD/YYYY.
> >
> > Can anyone tell me if it is possible to do this using the UK date format[/color][/color]
of[color=blue][color=green]
> > DD/MM/YYYY.
> >
> > Also what is the most efficient way to store a datetimestamp in Access?
> >
> > Thnaks In Advance
> > Macca[/color]
>
> Perhaps this will help you.
> Dates (in a Date datatype field) or not stored with any particular
> date format. They are stored as a double number, with an integer
> portion representing the date and a decimal value representing the
> time.
>
> You can test this for yourself.
> Open the debug window and type:
> ?cdbl(#1/1/2005 11:25 AM#)
> 1/1/2005 11:25 AM is stored as 38353.4756944444
>
> The integer value (38353) represents the number of days since
> 12/31/1899, the date used as a start date.
> The decimal value (.4756944444) represents the percentage of a 24 hour
> day that has elapsed since 00:00:00 AM (midnight).
> Noon on 1/1/2005 would be represented as 38353.5.
>
> This permits easy and accurate date math, as you are simply adding or
> subtracting numbers.
>
> It is displayed using the system format, in your case dd/mm/yyyy etc.,
> or in any other date format you wish by changing the Format property
> of the control.
>
>
> However, in a Query SQL, even though the date field value is stored as
> a number, when entering dates as criteria you MUST use the U.S. format
> of mm/dd/yyyy.
> That's just the way it is.
>
> --
> Fred
> Please only reply to this newsgroup.
> I do not reply to personal email.[/color]

re: Date Format

Thanks for the reply.

Does this mean that i have to store data in MM/DD/YYYY format when storing
data in database through SQL and when i do a query to get data i have to use
the american format then convert it into UK format for presentation?

This seems very inefficient especially for a large number of record.

I want to present data in an excel spreadsheet as DD/MM/YYYY HH:MM:SS. I do
this by doing a query on the database and then storing the resulting data in
an excel spreadsheet for presentation.

Also how do i determine how the data is presented in the database? i.e if i
open up access what will data look like in the date/time field?

Thanks
Macca
"Rick Brandt" <rickbrandt2@hotmail.com> wrote in message
news:34l2iaF4c0asaU1@individual.net...[color=blue]
> Macca wrote:[color=green]
> >
> > I have a table which has a date/time field. I am storing them as
> > follows :-
> >
> > 01/01/2005 11:25
> > 01/01/2005 19:44
> > 02/01/2005 05:04[/color]
>
> No you're not. You are using format properties to *display* them that[/color]
way.[color=blue]
> DateTimes are always *stored* exactly the same way.
>[color=green]
> > However when i write a query in Access i notice it changes the
> > format of the date in the query to american format, MM/DD/YYYY.[/color]
>
> When using explicit date strings in an Acess query you have to use[/color]
American[color=blue]
> format, ISO format (yyyy-mm-dd), or a format where the month uses letters
> for the month (Jan-12-2005).
>
> --
> I don't check the Email account attached
> to this message. Send instead to...
> RBrandt at Hunter dot com
>
>[/color]

re: Date Format

Macca wrote:[color=blue]
> Thanks for the reply.
>
> Does this mean that i have to store data in MM/DD/YYYY format [snip][/color]

No. It means that DateTimes are stored internally in whatever fashion the
designers of the database engine dictated. *You* have absolutely no control
over how the dates are stored. How you choose to enter them and display them
has absolutey nothing to do with how they are stored or how much space they take
up.

I can enter a date as January 12, 2005 and it is stored exactly the same as if I
had entered 1/12/05. In addition, I can enter the date as 1/12/05 and later
find that record with a query of...

SELECT blah blah FROM blah
WHERE DateField = #January 12, 2005#

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com