Date Format Problem

X

x-rays

Hello All,

I got a little problem with the data I want to fetch limited by a date
range.

What are my settings and what I use:

1) My regional settings are Greek and using the dd/mm/yyyy format for
dates (to be exact the short date format in my settings is d/M/yyyy)

2) I use Access 2003 (English Version) but my database is in Access
2000 Format

3) Windows XP SP2 (might be irrelevant but...)

In my tables, date is stored in dd/mm/yyyy format (because of regional
sets).

I have created 2 functions to return the first and the last date of the
year:

Function FirstDayOfTheYear_manual()
FirstDayOfTheYear_manual = DateSerial(Year(CVDate(Forms![Print
Reports]![Starting date])), 1, 1)

End Function

Function LastDayOfTheYear_manual()
LastDayOfTheYear_manual = DateSerial(Year(CVDate(Forms![Print
Reports]![Starting date])) + 1, 1, 1 - 1)

End Function

Now I create a simple Select query:

Select * from Table1 Where DateField between FirstDayOfTheYear_manual()
and LastDayOfTheYear_manual()

1st question:
I know that Access automaticaly changes the format of the dates in
mm/dd/yyyy for the where clauses but is it changes the format of the
DateField too?

2nd question:
Why this does not return any records:
Select * from Table1 Where Format(DateField, "mm/dd/yyyy") between
FirstDayOfTheYear_manual() and LastDayOfTheYear_manual()

I want this query to run in any system that uses english, greek,
chinese... any regional settings.
 
B

Brendan Reynolds

You only need to format literal date values, there's no need to use the
Format function like that with a date/time field. Using the Format function
in your second query will coerce the value to a string, causing an
alphabetic comparison rather than a date/time comparison. For example ...

? #01/01/2006# > #02/02/2005#
True

This returns True because any date in the year 2006 is greater than any date
in the year 2005.

? "01/01/2006" > "02/02/2005"
False

This returns False because any string that begins with "02" is greater than
any string that begins with "01".

You might want to change the return type of your functions to Date ...

Function FirstDayOfTheYear_manual() as Date

As currently written, the return type is undeclared, so it is Variant by
default.
 
X

x-rays

Hello Brendan,

Thank you very much for your response, I'm a little confused now about
how to handle dates. When do I need to format the date in the American
way (mm/dd/yyyy) so all my will appear correct?

In the fucntions I made, do I need the format there or in my query (
Where Format(FirstDayOfTheYear_manual(), "mm/dd/yyyy").... )?

I saw quiet times when creating a dynamic sql query, formating the date
field too, is this wrong (as you said before it is)?

Do I need the format when exporting data in csv (delimited) files?

PS: I think this will be a long day and night.

Brendan said:
You only need to format literal date values, there's no need to use the
Format function like that with a date/time field. Using the Format function
in your second query will coerce the value to a string, causing an
alphabetic comparison rather than a date/time comparison. For example ...

? #01/01/2006# > #02/02/2005#
True

This returns True because any date in the year 2006 is greater than any date
in the year 2005.

? "01/01/2006" > "02/02/2005"
False

This returns False because any string that begins with "02" is greater than
any string that begins with "01".

You might want to change the return type of your functions to Date ...

Function FirstDayOfTheYear_manual() as Date

As currently written, the return type is undeclared, so it is Variant by
default.

--
Brendan Reynolds
Access MVP

x-rays said:
Hello All,

I got a little problem with the data I want to fetch limited by a date
range.

What are my settings and what I use:

1) My regional settings are Greek and using the dd/mm/yyyy format for
dates (to be exact the short date format in my settings is d/M/yyyy)

2) I use Access 2003 (English Version) but my database is in Access
2000 Format

3) Windows XP SP2 (might be irrelevant but...)

In my tables, date is stored in dd/mm/yyyy format (because of regional
sets).

I have created 2 functions to return the first and the last date of the
year:

Function FirstDayOfTheYear_manual()
FirstDayOfTheYear_manual = DateSerial(Year(CVDate(Forms![Print
Reports]![Starting date])), 1, 1)

End Function

Function LastDayOfTheYear_manual()
LastDayOfTheYear_manual = DateSerial(Year(CVDate(Forms![Print
Reports]![Starting date])) + 1, 1, 1 - 1)

End Function

Now I create a simple Select query:

Select * from Table1 Where DateField between FirstDayOfTheYear_manual()
and LastDayOfTheYear_manual()

1st question:
I know that Access automaticaly changes the format of the dates in
mm/dd/yyyy for the where clauses but is it changes the format of the
DateField too?

2nd question:
Why this does not return any records:
Select * from Table1 Where Format(DateField, "mm/dd/yyyy") between
FirstDayOfTheYear_manual() and LastDayOfTheYear_manual()

I want this query to run in any system that uses english, greek,
chinese... any regional settings.
Click to expand...
Click to expand...
 
B

Brendan Reynolds

Allen Browne has a good article on the subject of handling dates in Access,
at the following URL ...

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

--
Brendan Reynolds
Access MVP

x-rays said:
Hello Brendan,

Thank you very much for your response, I'm a little confused now about
how to handle dates. When do I need to format the date in the American
way (mm/dd/yyyy) so all my will appear correct?

In the fucntions I made, do I need the format there or in my query (
Where Format(FirstDayOfTheYear_manual(), "mm/dd/yyyy").... )?

I saw quiet times when creating a dynamic sql query, formating the date
field too, is this wrong (as you said before it is)?

Do I need the format when exporting data in csv (delimited) files?

