date format oledb command => ms access

[Stijn]

I have a problem with passing dates to my database. I have a table: tblError. And I want to delete some entries of this table at the beginning of my porgram. I'm taking a date fourteen days back from the current date.

 

Dim currentDate As Date = DateTime.Now

Dim fourteen As New TimeSpan(14, 0, 0, 0)

Dim fourteenDaysBack As Date = currentDate.Subtract(veertienDagen)

 

dbCommand = New OleDbCommand("DELETE * FROM tblError WHERE (((tblError.ErrTime)<#" & fourteenDaysBack.Date & "#))"

,dbConnectie)

 

dbCommand.ExecuteNonQuery()

 

When I'm running the program and I check the dates in the source code, they are in the format that I want: dd/MM/yyyy

 

But when the dbCommand is executing the day and the month is switched. The wrong entries are removed from my access table.

 

I checked the regional settings of my operating system but the are in the correct format.

 

The entries are deleted correctly when I make an access delete querry with the same sql statement.

 

I have the same problem when I want to select some entries in an other table between two dates, it isn't working. At this moment, I select all the entries in the table and check the date into my source code but my table will grow and it will work slower.

 

Does anyone knows whats going wrong?

[TechnoTone]

When you use "fourteenDaysBack.Date", convert it to a string with .ToString and use the format "dd-MMM-yyyy". This way there is no confusion as to which part of the date is the Day and which part is the Month.

[Stijn]

Thanks for your reply, but in the meantime I found a solution for my problem and it's working with the following command:

 

dbCommand = New OleDbCommand("DELETE * FROM tblError WHERE (((tblError.ErrTime)<#" & Format(fourteenDaysBack, "yyyy-MM-dd) & "#))"

 

I just had to format the date. But I do not understand why I have to do this. But I'm happy that it's working.

 

But I have a problem with sql that looks the same. I have the following query

 

SELECT dbo.tblWinkel.AFK, dbo.tblImportCCD.CCDDatum, dbo.tblImportCCDDetail.CCDNaam, dbo.tblImportCCDDetail.CCDWaarde,

dbo.tblImportCCDDetail.CCDQty

FROM dbo.tblImportCCD INNER JOIN

dbo.tblWinkel ON dbo.tblImportCCD.WinkelFK = dbo.tblWinkel.WinkelPK INNER JOIN

dbo.tblImportCCDDetail ON dbo.tblImportCCD.ImportCCDPK = dbo.tblImportCCDDetail.ImportCCDFK

WHERE (dbo.tblImportCCD.CCDDatum > CONVERT(DATETIME, '2004-02-20', 102)) AND (dbo.tblWinkel.AFK = N'AAL_DEN') AND

(dbo.tblImportCCDDetail.CCDNaam = 'Bon 1 EUR') OR

(dbo.tblImportCCDDetail.CCDNaam = 'Bon 3 EUR') OR

(dbo.tblImportCCDDetail.CCDNaam = 'Maaltijdcheque') OR

(dbo.tblImportCCDDetail.CCDNaam = 'TicketRestaurant')

ORDER BY dbo.tblWinkel.AFK

 

But the problem is that I get alle the records back even the CCDDatum is less then 20/02/2004 and also all the other records where AFK isn't AAL_DEN.

 

The data type of CCDDAtum is datetime with size 8 and the datatype of AFK is nvarchar(50)

 

I really don't know what's going wrong because the selection of the CCDNaam is working. I only get the records with CCDNaam = 'Bon 1 EUR' or 'Bon 3 EUR' or 'Maaltijdcheque' or ' TicketRestaurant'

 

Does anyone knows what's wrong?

[Stijn]

:-\ My mistake, I forgot some brakets in the commandstring

 

SELECT dbo.tblWinkel.AFK, dbo.tblImportCCD.CCDDatum, dbo.tblImportCCDDetail.CCDNaam, dbo.tblImportCCDDetail.CCDWaarde,

dbo.tblImportCCDDetail.CCDQty

FROM dbo.tblImportCCD INNER JOIN

dbo.tblWinkel ON dbo.tblImportCCD.WinkelFK = dbo.tblWinkel.WinkelPK INNER JOIN

dbo.tblImportCCDDetail ON dbo.tblImportCCD.ImportCCDPK = dbo.tblImportCCDDetail.ImportCCDFK

WHERE (dbo.tblImportCCD.CCDDatum > CONVERT(DATETIME, '2004-02-20', 102)) AND (dbo.tblWinkel.AFK = N'AAL_DEN') AND

((dbo.tblImportCCDDetail.CCDNaam = 'Bon 1 EUR') OR

(dbo.tblImportCCDDetail.CCDNaam = 'Bon 3 EUR') OR

(dbo.tblImportCCDDetail.CCDNaam = 'Maaltijdcheque') OR

(dbo.tblImportCCDDetail.CCDNaam = 'TicketRestaurant'))

ORDER BY dbo.tblWinkel.AFK

[TechnoTone]

Thanks for your reply, but in the meantime I found a solution for my problem and it's working with the following command:

 

dbCommand = New OleDbCommand("DELETE * FROM tblError WHERE (((tblError.ErrTime)<#" & Format(fourteenDaysBack, "yyyy-MM-dd) & "#))"

 

I just had to format the date. But I do not understand why I have to do this. But I'm happy that it's working.

This is why I always write my dates with a character month - it eliminates any uncertainty.

 

The reason you code is now working is purely coincidence. It just so happens that the database engine is interpreting it the correct way. Personally, I wouldn't be happy with your solution and would change the month part to MMM to be 100% sure.

[pendragon]

I use the DateValue Function in the sql statement so that it takes into account the International settings.

 

ie

dbo.tblImportCCD.CCDDatum > DateValue('" & DateVariable & "')"

 

This should sort out both problems