still having probs with select statement where date = todays date

[altf4]

Hi Guys

I am using vb6.0 connecting to a SQL database (runningMSDE)

I am trying to load all contacts where the recontactdate is today
I have tried the following but am getting the error - syntax error converting datetime to character string - please help!

my code -
onload - dtpicker1.value = date
(tblcontacts.dtmrecontactdate is date/time)

Set adoduecallbacks = New Recordset

adoduecallbacks.Open "Select * from tblcontacts where dtmrecontactdate = ' " & DTPicker1.Value & "%" & " ' ", db, adOpenStatic, adLockOptimistic

- Please help!!!

Thanks

[szlamany]

You really should format the date in the SQL query string to be:

YYYY-MM-DD.

If the field you are comparing to has "time" in it, then use CONVERT on it to get it to be YYYY-MM-DD only.

[altf4]

Hi There
thansks for the reply

The date format in the required table is d/mm/yyyy
so would i need to convert it to that?

[altf4]

something like
"Select * from tblcontacts where dtmrecontactdate = ' " & format(Date,d/mm/yyy) & "%" & " ' ",

[brucevde]

date format in the required table is d/mm/yyyy

Dates are not stored in a "Format". They are stored as decimal data. Formats are only used for display purposes.

By default, SQL Server always assumes mm/dd/yyyy when it converts literal strings to dates.

So if your DatePicker value is 06/01/2004, displayed in dd/mm/yyyy format (ie Jan 6) , SQL Server will interpret that as June 1.

As suggested make sure your date literals use the ISO standard of yyyymmdd for comparisons.

adoduecallbacks.Open "Select * from tblcontacts where dtmrecontactdate = '" & Format$(DTPicker1.Value,"yyyymmdd") & "'", db, adOpenStatic, adLockOptimistic

By the way the conversion error was being caused by the % sign. % is only used as a Wildcard with Like.

[szlamany]

I mentioned the time issue because I saw that you were putting the "%" on the date.

Since you have MSDE you probably don't have query analyzer - if you did you could do a SELECT * in that tool and see exactly what SQL has stored in your column.

How is that column being filled? GETDATE() - CURRENT_TIMESTAMP?

At any rate, BRUCEVDE is correct in saying that the date is stored internally as a decimal value - this has nothing to do with how it displays and how you query it.

Whenever I write a query in QA, I use YYYY-MM-DD, but DD/MM/YYYY is also recognized.

If you do not have QA (or ENTERPRISE MANAGER), consider buying the DEVELOPERS EDITION of FULL MS SQL 2000 - it has the tools that can manage the MSDE environment - and it's inexpensive.

The most IMPORTANT thing you can do is start treating DATES in QUERIES consistently and follow that technique throughout your code.

[altf4]

Hi There

The column is being filled when a user selects a date using the dtpicker on the contacts form.

Thanks for the input guys - the issue is resolved by using the following -

adoduecallbacks.Open "Select * from tblcontacts where dtmrecontactdate = '" & Format$(Date, "yyyymmdd") & "'", db, adOpenStatic, adLockOptimistic