Select data by date

ctrohaya · #1 03-Jan-2007, 22:24

hi all, i have a simple system that include function of selecting data from dbase by month. in my table date was set as format Y-m-d
so now, how can i write query to select only by month.. is it possible if i write query as

PHP Code:


SELECT * FROM terima WHERE date_in = '%%%%-o1-%%'

i've run it but didnt work

admin · #2 11-Jan-2007, 01:23

When you are trying to search by MONTH, shouldn't you care about the YEAR too?

I mean, if you had 2 records for January, but one was for 2006 and the other for 2007, how would that work?

If the date_in column is of the date type, then I suppose one way to do this would be something like:

Code:

SELECT * FROM `terima` WHERE '2007'=YEAR(`date_in`) AND '1'=MONTH(`date_in`)

Sorry ctrohaya,

I don't know how you managed to get online to post this question since I am also using TMNet and we both know how ridiculous the connection has been recently. Today is good though... so I am catching up.

ctrohaya · #3 12-Jan-2007, 19:04

thanks a lot for giving reply to my question, actually it was settled. it is true of using month and year. i just ask about month, it mean if i got the solution for month, then i'll also got solution about year.

now, may i ask you on how to convert seconds to date(Y-m-d)?
i already know how to convert date to seconds, but how to re-convert seconds to date.
i want to know what is the date if the seconds is 2592000?

admin · #4 12-Jan-2007, 19:23

When you say "seconds", do you mean the unix timestamp? The number of seconds since Jan. 1, 1970?

If so, like this:

PHP Code:


<?php
$uts = 2592000;
echo "Y-m-d is ", date( "Y-m-d", $uts );

// Result:
// Y-m-d is 1970-01-31
?>

You put the "seconds" in the second parameter of the date() function.

ctrohaya · #5 12-Jan-2007, 20:54

it already looks close to my question. the number of seconds since current date.
for ex. if customer enter 30days, we will find the date 30 days backward from current date.

admin · #6 12-Jan-2007, 21:29

I will try one last time. You should really put some effort into your questions as well.

We discussed strtotime() before, so refer to the thread if you need to know how to get a unix timestamp relative to a date or the system's date.

As for your question, see if this is what you were expecting:

PHP Code:


<?php
$days = 30; // 30 days ago

// get the unix timestamp, 30 days ago.
$uts_offset = strtotime( "-$days days" );

// format the timestamp into 'Y-m-d'
echo "$days days ago was ", date( "Y-m-d", $uts_offset );

// Result:
// 30 days ago was 2006-12-14
?>

ctrohaya · #7 13-Jan-2007, 01:15

actually i've make my own effort based on your first post of how to convert second to date, this is my own script

PHP Code:


$period = 30 days ago
$today = date ("Y-m-d");
$second = $period *86400;
if (!$using_timestamps) {
$date_2 = strtotime($today, 0);
$date_1 = $date_2 - $second; //second
$tarikh = date( "Y-m-d", $date_1);
echo $tarikh; 
}

and i'm so happy..... this script is work!
thank you so much for helping me find out the solution.......

admin · #8 13-Jan-2007, 01:56

Many lines of unnecessary code and calculations. You can do it all with just date() and strtotime(), like this:

PHP Code:


<?php
$period = 30; // 30 days ago

if( !$using_timestamps )
{
    $tarikh = date( 'Y-m-d', strtotime("-$period days") );
    echo $tarikh;
}
?>

ctrohaya · #9 17-Jan-2007, 18:53

i have one more situation using date.
if i issue certain date for ex 2007-01-14 then i want to select from database the data that have same that and also date before such given date, that mean all data dated less than 14 will be shown($date<=2007-01-14)
is it possible if i use this script..

PHP Code:


SELECT * FROM terima WHERE  date_in LESS THAN '2007-01-14'

???

admin · #10 18-Jan-2007, 00:26

Try:

Code:

SELECT * FROM `terima` WHERE  `date_in`<='2007-01-14'

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
[Include] Doubly-linked List	dsmith	C Programming Language	6	14-Apr-2006 13:12
Strange C++ code memory leakage problem	gaoanyu	C++ Forum	7	04-Nov-2005 08:09
Mrs	stacy12	C Programming Language	14	05-Feb-2005 18:02
[GIM] gim.h	dsmith	C Programming Language	0	18-Jan-2005 08:48
[CONTEST?]Data Structure Test	dsmith	C Programming Language	2	06-Jun-2004 15:13

Select data by date

Re: select data by date

Re: select data by date

Re: select data by date

Re: select data by date

Calculate date, 30 days ago

Re: Calculate date, 30 days ago

Re: Calculate date, 30 days ago

Re: Select data by date

Re: Select data by date