Problem with Date and Year

Databases
1

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.

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

-- 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');
2

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

:slight_smile:

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

:slight_smile:

3

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?:

[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') 
)
4

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 :slight_smile:

5

Ok, I understand your suggestion, many thanks:


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`;