Web Analytics Made Easy -
StatCounter Date formatting - CodingForum

Announcement

Collapse
No announcement yet.

Date formatting

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Date formatting

    Hi there i was wondering whether anyone could help me with a problem i am having retrieve data out of one databases.

    The problem that i am having is that i have a field called logdate which is within table opencall. This logdate field is set as a string (dd/mm/yy hh:mm:ss).
    I am wanting to extract certain dates of information, for example the previous months information, but at the moment the script i have is producing false data. I have realised because the logdate is a string and the current date variable is a date that this may be the problem so i have made the logdate into a unix timestamp as you can see in the script below but i am still getting problems. It is not bringing back any syntax errors, and it is not rpoducing any information which i am sure it should be.

    Select suppgroup, UNIX_TIMESTAMP(concat(substring(logdate,7,4),"-",substring(logdate,4,2),"-",substring(logdate,1,2),substring(logdate,11,9))), count(*) Number
    From opencall
    where logdate between UNIX_TIMESTAMP((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-1)*100)+1) and UNIX_TIMESTAMP((PERIOD_ADD(EXTRACT(YEAR_MONTH FROM CURDATE()),-0)*100)+1)
    and probcode not like 'CHNG%'
    Group by suppgroup ;

    Any help would be appreciated.
Working...
X