Date related query problem, need syntax help

[chasbrouck]

Hello,

I am trying to execute a query that gets a date from a user form and compares it against a field in my Database. The trouble is that the field in the database is a text field containing a date and a time in the following format: mm/dd/yyyy-hh:mm AM

I can not change this field to be a date or date time field, so I need to look at only the first 10 characters for my comparison. Below are two versions of the query I have tried with out sucess. Any help would be gratly appreciated.

Thanks,
Chris Hasbrouck
Software Developer
Bassett Healthcare

******************************************
Select PropertyControlNO, Description, ManufacturerID, ProductType, Dept, Name, EntryDate, EntryID, DeleteID, SUBSTRING('DeleteDate' FROM 1 FOR 10) as DDATE
From deleted
Where DDATE > DateValue('#Form.Date#')
Order by PropertyControlNO
*******************************************
Select PropertyControlNO, Description, ManufacturerID, ProductType, Dept, Name, EntryDate, EntryID, DeleteID, DeleteDate
From deleted
Where SUBSTRING('DeleteDate' FROM 1 FOR 10) > DateValue('#Form.Date#')
Order by PropertyControlNO

 

[Andel]

Can you try this.

Select PropertyControlNO, Description, ManufacturerID, ProductType, Dept, Name, EntryDate, EntryID, DeleteID, SUBSTRING('DeleteDate' FROM 1 FOR 10) as DDATE
From deleted
Where convert(datetime, left(@dt, 10)) > DateValue('#Form.Date#')
Order by PropertyControlNO Andel
andel@barroga.net

http://andyb.8m.com

 

[bperry]

I'm not sure if this helps you, but I don't think the syntax of the SUBSTRING() function in SQL Server is:

SUBSTRING('DeleteDate' FROM 1 FOR 10) as DDATE

Instead, I think the correct syntax is:
SUBSTRING(DeleteDate, start, length) as DDATE

Maybe I'm off base here.

bp

 

[bperry]

Of course,
SUBSTRING(DeleteDate, start, length) as DDATE

should be:

SUBSTRING(DeleteDate,1,10) as DDATE

sorry about that.
bp