Question on date format in SQL statement 
Author Message
 Question on date format in SQL statement


 VB 5.0
 Access 97

I have a question regarding date formats in a SQL statement.

An application I am developing in VB 5.0 has dates going back as
far as 1908. Access 97 automatically adds the century to any date
prior to 1929 or so.  Thus, in Access 97, the century is
displayed along with the year.
e.g. 25/01/08 (dd/mm/yy) is displayed as 25/01/1908

I am required to display data in the format dd/mm/yyyy regardless
of the setting in the Control Panel. I have no problem doing this via
the Format() function for all data entry screens, grid controls, etc.

e.g. Format(rs_PublicMainComplaintData("OPENED_DATE"),
where s_PublicDateFormat is "dd/mm/yyyy"


My question has to do with the date format in a SQL statement.

Suppose I want to create a recordset containing data in a date
range from 01/01/1997 to 31/07/1997.  This is dd/mm/yyyy.  The
user has their date format set to mm/dd/yy. Given the fact that
the date in the date field in the table (MAINCOMPLAINTDATA) could
range from January 1, 1908 to the current date, I'm unsure as to
how to format the dates in the SQL statement

I tried to format the SQL statement as follows:
   Select * From MAINCOMPLAINTDATA Where _
      Format(OPENED_DATE,'dd/mm/yyyy') >= '01/01/1997' And _
      Format(OPENED_DATE,'dd/mm/yyyy') <= '31/07/1997'
This did not work. Very slow and returned the wrong data.

Then I tried:
   Select * From MAINCOMPLAINTDATA Where _
      OPENED_DATE >= DateValue('01/01/1997') And _
      OPENED_DATE <= DateValue('31/07/1997')

This seems to work ok.

Am I on the right track here?

Thanks for any pointers.

Rick Haslip

Tue, 19 Oct 1999 03:00:00 GMT
 Question on date format in SQL statement

>   Select * From MAINCOMPLAINTDATA Where _
>      Format(OPENED_DATE,'dd/mm/yyyy') >= '01/01/1997' And _
>      Format(OPENED_DATE,'dd/mm/yyyy') <= '31/07/1997'
>This did not work. Very slow and returned the wrong data.

>Then I tried:

I always convert dates to the same format (and this works fine)
Dim StartDate,EndDate

Startdate = "1/1/1997"
EndDate = "31/7/1997"

   Select * From MAINCOMPLAINTDATA Where _
      Format(OPENED_DATE,"mm/dd/yyyy")  >= _
        Format(StartDate,"mm/dd/yyyy')  And _
        Format(OPENED_DATE,"mm/dd/yyyy")  <= _
        Format(EndDate,"mm/dd/yyyy')  Then

using variable for the searchdates gives you  more flexibility. And
don't forget Access uses always (internal) the american date format
even if the user has an european format selected!

Check out

for the file Access

gr. walther

----- it's the line -----
Walther Musch; Tilburg University

Sat, 23 Oct 1999 03:00:00 GMT
 [ 2 post ] 

 Relevant Pages 

1. Format Dates in SQL statement

2. Date format in SQL-statement

3. Date Time format to Date format

4. very simple question about dates formats in SQL

5. easy question: formatting dates using T-SQL

6. Access - SQL format date question

7. Simple question: java.sql.Date and Escape formatting

8. Converting Unix Date/time format into SQL readable format

9. SQL Statement question re automated date ranges

10. Date format in INSERT-statement

11. Date fields default output format on select statements

12. Formatting date in INSERT statement problem

Powered by phpBB® Forum Software