WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.
COURSE of the MONTH
7 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Extract Data from Start-Month and End-Month within one column
Posted on 2013-01-07
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...
Can you suggest the query for this or any reference for this?
Thanks,
Shail
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
Can you suggest the query for this or any reference for this?
Thanks,
Shail
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2
- +1
7 Comments
Active 3 days ago
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:
the rest should be simple ...
* 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'
the rest should be simple ...
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'
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'
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
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
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.
(CODE)
(CODE)
Then you just need to pass your date to the function.
If i…
Originally, this post was published on Monitis Blog, you can check it
here
.
In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing.
Set up your basic HTML file. Open your form tag and set the method and action attributes.:
(CODE)
Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month7 days, 21 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.