date help appreciated

[diablo]

hi

my access database may be placed on OS with different date settings - i.e.
the date may be m/d/y or d/m/y

how can i use this databse in a consisten way?

I want to be able to sort on the date for example 'select * from ...
mydatetime asc'

I also want to filter so that i can say 'select * from ... where mydatetime[color=blue]
>' & mydatetimevar[/color]


which is the best way to store the dates in the database i.e. if i get the
date using now() what do i do to it before putting into the database.

if i just store now() into a datetime then it gets confused when dates are
like 07/08/2005 - which can be in jul or aug

is there a way i can get now() to give me a better datetime value.

i am using VBScript ASP by the way.

Thanks

D

[Allen Browne]

Re: date help appreciated

See:
International Dates in Access
at:

http://allenbrowne.com/ser-36.html

The article comes from 12 years experience working with Access in a d/m/y
country, and explains how to handle literal values in VBA and SQL.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"diablo" <diablo@noplace .com> wrote in message
news:J5NCe.1798 $Ox6.1665@newsf e1-win.ntli.net...[color=blue]
>
> my access database may be placed on OS with different date settings - i.e.
> the date may be m/d/y or d/m/y
>
> how can i use this databse in a consisten way?
>
> I want to be able to sort on the date for example 'select * from ...
> mydatetime asc'
>
> I also want to filter so that i can say 'select * from ... where
> mydatetime[color=green]
>>' & mydatetimevar[/color]
>
>
> which is the best way to store the dates in the database i.e. if i get the
> date using now() what do i do to it before putting into the database.
>
> if i just store now() into a datetime then it gets confused when dates are
> like 07/08/2005 - which can be in jul or aug
>
> is there a way i can get now() to give me a better datetime value.
>
> i am using VBScript ASP by the way.
>
> Thanks
>
> D[/color]

[Chandra]

Re: date help appreciated

Hi
The better way to do that is, store the field in the database in the
format that you wanted. Say, for example let the column be varchar2(10)

before you store the value in the database, store it as
Format(datevalu e,"mm/dd/yyyy")

in that case whatever might be the regional setting values, the date
will be stored in the format that you wanted.

When u are trying to check the values back, use CDATE(datefield )

please let me know if this helped you

best Regards,
Chandra

http://groups.msn.com/SQLResource/

http://chanduas.blogspot.com/

---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***

[Allen Browne]

Re: date help appreciated

"Chandra" <chandra@discus sions.hotmail.c om> wrote in message
news:KDNCe.3$Oa 2.3295@news.usw est.net...[color=blue]
> Hi
> The better way to do that is, store the field in the database in the
> format that you wanted. Say, for example let the column be varchar2(10)[/color]

That has to be the worst advice I've seen in here all year.

The nonsense text date not only has all the problems associated with
interpretation in different locales, but also is unsortable, unfilterable,
and cannot perform basic date math without very clumsy and inefficient
string manipulation.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[diablo]

Re: date help appreciated

[color=blue]
> at:
> http://allenbrowne.com/ser-36.html
>
> The article comes from 12 years experience working with Access in a d/m/y
> country, and explains how to handle literal values in VBA and SQL.[/color]

I have read the above article - along with a couple of others.

my deduction is that if i convert my dates to yyyy/mm/dd before inserting
them into th database and use this format when filtering, i will cover all
my bases. am i correct?

thanks for your help

D

[Br@dley]

Re: date help appreciated

diablo <diablo@noplace .com> wrote:[color=blue][color=green]
>> at:
>> http://allenbrowne.com/ser-36.html
>>
>> The article comes from 12 years experience working with Access in a
>> d/m/y country, and explains how to handle literal values in VBA and
>> SQL.[/color]
>
> I have read the above article - along with a couple of others.
>
> my deduction is that if i convert my dates to yyyy/mm/dd before
> inserting them into th database and use this format when filtering, i
> will cover all my bases. am i correct?
>
> thanks for your help
>
> D[/color]

No, you shouldn't need to do anything as Access stores dates as a serial
number.

The way you enter dates and how they are dsplayed by default depends on
the OS. ie. if the OS is setup to be m/d/yyyy then that is how you enter
the date and how you will see it by default in Access but it will be
stored as a serial number. If you format your form fields as "short
date", "long date" etc Access uses the OS to determine the format of
each.

(In code Access expects American formatted dates....just to be more
confusing)

Make sense? :)
--
regards,

Bradley

A Christian Response

http://www.pastornet.net.au/response