PS: I think this will be a long day and night.

Brendan said:
You only need to format literal date values, there's no need to use the
Format function like that with a date/time field. Using the Format
function
in your second query will coerce the value to a string, causing an
alphabetic comparison rather than a date/time comparison. For example ...

? #01/01/2006# > #02/02/2005#
True

This returns True because any date in the year 2006 is greater than any
date
in the year 2005.

? "01/01/2006" > "02/02/2005"
False

This returns False because any string that begins with "02" is greater
than
any string that begins with "01".

You might want to change the return type of your functions to Date ...

Function FirstDayOfTheYear_manual() as Date

As currently written, the return type is undeclared, so it is Variant by
default.

--
Brendan Reynolds
Access MVP

x-rays said:
Hello All,

I got a little problem with the data I want to fetch limited by a date
range.

What are my settings and what I use:

1) My regional settings are Greek and using the dd/mm/yyyy format for
dates (to be exact the short date format in my settings is d/M/yyyy)

2) I use Access 2003 (English Version) but my database is in Access
2000 Format

3) Windows XP SP2 (might be irrelevant but...)

In my tables, date is stored in dd/mm/yyyy format (because of regional
sets).

I have created 2 functions to return the first and the last date of the
year:

Function FirstDayOfTheYear_manual()
FirstDayOfTheYear_manual = DateSerial(Year(CVDate(Forms![Print
Reports]![Starting date])), 1, 1)

End Function

Function LastDayOfTheYear_manual()
LastDayOfTheYear_manual = DateSerial(Year(CVDate(Forms![Print
Reports]![Starting date])) + 1, 1, 1 - 1)

End Function

Now I create a simple Select query:

Select * from Table1 Where DateField between FirstDayOfTheYear_manual()
and LastDayOfTheYear_manual()

1st question:
I know that Access automaticaly changes the format of the dates in
mm/dd/yyyy for the where clauses but is it changes the format of the
DateField too?

2nd question:
Why this does not return any records:
Select * from Table1 Where Format(DateField, "mm/dd/yyyy") between
FirstDayOfTheYear_manual() and LastDayOfTheYear_manual()

I want this query to run in any system that uses english, greek,
chinese... any regional settings.
Click to expand...
Click to expand...
Click to expand...
 
X

x-rays

Yes I'm looking at it,

thank you very much Brendan.


Brendan said:
Allen Browne has a good article on the subject of handling dates in Access,
at the following URL ...

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

--
Brendan Reynolds
Access MVP

x-rays said:
Hello Brendan,

Thank you very much for your response, I'm a little confused now about
how to handle dates. When do I need to format the date in the American
way (mm/dd/yyyy) so all my will appear correct?

In the fucntions I made, do I need the format there or in my query (
Where Format(FirstDayOfTheYear_manual(), "mm/dd/yyyy").... )?

I saw quiet times when creating a dynamic sql query, formating the date
field too, is this wrong (as you said before it is)?

Do I need the format when exporting data in csv (delimited) files?

PS: I think this will be a long day and night.

Brendan said:
You only need to format literal date values, there's no need to use the
Format function like that with a date/time field. Using the Format
function
in your second query will coerce the value to a string, causing an
alphabetic comparison rather than a date/time comparison. For example ...

? #01/01/2006# > #02/02/2005#
True

This returns True because any date in the year 2006 is greater than any
date
in the year 2005.

? "01/01/2006" > "02/02/2005"
False

This returns False because any string that begins with "02" is greater
than
any string that begins with "01".

You might want to change the return type of your functions to Date ...

Function FirstDayOfTheYear_manual() as Date

As currently written, the return type is undeclared, so it is Variant by
default.

--
Brendan Reynolds
Access MVP

Hello All,

I got a little problem with the data I want to fetch limited by a date
range.

What are my settings and what I use:

1) My regional settings are Greek and using the dd/mm/yyyy format for
dates (to be exact the short date format in my settings is d/M/yyyy)

2) I use Access 2003 (English Version) but my database is in Access
2000 Format

3) Windows XP SP2 (might be irrelevant but...)

In my tables, date is stored in dd/mm/yyyy format (because of regional
sets).

I have created 2 functions to return the first and the last date of the
year:

Function FirstDayOfTheYear_manual()
FirstDayOfTheYear_manual = DateSerial(Year(CVDate(Forms![Print
Reports]![Starting date])), 1, 1)

End Function

Function LastDayOfTheYear_manual()
LastDayOfTheYear_manual = DateSerial(Year(CVDate(Forms![Print
Reports]![Starting date])) + 1, 1, 1 - 1)

End Function

Now I create a simple Select query:

Select * from Table1 Where DateField between FirstDayOfTheYear_manual()
and LastDayOfTheYear_manual()

1st question:
I know that Access automaticaly changes the format of the dates in
mm/dd/yyyy for the where clauses but is it changes the format of the
DateField too?

2nd question:
Why this does not return any records:
Select * from Table1 Where Format(DateField, "mm/dd/yyyy") between
FirstDayOfTheYear_manual() and LastDayOfTheYear_manual()

I want this query to run in any system that uses english, greek,
chinese... any regional settings.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Formatting date gives bad results in query 2
calculate Age problem date notation european american format 2
Data Type error in WHERE, but not in SELECT 2
Forcing a date entry format of dd/mm/yyyy 1
Changing date fotmats & calculating date - HELP 5
Date format in query export. 4
Excel is changing date format incorrectly 7
Peculiar date problem 1

Top