SQL between dates problem

SQL server 2000. I have two columns - startdate and enddate, why would this not return a record if the startdate and enddate are the same. ex. 12/01/2008 for both. AND GETDATE() >= couponstartdate and GETDATE() <= couponenddate Is there a better way to write this? I need it to return a record. It also seems to not pull the record unless the enddate is one day after the day I want it to expire. Thanks, Will

Because GETDATE() will return the current date *and time* and I'll bet that your start/end dates are coming back as 12/1/2008 12:00 AM. ----- Excess quoted text cut - see Original Post for more -----

>Because GETDATE() will return the current date *and time* and I'll bet >that your start/end dates are coming back as 12/1/2008 12:00 AM. > Ahhh yes! That makes sense. Thanks John! Will

If you are using MS SQL... SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) This will remove any time part from a datetime field. Mike >Because GETDATE() will return the current date *and time* and I'll bet >that your start/end dates are coming back as 12/1/2008 12:00 AM. > Ahhh yes! That makes sense. Thanks John! Will

>If you are using MS SQL... > >SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) > >This will remove any time part from a datetime field. > So then I write it like this? AND datetime >= couponstartdate and datetime <= couponenddate Thanks! Will

I would do something like: WHERE datetime BETWEEN CAST(FLOOR(CAST(couponstartdate AS float)) AS datetime) AND CAST(FLOOR(CAST(couponenddate AS float)) AS datetime) >If you are using MS SQL... > >SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) > >This will remove any time part from a datetime field. > So then I write it like this? AND datetime >= couponstartdate and datetime <= couponenddate Thanks! Will

Another CFMLish option: couponstartdate <= #CreateODBCDateTime(CreateDateTime(DatePart("yyyy", Now()), DatePart("m", Now()), DatePart("d", Now()), 23, 59, 59))# and couponenddate >= #CreateODBCDateTime(CreateDateTime(DatePart("yyyy", Now()), DatePart("m", Now()), DatePart("d", Now()), 0, 0, 1))# ----- Excess quoted text cut - see Original Post for more -----

Although this will work perfectly, if provided with CF variables. However, I think Will's dates were already in the database where he would need to stick to the database's date functions. In other words, he wasn't in a situation where he was passing in CF variables to his SQL statement. Mike Another CFMLish option: couponstartdate <= #CreateODBCDateTime(CreateDateTime(DatePart("yyyy", Now()), DatePart("m", Now()), DatePart("d", Now()), 23, 59, 59))# and couponenddate >= #CreateODBCDateTime(CreateDateTime(DatePart("yyyy", Now()), DatePart("m", Now()), DatePart("d", Now()), 0, 0, 1))#

>I would do something like: > >WHERE datetime BETWEEN CAST(FLOOR(CAST(couponstartdate AS float)) AS >datetime) >AND CAST(FLOOR(CAST(couponenddate AS float)) AS datetime) > I'm remote right now, but will give it a whirl tonight. Thanks for your help! Will

>If you are using MS SQL... > >SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) > >This will remove any time part from a datetime field. I'm getting an error from the SQL. I've played around with it with no luck. SELECT couponpercdisc, CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) FROM tblCoupons WHERE couponcode = <cfqueryparam value="#arguments.couponcode#" cfsqltype="cf_sql_varchar"> AND datetime BETWEEN CAST(FLOOR(CAST(couponstartdate AS float)) AS datetime) AND CAST(FLOOR(CAST(couponenddate AS float)) AS datetime) [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'datetime' Will

"datetime" would be your variable or another column to which you would compare the coupon start/end dates. Mike >If you are using MS SQL... > >SELECT CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) > >This will remove any time part from a datetime field. I'm getting an error from the SQL. I've played around with it with no luck. SELECT couponpercdisc, CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime) FROM tblCoupons WHERE couponcode = <cfqueryparam value="#arguments.couponcode#" cfsqltype="cf_sql_varchar"> AND datetime BETWEEN CAST(FLOOR(CAST(couponstartdate AS float)) AS datetime) AND CAST(FLOOR(CAST(couponenddate AS float)) AS datetime) [Macromedia][SQLServer JDBC Driver][SQLServer]Invalid column name 'datetime' Will

Try this: AND GETDATE() >= '#couponstartdate# 00:00:00' and GETDATE() <= '#couponenddate# 23:59:59' SQL server 2000. I have two columns - startdate and enddate, why would this not return a record if the startdate and enddate are the same. ex. 12/01/2008 for both. AND GETDATE() >= couponstartdate and GETDATE() <= couponenddate Is there a better way to write this? I need it to return a record. It also seems to not pull the record unless the enddate is one day after the day I want it to expire. Thanks, Will

>Try this: > >AND GETDATE() >= '#couponstartdate# 00:00:00' and GETDATE() <= >'#couponenddate# 23:59:59' > Except the values I want to match them against are the value in a column - not a couponstartdate or couponenddate variable. Thanks! Will

Screw it. I'm just telling my client to enter the day after as the expiration date of the coupon. It works. But one thing I noticed and tested is, you can insert a date like so into SQL server and it accepts it and converts it to the right format. 12/01/2008 10:37 PM So I could have two inputs for this. A datepicker, and a timepicker, stick them together, and it'd work. Thanks for everyone's input! Will