how to sort date?

homer.favenir · #1 (**permalink**) 05-22-08, 04:31

hi to all,
i cant sort my date
can anyone please check my query.

Code:

SELECT
concat_ws('-',YEAR(tbl_transactions.dtestarted),MONTH(tbl_transactions.dtestarted),DAY(tbl_transactions.dtestarted)) as 'Date',
tbl_transactions.startedby,
tbl_user.lastname,
tbl_errordetail.errcode,
tbl_error.errdesc,
count(tbl_errordetail.errcode) as 'error count'


FROM
tbl_errordetail
inner join tbl_error on (tbl_errordetail.errcode = tbl_error.errcode)
inner join tbl_transactions on (tbl_errordetail.transid = tbl_transactions.transid)
inner join tbl_user on (tbl_transactions.startedby = tbl_user.userid)
where tbl_user.lastname = 'barrios'

group by
'date',
tbl_transactions.startedby,
tbl_user.lastname,
tbl_errordetail.errcode,
tbl_error.errdesc

order by
'date' desc,
tbl_user.lastname

my date is sorted but as a string

e.g.

Code:

'2008-5-9', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 2
'2008-5-9', '07010047', 'Barrios', 'UNF', 'UDAC Not Followed', 1
'2008-5-8', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
'2008-5-8', '07010047', 'Barrios', 'MS', 'Missing Text/Omission', 2
'2008-5-8', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
'2008-5-8', '07010047', 'Barrios', 'UNF', 'UDAC Not Followed', 1
'2008-5-7', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 4
'2008-5-6', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 4
'2008-5-5', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
'2008-5-4', '07010047', 'Barrios', 'ME', 'Mechanical Error', 1
'2008-5-4', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 2
'2008-5-3', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
'2008-5-3', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 6
'2008-5-22', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
'2008-5-22', '07010047', 'Barrios', 'UNF', 'UDAC Not Followed', 1
'2008-5-21', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
'2008-5-21', '07010047', 'Barrios', 'ME', 'Mechanical Error', 1
'2008-5-21', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
'2008-5-20', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
'2008-5-20', '07010047', 'Barrios', 'ME', 'Mechanical Error', 2
'2008-5-2', '07010047', 'Barrios', 'ME', 'Mechanical Error', 2
'2008-5-17', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
'2008-5-16', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 4
'2008-5-15', '07010047', 'Barrios', 'IBN', 'Incorrect Business Name', 1
'2008-5-15', '07010047', 'Barrios', 'ME', 'Mechanical Error', 1
'2008-5-15', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1

im searching in the in the internet on converting string to date, or sorting date as numeric, but i cant find any.

thanks

healdem · #2 (**permalink**) 05-22-08, 04:42

so store your date as a DATETIME datatype
always choose the most appropriate datatype for the type of data, unless you are forced by other constraints

so store a date as a date, and integer as one of the integer types....

homer.favenir · #3 (**permalink**) 05-22-08, 05:01

or to sort a string as a numeric?

e.g

Code:

to

Code:

r937 · #4 (**permalink**) 05-22-08, 06:26

would you kindly run this query please:

Code:

SHOW CREATE TABLE tbl_transactions

healdem · #5 (**permalink**) 05-22-08, 06:33

Quote:

Originally Posted by homer.favenir

or to sort a string as a numeric?

e.g

Code:

to

Code:

as said before store numbers as number, dates as dates strings as strings and let the SQL engine do the sorting.
failing that YOU are going to have to do the work so YOU will have to store dates with leading zeros of spaces
eg 5th Feb 2008 is 2008-02-05
1 becomes 01 or 001 or whatever the highest number in your range is#
you can fake the same approach by using leading spaces if YOU MUST go down this route.

the basic problem is that YOU are storing data in the wrong datatype. so either store it in the right datatype or be prepared to do a lot more formatting

mike_bike_kite · #6 (**permalink**) 05-22-08, 06:33

change your order by clause to

Code:

order by tbl_transactions.startedby desc, 
            tbl_user.lastname

or, if the field startedby is not a datetime, then you could use the following:

Code:

order by convert( tbl_transactions.startedby, date ) desc, 
            tbl_user.lastname

but strictly speaking the date fields should be specified as date fields otherwise you'll be continually hitting problems like this. The other advantage of using the right type is that your data will be validated as a correct date before being stored.

