Home of the ColdFusion Community
|
Mailing Lists
|
Home /
Groups /
ColdFusion Talk (CF-Talk)
SQL between dates problem
Author: Dawson, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316107
"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
Author: Will Tomlinson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316103
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
Author: Will Tomlinson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316088
>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
Author: Will Tomlinson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316076
>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
Author: Dawson, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316069
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))#
Author: John M Bliss
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316066
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 -----
Author: Dawson, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316065
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
Author: Will Tomlinson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316064
>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
Author: Will Tomlinson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316063
>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
Author: Dawson, Michael
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316062
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
Author: Duane Boudreau
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316061
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
Author: Will Tomlinson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316059
>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
Author: John M Bliss
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316057
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 -----
Author: Will Tomlinson
Short Link: http://www.houseoffusion.com/groups/cf-talk/thread.cfm/threadid:58204#316056
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
|
May 24, 2012
|
Latest Fusion Authority Articles
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
