still having probs with select statement where date = todays date

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

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.

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?

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

Quote:

---

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.

Code:

---

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.

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.

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
:wave: