Select by job date stopped working on 13/01/01

Select by job date stopped working on 13/01/01

Post by The Kingofs » Wed, 24 Jan 2001 02:01:47



Hi

Can anyone answer this, our report generator uses code likwe

"select * from table where job_date between #"+format(11/01/01,
dd/mm/yy)+"# and #"+format(13/01/01,dd/mm/yy)+"# order by etc"

This has worked fine for 2 years until 13/01/2001, then it stopped
working until I changed the format to mm/dd/yyy.

Can anyonec explain this

--
Kevin C Martin

Sent via Deja.com
http://www.deja.com/

 
 
 

Select by job date stopped working on 13/01/01

Post by Ed » Wed, 24 Jan 2001 09:30:06


Try checking your server settings. They may have been changed to the
mm/dd/yy format. Using the format() statement like you are isn't really
doing anything.  you could simple type ...between #11/01/01# and
#13/01/01# order by...   It has to be the server settings.


> Hi

> Can anyone answer this, our report generator uses code likwe

> "select * from table where job_date between #"+format(11/01/01,
> dd/mm/yy)+"# and #"+format(13/01/01,dd/mm/yy)+"# order by etc"

> This has worked fine for 2 years until 13/01/2001, then it stopped
> working until I changed the format to mm/dd/yyy.

> Can anyonec explain this

> --
> Kevin C Martin

> Sent via Deja.com
> http://www.deja.com/

--
Posted via CNET Help.com
http://www.help.com/

 
 
 

Select by job date stopped working on 13/01/01

Post by The Kingofs » Wed, 24 Jan 2001 20:07:52


Thanks Ed



> Try checking your server settings. They may have been changed to the
> mm/dd/yy format. Using the format() statement like you are isn't
really
> doing anything.  you could simple type ...between #11/01/01# and
> #13/01/01# order by...   It has to be the server settings.


> > Hi

> > Can anyone answer this, our report generator uses code likwe

> > "select * from table where job_date between #"+format(11/01/01,
> > dd/mm/yy)+"# and #"+format(13/01/01,dd/mm/yy)+"# order by etc"

> > This has worked fine for 2 years until 13/01/2001, then it stopped
> > working until I changed the format to mm/dd/yyy.

> > Can anyonec explain this

> > --
> > Kevin C Martin

> > Sent via Deja.com
> > http://www.deja.com/

> --
> Posted via CNET Help.com
> http://www.help.com/

--
Kevin C Martin

Sent via Deja.com
http://www.deja.com/

 
 
 

Select by job date stopped working on 13/01/01

Post by Douglas J. Steel » Thu, 25 Jan 2001 08:37:29


Are you _sure_ it used to work? SQL statements in Access have always
required that the dates be in mm/dd/yyyy format, regardless of what the date
format is set to via your Regional Settings.

In any case, you're probably running into a problem with the fact that
you're using 2 digit dates. When you were using 99 or 00 as the year, there
was no ambiguity: those numbers could only be years. However, now that
you've got 13/01/01, there's more than one way to interpret it. January 1st,
2013 becomes a valid possiblity, and Access is likely choosing that.

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/


> Hi

> Can anyone answer this, our report generator uses code likwe

> "select * from table where job_date between #"+format(11/01/01,
> dd/mm/yy)+"# and #"+format(13/01/01,dd/mm/yy)+"# order by etc"

> This has worked fine for 2 years until 13/01/2001, then it stopped
> working until I changed the format to mm/dd/yyy.

> Can anyonec explain this

> --
> Kevin C Martin

> Sent via Deja.com
> http://www.deja.com/

 
 
 

1. Input date of 01-JAN-99 Defaults to 01-JAN-2099

I am trying to run this insert statement with a start_date of Jan 1, 1999:
Insert into monthlyoffers
   (offer_title, offer_desc, start_date, end_date, username, pointofcontact,
id_mom_category, id_market,
inserted_date)
Values
   ('Test','This is a test', '01-JAN-99', '', 'irosa', '', 8, 1,
'26-JAN-01');

The statement runs fine, but when I run this query:
select to_char(start_date, 'DD-MON-YYYY')
from monthlyoffers
where start_date >='01-JAN-01';

Result is: 01-JAN-2099

Should this be happening?  How do I force year 19xx?

2. Security Problems with ASP

3. problems with online 5.01.UD2 together with I-STAR TCP/IP 5.01.UD3

4. Character fields overflow

5. OI 1.2/01 (vax.vms/01) ISQL problem

6. ATTACH/DETACH sql databases

7. xxxxx - autoexec.bat [01/01]

8. data overflow error

9. **MAKING MONEY FOR YOU OWN DATABASE - a.txt [01/01]

10. Heeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeelp - vb.txt [01/01]

11. Help labels for UIObjects on a form - helplbl.zip [01/01]

12. - ER Diagram [01/01] Re: Filemaker Pro 3.0 many-to-many relation

13. Importing a text file - Smallctx.txt [01/01]