Welcome to WebmasterWorld Guest from 54.167.85.221

Forum Moderators: coopster & jatar k & phranque

Message Too Old, No Replies

Setting Date

     

textex

6:27 pm on Oct 12, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



AND created_at < DATE_SUB(CURDATE(), INTERVAL 2 DAY)

I am trying to email today's data and all future. But using the above is sending an email to members from the db creating date. Any input?

janharders

6:36 pm on Oct 12, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



use >, if you mean "greater than", use < only if you mean "small than".

textex

7:38 pm on Oct 12, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



What about for a specific start date of 10-01-11? What would the command be?

rocknbil

6:13 pm on Oct 13, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



What's between greater and lesser? Equal! :-)

A note though, curdate() and now() are internal mySQL functions. To execute a select with a static value it must be quoted.

AND created_at = '2011-13-11'

or

AND created_at = "2011-13-11"

You can also use

>=
<=

Both of these work for not equal

!=
<>

textex

6:28 pm on Oct 13, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I will give these a try. Thanks!

textex

7:51 pm on Oct 13, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



I am still getting an error. What am I doing wrong?
$emails_to_send = $dbh->selectall_arrayref(
qq(
SELECT $cols
FROM apps
WHERE is_dup = 0
AND e3 IS NULL
AND AND created_at >= "2011-11-01", INTERVAL 2 DAY
ORDER BY id
LIMIT 5
),
{ Slice => {} },
);

janharders

9:23 pm on Oct 13, 2011 (gmt 0)

WebmasterWorld Senior Member 5+ Year Member



You are obviously trying to use date functions but you're not telling mysql which function you want to use.

[dev.mysql.com...]
has them all and their meanings. You'll probably want DATE_SUB().

also, "AND AND" will give you errors. and " is not the quote char for mysql, it's '
Use placeholders:
my $sth = $dbh->prepare("SELECT * FROM table WHERE field = ?");
$sth->execute( $fieldvalue );

and ? will magically be replaced by $fieldvalue and DBI will take care of escaping special chars and quote it correctly.

rocknbil

4:19 pm on Oct 14, 2011 (gmt 0)

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Huh? You can use double quotes, if it doesn't conflict with content(? Just tested that). Single quotes are a decent habit to form though, because one approach to coding (in Perl or PHP) is encapsulate select statements in double quotes to allow interpolation of the variables.

$query = "select * from table where the_date='$my_date'";

More correction examples . . .

AND created_at >= date_add('2011-11-01', INTERVAL 2 DAY)

or

AND created_at >= date_sub('2011-11-01', INTERVAL 2 DAY)

or for an exact match on the day, you don't use the functions.

AND created_at >= '2011-11-01'
AND created_at <= '2011-11-01'
AND created_at = '2011-11-01'
AND created_at <> '2011-11-01'

coopster

6:27 pm on Oct 14, 2011 (gmt 0)

WebmasterWorld Administrator coopster is a WebmasterWorld Top Contributor of All Time 10+ Year Member



Double quotes are a MySQL extension to the ANSI standard. I run my MySQL server in ANSI mode so if you try to use double quotes they will be interpreted as an identifier. I agree though, it's good practice to keep with single quotation marks.

References for further reading:
[dev.mysql.com...]
[dev.mysql.com...]

textex

7:04 pm on Oct 14, 2011 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



This works:
AND created_at >='2011-10-01'
AND created_at < DATE_SUB(NOW(), INTERVAL 2 DAY)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month