formatting two dates.

[bazz]

Hi,

Is is better to format two dates in the one query or to use two?

$check_in_date = 2008-05-14
$check_out_date = 2008-05-16

This code formats just one date. How would I begin to make it format two dates.


my $sth = $bookings_db_connect->prepare ("SELECT DATE_FORMAT('$check_in_date', '%W %M %D %Y') ")or die "prepare statement failed: $DBI::errstr\n";


bazz

[oesxyl]

date_format is function in mysql, so you use it when you need and where you need. For example if I want to output from a query two date fields formated I use the function for each field with the format I need.


select date_format(field1, '%W %M %D %Y'),
date_format(field2, '%M %D %Y'),
date_format(field3, '%W %D %Y');


or if I need to compare to fields and also to format one of them to have the same form


select a.field1, b.field2 from a, b where a.field3 = date_format(b.field1, '%W %M %D %Y');


If you want to format a variable in perl I suggest you to use perl and not use a mysql function.
What I mean is that passing the variable to mysql and fetching the results include more useles steps then using a usual perl method.

regards

[bazz]

Thanks for the suggestion. I couldn;t find how to do it in perl, so went for the easier option. :o

I'll look again tomorrow when I am refreshed and see what I can find. 6am finishes for three days are taking their toll.

bazz

[oesxyl]

Thanks for the suggestion. I couldn;t find how to do it in perl, so went for the easier option. :o

I'll look again tomorrow when I am refreshed and see what I can find. 6am finishes for three days are taking their toll.

bazz
you can use Date::Format module and strftime. I see from others post of yours that you use Date modules.
See the Date::Format documentation and/or post if you have some problem, I will try to help.

regards

[bazz]

Thanks oesxyl,

my script receives input like this:

start date: 2008-05-30
number of nights: 4

I need to query a table (tbl_room_rates) which is like this, where I get the rate for each night requested.

rate_id | rate | date_from | date_to |

2008-05-30
2008-05-31
2008-06-01
2008-06-02

here is the query


my $sth = $bookings_db_connect->prepare ("SELECT
RR.rack_rate_id,
RR.room_id,
RD.room_type,
RD.room_number,
RD.room_name,
RD.description_short,
RD.max_occupancy,
RD.image_url
FROM tbl_rooms_details RD, tbl_room_rates RR
WHERE RR.room_id = RD.room_id
AND RR.date_from <= ?
AND RR.date_to >= ?
AND RD.room_id NOT IN ( SELECT
booking_id
FROM tbl_rooms_booked
WHERE check_in_date <= ?
AND check_out_date >= ? )
") or die "prepare statement failed: $DBI::errstr\n";






I needd to change the highlighted part so that I get the rate for each date

If you or anyone can help I would be very grateful.

bazz

[oesxyl]

I'm a little bit confused, I answer to your initial post and seems you change it, but I hope this help, :)

Thanks oesxyl,

I am trying to do things:

my script receives input like this:

start date: 2008-05-30
number of nights: 4

I want to create a range of vars which would represent each of the dates:

2008-05-30
2008-05-31
2008-06-01
2008-06-02

I also have yet to find how to convert a date from 2008-05-01 to May 1st 2008, without using mysql. I don't have Date::ISO and I just can't seem to grasp it from the pages suggested by FishMonger. :(

If you can help I would be very grateful.

I'm off to read them all again.
bazz


#!/usr/bin/perl -wT
use strict;
use warnings;

# build a list of unix timestamp values starting with
# $start_date for next $how_many days
sub date_list {
my ($start_date, $how_many) = @_;
my @outlist;
use Date::Parse;
# parse and convert date to unix time for start_date
my $date = str2time($start_date);
do{
push @outlist, $date;
# seconds/days to avoid loading other modules
$date += 86400;
}while(--$how_many);
return @outlist;
}

# convert a unix timestamp to a string as 'May 1st 2008'
# for example
sub date_conversion {
my $date = shift;
use Date::Format;
return time2str("%B %o %Y",$date);
}

# this is the test part
my @daylist = date_list('2008-05-31',4);
foreach my $day (@daylist){
print date_conversion($day),"<br/>\n";
}


regards

[bazz]

Thank you very much oesxyl, much closer.

However, it oputputs the date like this - 21st May 20081.

I am going now, to loook at where the 1 may be coming from. But have you any ideas?



my @daylist = date_list("$arrival_date_requested",$number_of_nights); # successful
foreach my $day (@daylist){
print date_conversion($day),"<br/>\n"; # incorrect output (check sub).
}




# build a list of unix timestamp values starting with
# $start_date for next $how_many days - from oesxyl (cf).
sub date_list {
my ($start_date, $how_many) = @_;
my @outlist;
use Date::Parse;
# parse and convert date to unix time for start_date
my $date = str2time($start_date);
do{
push @outlist, $date;
# seconds/days to avoid loading other modules
$date += 86400;
}while(--$how_many);
return @outlist;
}

# convert a unix timestamp to a string as 'May 21st 2008' from oesxyl(cf);
# for example
sub date_conversion {
my $day = shift;
print time2str("%B %o %Y",$day);
}



bazz

[oesxyl]

you modify the code I posted, :)


# be sure that $arrival_date_requested and $number_of_nights are valid, you must check, I mean there are something like '2008-05-28' and 10
# - you must remove the quotes from $arrival_date_requested
# - because you call the subs before you declare them you must use & or put
# this code after subs declaration
my @daylist = &date_list($arrival_date_requested,$number_of_nights); # successful
foreach my $day (@daylist){
# here you print the print, therefor the 1 from the end, see date_conversion sub for detailes, :)
print &date_conversion($day),"<br/>\n"; # incorrect output (check sub).
}

# build a list of unix timestamp values starting with
# $start_date for next $how_many days - from oesxyl (cf).
sub date_list {
my ($start_date, $how_many) = @_;
my @outlist;
use Date::Parse;
# parse and convert date to unix time for start_date
my $date = str2time($start_date);
do{
push @outlist, $date;
# seconds/days to avoid loading other modules
$date += 86400;
}while(--$how_many);
return @outlist;
}

# convert a unix timestamp to a string as 'May 21st 2008' from oesxyl(cf);
# for example
sub date_conversion {
my $day = shift;
# time2str is in Date::Format module, you must use it
use Date::Format;
# don't print here, just return the string
return time2str("%B %o %Y",$day);
}



regards

[bazz]

oesxyl, thank you very much.

I had changed the code to print (instead of to return), when I was trying to undertsand it all. I forgot to change it back.

it works as I had hoped so, thank you again.

bazz