Date Select SQL Problems

[dmc1980]

Hi All
Been cracking up with this one for a few days and have no idea why it is doing it. I have a database table which stores the schedules for inspections. When an operator signs into the system a snapshot is displayed showig the user the inspections for today, tomorrow, this week and overdue. The strange thing is, none of the date selections are working correctly.
The likes of today, 24/08/2005, should return 0 overdue, 0 for tomorrow, 0 for today and 0 for this week, as the only inspection in the table is for the 01/09/2005. But when the queries are run, it returns 1 inspection overdue which isnt correct. The field in the table is setup as a date field etc.
Code as below

Dim connection As New OleDbConnection(gsConnection) 
Dim gSql As String
 
Dim null As System.DBNull
 
Dim pickdate As Date = insDate
 
Dim sdate As String
 
pickdate = Format(pickdate, "dd/MM/yyyy")
 
sdate = pickdate
 
gSql = "Select LocationName, InspectionDate, Inspector from Schedule where (((Schedule.InspectionDate) < #" & sdate & "#))" 
 
connection.Open()
 
Dim adapter As New OleDbDataAdapter(gSql, connection)
 
Dim dataset As New DataSet
 
adapter.Fill(dataset)
 
adapter.Dispose()
 
connection.Close()
 
overdue = dataset.Tables(0)
 
Dim count As Integer
 
count = overdue.Rows.Count()
 
If count <> 0 Then
 
lblOverdue.Text = count & " inspections are overdue."
 
'Display_SnapShot()
 
Else
 
lblOverdue.Text = "0 inspections are overdue."
 
dgSnapshot.DataSource = null
 
End If

 

[TechGnome]

I'm going to go out on a limb here and assume that this is Access, right? And then I'm going to mention that most (especialy MS ones) DBMSs dates are in m/d/y format, which means that while the DB may say 1/9/2005, the DB probably mis reads that (just like I did) and think that it is for Jan 9, 2005, which would fill the requirement of being less than 24/08/2005 (which the DB can correctly assume as the 24th of Aug, since there is no month 24.).

Tg

 

[dmc1980]

You are correct in saying it is an access database.
But if my system dates etc are all set to dd/mm/yyyy will it not assume that format throughout? What ways do I have of combatting this?

 

[dmc1980]

And also, I just changed the system date to 01/09/2005 as in 1st September 2005 and it selects the inspection date as being scheduled for today.

 

[TechGnome]

The way the DBMS stores and deals with dates is independant of system settings. All system settings is is a disaply type of setting. How the DB actualy stores the date is the problem.

To combat it, you have to force it into a format that is recognizable, and yet clear in what is a month and what is a day.

This is the only thing I can think of off the top of my head:

gSql = "Select LocationName, InspectionDate, Inspector from Schedule where (((Schedule.InspectionDate) < #" & FORMAT$(sdate, "dd mmm yyyy") & "#))"

That'll format it to 24 Aug 2005.... it's then clear what the date parts are, and Access will translate it properly.

This might work too:

gSql = "Select LocationName, InspectionDate, Inspector from Schedule where (((Schedule.InspectionDate) < #" & FORMAT$(sdate, "yyyy-mm-dd") & "#))"

If you are still getting the wrong date, then I'd double check the value in the database itself and make sure it is what you think it is (if it still think's it's Jan 9, rather than Sept 1, you'll continue to have issues.)

See, that 01-09-2005 still reads Jan 9 to me....which is how the DB is probably reading it.

Tg