VB.NET SQL date is changed format in query

Question

Hi folks,

I've got a date variable that looks like this:

     Dim LogDate As Date = Date.Today.AddDays(-1)

the format comes out like: #4/5/2010#

then it goes into a SQL select query as a WHERE clause. When I debug, the query has changed this to '05/04/2010'. I want it to be in the format '04/05/2010' like it is when declared. Any ideas on how I do this?

Hee is the query:

 Dim sqlCmd As New SqlCommand("SELECT TOP (100) PERCENT tblBackup.BackupName, 
 tblBackupArchive.BackupDate, tblStatus.Status FROM tblStatus INNER JOIN tblBackupArchive ON 
 tblStatus.StatusID = tblBackupArchive.StatusID INNER JOIN tblBackup ON tblBackupArchive.BackupID      = 
 tblBackup.BackupID INNER JOIN tblClient ON tblBackup.ClientID = tblClient.ClientID WHERE      tblBackupArchive.BackupDate = '" & LogDate & "' AND (tblBackupArchive.StatusID = 3) ORDER BY 
 tblBackupArchive.BackupDate DESC", connection)

-- Jonesy

Answer 1

The best way would be to use a SQLCommand object with a suitable named parameter in the where clause - this would make the formatting of the textual representation of the date totally beside the point...

Another approach, if you're using MS-SQL, would be to use a date in the following format:

Where date = '20100504'

Be careful when using dates though - remember that behind the scenes they are DateTimes...

Martin.