dongpu2476
dongpu2476
2013-08-02 18:27

在一天中的时间走开

已采纳

it is such that I need to find out the sql about that "" should not be there that day., and I also find out eventually if I set it to 21:00 so 1.21 must be gone!

right now it works with date, but right now it seems the time.

this does not work

SELECT title, Dato, Tidspunkt, antal 
FROM tilmeldt 
WHERE Dato >= CURDATE() Tidspunkt > TIME() 
ORDER BY Dato DESC

This works 100%

SELECT title, Dato, Tidspunkt, antal 
FROM tilmeldt 
WHERE Dato >= CURDATE() 
ORDER BY Dato DESC

The problem is: when it has come to the time of day so it must get away right now, nothing happens.

    CREATE TABLE IF NOT EXISTS `tilmeldt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) COLLATE utf8_danish_ci NOT NULL,
  `info` longtext COLLATE utf8_danish_ci NOT NULL,
  `Dato` date NOT NULL,
  `Tidspunkt` time NOT NULL,
  `antal` int(3) NOT NULL,
  `opret_navn` varchar(200) COLLATE utf8_danish_ci NOT NULL,
  `opret_email` varchar(250) COLLATE utf8_danish_ci NOT NULL,
  `opret_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci AUTO_INCREMENT=3 ;

--
-- Data dump for tabellen `tilmeldt`
--

INSERT INTO `tilmeldt` (`id`, `title`, `info`, `Dato`, `Tidspunkt`, `antal`, `opret_navn`, `opret_email`, `opret_id`) VALUES
(1, 'Hello World', '<p>ehhehehe</p>', '2013-08-02', '16:00:00', 0, 'Jesper', 'jp@hey.dk', 1),
(2, 'herherh', '<p>hererh</p>', '2013-08-02', '21:00:00', 1, 'Jesper', 'jp@hey.dk', 1);
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • dt102282 dt102282 8年前

    That's because you are missing logical operator either AND or OR in between the conditions in the where clause. Try the following (I'm assuming AND here):

    SELECT title, Dato, Tidspunkt, antal 
    FROM tilmeldt 
    WHERE Dato >= CURDATE() AND Tidspunkt > TIME(NOW()) ORDER BY Dato DESC
    

    Update:

    Also you were missing argument to time() function. See here: https://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_time.

    Alternatively you can use current_time() function like follows:

    SELECT title, Dato, Tidspunkt, antal 
    FROM tilmeldt 
    WHERE Dato >= CURDATE() AND Tidspunkt > CURRENT_TIME() ORDER BY Dato DESC
    

    Select code used in fiddle (formatted time in result):

    SELECT title, Dato, time_format('%H-%i-%s', Tidspunkt) as Tidspunkt, antal 
    FROM tilmeldt 
    WHERE Dato >= CURDATE() AND Tidspunkt > CURRENT_TIME() ORDER BY Dato DESC
    
    点赞 评论 复制链接分享

为你推荐