varchar and date

[boatguy]

I have a problem with a varchar field that has been used to store dates. This was not originally my project and I have no idea what problems converting the field would cause.
My problem is, I need to query between 2 dates. Here's my current query:

<CFQUERY name="Betweendates" datasource="datasource">
SELECT * FROM 
VIEW_Entry_TodaysNotes
WHERE CreatedDate between #DateNow# and #Dateminusformat# 
AND OrgID = '#ORGID#'
</cfquery>

And here's the error I get when I run it:

ODBC Error Code = 22005 (Error in assignment)


[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value '08/24/2002 10:45:18' to a column of data type int.


SQL = "SELECT * FROM VIEW_Entry_TodaysNotes WHERE CreatedDate between 08/30/2005 and 08/23/2005 AND OrgID = '106'"

Can I convert the CreatedDate field in the query and if so how?

Thanks.

 

[pigsie]

Looking at the code i'm guessing that CreatedDate is held as an int?

Is the data held in this format:

20050830

as 30th August '05? if so if you change the format of DateNow and Dateminusformat so they are in a similar format. e.g.

SELECT * FROM VIEW_Entry_TodaysNotes WHERE CreatedDate between 20050830 and 20050823 AND OrgID = '106' you may want to change it to also include the time.

 

[boatguy]

Actually, CreatedDate is a varchar field.

 

[boatguy]

I guess my question is, can I convert CreateDate from varchar to int or datetime in the query OR can I convert it in my view?

 

[boatguy]

I know I can use Select YEAR(Entries.CreatedDate) AS CreateDate and display the data as a four digit year. Is there a function to display is as mm/dd/yyyy?

 

[boatguy]

I got it. I used CAST(dbo.Entries.CreatedDate AS DATETIME) AS CreatedDate in my VIEW.

Thanks for your help today pigsie.