Solved

Extract Data from Start-Month and End-Month within one column

Posted on 2013-01-07
7
143 Views
Last Modified: 2015-01-06
Hello experts,

I have an database, where the only column for date is Tabledate. I would like to do the query on this as start-month and end-month. For Example...

TableDate
2012-02-05
2012-04-25
2012-05-15
2012-06-25
2012-09-11
2012-11-02
2012-12-09

Open in new window

If, the user select start-month as march and end-month as december. the database should provide the data related to mentioned date ranging from march to december.

Can you suggest the query for this or any reference for this?
Thanks,
Shail
0
Comment
Question by:ShaileshShinde
7 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 38750109
the question is about how exactly the user will enter the "month" ...
* with year or without year
* with month name or with month number
* what if the "start" month specified is "later" than start month?

what is the data type of that column? I presume date ...
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

if the user specifies january (01) and october (10), the basic query is:
SELECT *
 FROM yourtable
WHERE date_colum >= '2012-01-01'
  and date_colum < '2012-11-01' 

Open in new window


the rest should be simple ...
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 38750340
SELECT *
 FROM yourtable
WHERE date_colum between '2012-01-01' and '2012-10-31'
0
 
LVL 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 50 total points
ID: 38750521
This article will explain it. http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

The question seems to assume that all selections will be in one year.  That may not be the case, for example, I might want to see data from July 2011 through June 2012 (a typical fiscal year for non-profits in USA).

The overall logic goes this way... The client enters the date in any reasonable format and you use strtotime() and date() to convert to ISO-8601 DATETIME strings.  Your query will use the BETWEEN clause, like this.

SELECT columns
FROM myTable
WHERE tabledate BETWEEN '$client_date_low' AND '$client_date_high'
0
 
LVL 1

Author Comment

by:ShaileshShinde
ID: 38750536
Hello Experts,

The month selection is numbers 01 to 12 and this is only for the year Jan 2012 to December 2012. If user select the month 04 as start-month and end-month as 02 then user will be prompted to select end -month greater then start month.

Thanks,
Shail
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 38750552
OK, then the constructed data strings for use in the query will consist of a "date" pattern like this.

2012-XX-01 will be the start date, where XX is the client-selected low month.
2012-YY-t will be the end date, where YY is the client-selected high month.  The "t" is the date() pattern for the last day of the month.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Creating Functions in phpMyAdmin 8 26
mysqli_connect error on wamp but not on remote server 8 37
Help with PHP 13 27
PHP and MSSQL Arrays and Variables 3 23
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question