SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Problem with Date and Year

    Hi everyone, I need your help.

    This query not working because the Select Count of year 2011 is 9, but the sql query Count 7 records, why?

    I hope your help, thanks.

    Code:
    SELECT 
    YEAR(STR_TO_DATE(DateOfAlbum,'%a, %d %b %Y %h:%m:%s +0000')) AS YearOfFolder
     , COUNT(*) AS total 
    FROM _tblPics 
    GROUP by 
    YEAR(STR_TO_DATE(DateOfAlbum,'%a, %d %b %Y %h:%m:%s +0000')) 
    ORDER BY YearOfFolder DESC;
    The`_tblPics`
    Code:
    -- phpMyAdmin SQL Dump
    -- version 2.11.6
    -- http://www.phpmyadmin.net
    --
    -- localhost:3306
    -- 10 Jul, 2011 at 10:40 AM
    -- MySQL: 5.0.45
    -- PHP: 5.2.6
    
    SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    
    --
    -- Database: `_DBWrite`
    --
    
    -- --------------------------------------------------------
    
    --
    -- `_tblPics`
    --
    
    CREATE TABLE `_tblPics` (
      `Id` int(11) NOT NULL auto_increment,
      `DateOfAlbum` varchar(255) default NULL,
      PRIMARY KEY  (`Id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=111 ;
    
    --
    -- `_tblPics`
    --
    
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (49, 'Sun, 09 Jan 2011 08:00:00 +0000');
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (50, 'Sun, 23 Jan 2011 08:00:00 +0000');
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (104, 'Mon, 25 Apr 2011 07:00:00 +0000');
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (110, 'Sat, 09 Jul 2011 07:00:00 +0000');
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (108, 'Fri, 06 May 2011 07:54:37 +0000');
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (109, 'Sat, 07 May 2011 20:11:59 +0000');
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (107, 'Fri, 29 Apr 2011 07:00:00 +0000');
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (100, 'Sat, 05 Feb 2011 08:00:00 +0000');
    INSERT INTO `_tblPics` (`Id`, `DateOfAlbum`) VALUES (101, 'Sat, 05 Mar 2011 08:00:00 +0000');
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    because the datetimes for ids 108 and 109 do not convert successfully

    these are the only two datetimes which have non-zero minutes and seconds

    your format string includes %h:%m:%s

    one of those format specifiers is wrong, and it ain't the hours and it ain't the seconds



    p.s. you should really use DATETIME to store your datetimes, not VARCHAR

    format the date on the way out of the database, otherwise in every query like this you have to unformat it to do date functions, which is quite inefficient

    not to mention error prone as you have discovered

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    because the datetimes for ids 108 and 109 do not convert successfully

    these are the only two datetimes which have non-zero minutes and seconds

    your format string includes %h:%m:%s

    one of those format specifiers is wrong, and it ain't the hours and it ain't the seconds



    p.s. you should really use DATETIME to store your datetimes, not VARCHAR

    format the date on the way out of the database, otherwise in every query like this you have to unformat it to do date functions, which is quite inefficient

    not to mention error prone as you have discovered

    Thanks for your explanation.

    Now I add new field in the table `_tblPics`: `STRTODATE` (DateTime) and try this insert query, but I have this error, why?:

    Code:
    [Err] 1411 - Incorrect datetime value: 'Sat, 07 May 2011 20:11:59 +0000' for function str_to_date
    
    INSERT INTO `_tblPics` 
    ( 
      `DateOfAlbum`
    , `STRTODATE` 
    ) 
    VALUES 
    (
    ' Sat, 07 May 2011 20:11:59 +0000'
    , STR_TO_DATE('Sat, 07 May 2011 20:11:59 +0000','%a, %d %b %Y %h:%m:%s +0000') 
    )
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you're still trying to use %h:%m:%s as the time specifiers

    like i said before, there's an error in one of those specifiers, and it ain't the hours and it ain't the seconds

    by the way, there's no need to quote my entire post in order to reply to it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist cms9651's Avatar
    Join Date
    Mar 2010
    Posts
    407
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ok, I understand your suggestion, many thanks:

    Code:
    Output
    record #108		record #109
    2011-05-07 20:11:59	2011-05-06 07:54:37
    
    ======================================
    
    #sql query
    SELECT 
    STR_TO_DATE('Sat, 07 May 2011 20:11:59 +0000','%a, %d %b %Y %H:%i:%s +0000') `record #108`
    , STR_TO_DATE('Fri, 06 May 2011 07:54:37 +0000','%a, %d %b %Y %H:%i:%s +0000') `record #109`;
    Thanks you very much for your help
    I'm really happy for your quickly answer.
    Good bye


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •