Quarterly date comparison

[ven.ganeva]

Hi,

 

I have a database with a field inside it which stores a quarter number and year like this "Q1 2002". I need to keep this format as the client imports a .csv file regularly which has this kind of data in it.

 

What I am trying to do is a write a query that will display all the data according to a date range that a user selects from a set of drop downs. For example,

        SELECT quarter, units
FROM tbl_data_quarterly 
WHERE quarter >= 'Q1 2004' AND quarter <= 'Q3 2007')
ORDER BY quarter

 

Any ideas on how I can manipulate the field to work in this way?

 

I have tried splitting the field into 2 so have quarter in 1 and year in another, but then how can I say something like

WHERE quarter and year >= 'Q1 2004' AND quarter and year <= 'Q3 2007')

 

I also need to order the results by quarter and year... I just have no idea how I can get these fields to work like a date field???

[Barand]

"2007 Q3" format would be better.

 

<?php
$ar = array (
  '2006 Q3',
  '2007 Q2',
  '2006 Q2',
  '2006 Q4',
  '2007 Q1',
  '2006 Q1',
);

sort ($ar);

echo '<pre>', print_r($ar, true), '</pre>';  
?>

// -->

Array
(
    [0] => 2006 Q1
    [1] => 2006 Q2
    [2] => 2006 Q3
    [3] => 2006 Q4
    [4] => 2007 Q1
    [5] => 2007 Q2
)

[fenway]

Ugh... mysql has a period function, you should store your dates properly.

[Hooker]

Ive gotto agre with fenway here, i see it all the time in work when a client sends in data i have to spend hours sorting it out due to bad logic on the part of the client.

 

Give storing your dates properly some serious thought (sooner rather than later!), it'l save you alot of hastle in the long run.