Between SQL date ranges

Hi 451ls,

 
you can use this one too. but pls try the suggestion of createodbcdate first.
#CreateODBCDate(yourDate)#

Regards,
---Pinal

Do I use the CreateODBCDate( ) on the select value or in the Query. Can you give me an example of what you mean?

WHERE CONVERT(DateBegin AS SMALLDATETIME, 103) BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#

If I understand you correctly.

Hi 451ls,

well, tacobell's suggestion is better use that pls.

Regards,
---Pinal

Tacobell777, what does the 103 mean.

its the format of the date, if you have MS SQL installed or something have a look at the books online and lookup CONVERT it shows you all the formats you can convert to.

Did it work?

Not yet - here is my WHERE clause:

WHERE CONVERT(DateBegin AS SMALLDATETIME, 103) BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#

The error is:
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near the keyword 'AS'.

7 : WHERE CONVERT(DateBegin AS SMALLDATETIME, 103) BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#
8 : AND SF.ProgramID IN (#pgmid#)

Oops, I was confused with CAST(blah AS blah)
Please use
CONVERT(DateBegin, SMALLDATETIME, 103)

my WHERE clause:
WHERE CONVERT(DateBegin, SMALLDATETIME, 103) BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#

The error:
[Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'SMALLDATETIME'.

7 : WHERE CONVERT(DateBegin, SMALLDATETIME, 103) BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#
8 : AND SF.ProgramID IN (#pgmid#)

Sorry about that, I just have a few things on my mind and they distract me..

WHERE CONVERT(SMALLDATETIME, DateBegin, 103) BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#

That gets rid of the errors but returns no results.
I did this to find out what this CONVERT was doing:

SELECT  CONVERT(SMALLDATETIME, DateBegin, 103)

and I get exactly what I had before without using the convert: 2004-06-21 08:51:00

I thought this was supposed to convert it to a format without hh:mm:ss included.

Hi 451ls,

how about this ... this may / may not work just give it a try...

WHERE #dateFormat(CreateODBCDate(DateBegin), "dd/mm/yy")#BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#


Regards,
---Pinal

Can't think so made a quick hack

WHERE CAST(DAY(GETDATE()) AS VARCHAR(2)) + '/' + CAST(MONTH(GETDATE()) AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS CHAR(4)) BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#

when I try the CreateODBC(DateBegin) approach.

Variable DATEBEGIN is undefined.
 
7 : WHERE #dateFormat(CreateODBCDate(DateBegin), "dd/mm/yy")# BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#
8 : AND SF.ProgramID IN (#pgmid#)

Let's see if there is a way to see what the convert does.

I entered this in SQL Query Analyzer: SELECT  CONVERT(SMALLDATETIME, DateBegin, 103)
but the output is always the same, regardless of the style.

Does SQL Query Analyzer not display the way it is converted?

Tacobell777 - this problem of getting a date to a yy-mm-dd format that can be compared
to other date values can't be that hard. I don't want to construct the entire string as casted
charactrers.

nick

OK OK here we go

WHERE CONVERT(VARCHAR(8), DateBegin, 1) BETWEEN #dateFormat(form.beginsurvey, "dd/mm/yy")# AND #dateFormat(form.endsurvey, "dd/mm/yy")#

duh, my brain...

Hi there

try this:

WHERE DateBegin BETWEEN <cfqueryparam value="#form.beginsurvey#" cfsqltype="CF_SQL_DATE"> AND <cfqueryparam value="#form.endsurvey#" cfsqltype="CF_SQL_DATE">

Mause

Martin's answer is exactly what I was going to post.  Use the 101 not 102.