When you used order by 'date' desc you're just creating a string with the value 'date' for each row and then you're ordering by this string value. Because the string value is the same for each row ('date') then it won't make any difference to your order by clause. It certainly has nothing to do with the date stored in the database.

It's also better to not use tbl_ for the start of all your tables unless you're prone to forgetting that your selecting your data from tables. Most people use an acronym for the name of the application etc. Of course, if you have lots of views and tables on the same database then there may be some justification for this.

Mike

PS just in case you need to ask questions again then it's probably best to provide the table definition as well as this allows people to see exactly what you're working with.

homer.favenir · #7 (**permalink**) 05-22-08, 06:49

Quote:

Originally Posted by r937

would you kindly run this query please:

Code:

SHOW CREATE TABLE tbl_transactions

Code:

'tbl_transactions', 'CREATE TABLE `tbl_transactions` (
  `Transid` int(10) unsigned NOT NULL auto_increment,
  `ArtID` varchar(45) character set latin1 collate latin1_general_ci default NULL,
  `transcode` varchar(45) character set latin1 collate latin1_general_ci default NULL,
  `dtestarted` datetime default NULL,
  `StartedBy` varchar(10) character set latin1 collate latin1_general_ci default NULL,
  `dteCompleted` datetime default NULL,
  `CompletedBy` varchar(10) character set latin1 collate latin1_general_ci default NULL,
  `CustomerID` varchar(10) character set latin1 collate latin1_general_ci default NULL,
  `prevstatus` varchar(10) character set latin1 collate latin1_general_ci default NULL,
  `RecordID` int(11) default NULL,
  `errorflag` varchar(10) character set latin1 collate latin1_general_ci default NULL,
  PRIMARY KEY  (`Transid`),
  KEY `Index_2` (`ArtID`,`transcode`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1'

r937 · #8 (**permalink**) 05-22-08, 07:00

okay, try this --

Code:

SELECT FROM_DAYS(TO_DAYS(tbl_transactions.dtestarted)) AS date_started
     , tbl_transactions.startedby
     , tbl_user.lastname
     , tbl_errordetail.errcode
     , tbl_error.errdesc
     , COUNT(tbl_errordetail.errcode) AS 'error count'
  FROM tbl_user
INNER
  JOIN tbl_transactions
    ON tbl_transactions.startedby = tbl_user.userid 
INNER
  JOIN tbl_errordetail
    ON tbl_errordetail.transid = tbl_transactions.transid 
INNER
  JOIN tbl_error
    ON tbl_error.errcode = tbl_errordetail.errcode 
 WHERE tbl_user.lastname = 'barrios'
GROUP 
    BY date_started
     , tbl_transactions.startedby
     , tbl_user.lastname
     , tbl_errordetail.errcode
     , tbl_error.errdesc
ORDER 
    BY date_started DESC
     , tbl_user.lastname

healdem · #9 (**permalink**) 05-22-08, 07:39

looking at the original query the problem I see there is formatting the date AND then using that formatted value for the sort order

if you set the sort order to

Code:

tbl_transactions.dtestarted then it should be OK

in place of
ORDER BY date_started DESC
, tbl_user.lastname

incidentally Id suggest not using a reserved word such as DATE in your SQL. ...it may be OK in MySQL but it can cause problems elsewhere

I'd also suggest that you do your date formatting in the presentation layer not the data extraction/manipulation layer

homer.favenir · #10 (**permalink**) 05-22-08, 07:52

thanks guys
and all your suggestions were very helpful,
i will not extract the date to manipulate it.
and also my error in using the reserved DATE in concatenating the extracted date.

problem solved....the best!

r937 · #11 (**permalink**) 05-22-08, 08:00

healdem, my interpretation of the problem is simply that he wanted to group by the date, and not the datetime

tbl_transactions.dtestarted quite clearly is a datetime (it wouldn't make sense for transaction errors to be logged with just the date, ignoring the time)

so your suggestion to replacve my date_started with the original column is wrong

look back at his first attempt to deal with this -- he's trying to construct a date from the date portions of the datetime value

grouping on datetime makes no sense, because you would get one error per group!!

what he wants is to count the errors by day

thus, he needs some way to strip the time off the datetime and group by the resulting date

in SQL Server, the best technique is DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

in MySQL, the FROM_DAYS(TO_DAYS(...)) technique works best