sql query replace NOW() to datetime

Question

I have a request:

SELECT 
  *,
  CASE WHEN NOW() BETWEEN `from` AND `to` THEN "1" ELSE "0" END AS status, 
FROM
  `table`
WHERE
  NOW() > `created_at`

And I would like to replace NOW() to my $date variable (UTC). How can I change my request?

P.S My query little bit changed.

Thanks!

score 2 · Accepted Answer · edited May 23 '17 at 11:58

2

If we're speaking about MySQL, which I think we are based on this syntax, this should work:

SELECT *
FROM `table`
WHERE  $date > `created_at`

Modified from another question, source.

EDIT

New query:

SELECT *,
CASE
WHEN $date BETWEEN `from` AND `to` THEN "1" 
ELSE "0" 
END 
AS status, 
FROM `table`
WHERE
$date > `created_at`

edited May 23 '17 at 11:58

Community

answered Jul 01 '15 at 06:30

A. Abramov

Why did you place "=" instead? – Chiragkumar Thakar Jul 01 '15 at 06:43
and as he also mentioned that $date is in UTC – Chiragkumar Thakar Jul 01 '15 at 06:45
@ChiragThakar I editetd my question, thank you. How does the date being UTC has any influence on this? – A. Abramov Jul 01 '15 at 06:50
UTC is a standard time, and timezone is varying as per the region, so we need to make sure about his 'created_at' is in UTC format, make sense? – Chiragkumar Thakar Jul 01 '15 at 06:54
@ChiragThakar I see, yes, I thought that's oblivious. If he ever comes back saying there's a problem with the format, that'd probably be the mistake. (: – A. Abramov Jul 01 '15 at 06:55
Hello, thanks for the answer - check my updated question please. – XTRUST.ORG Jul 01 '15 at 08:13

1 Answers1