regular expression problem

converting date formats

[shaan1980]

now i receive the date format from my form like"1-12-2004"

obviously in my database i have my formats aws "01-DEC-04"

how do i convert my form to the database format using regular expressions
Thanks

[VectorJ]

You cat either use the Date:Manip module [search.cpan.org ], or if you really want to use a regex, you could do something like this:

first populate an array with the month abbreviations:

$month[1] = 'JAN'; 
$month[2] = 'FEB'; 
$month[3] = 'MAR'; 
#....and so on.... 

then do the substitution:

$date =~ m/ (\d+)-(\d+)-(\d{2}(\d{2})/; 
$day = $1; 
$month_num = $2; 
$year = $3; 
if (length($day)<2) { 
 $day = '0' . $day; 
} 
$new_date = $day . '-' . $month[$month_num] . '-' . $year; 

$new_date now should hold the date in the format you want.

[coopster]

Going a step further, you may want to change the way you collect dates in a form. You might consider getting the month, day and year separately. Then concatenate them in the format you want.

$date = $year . '-' . $month . '-' . $day;

I realize you aren't asking for this, but I would highly recommend storing dates in your tables in the

DATE

format provided by the database you are using. For example, MySQL

DATETIME

,

DATE

, and

TIMESTAMP

values can be specified using any of a common set of formats, such as 'YYYY-MM-DD'. This is going to allow you to use the database's built-in

DATE

functions down the road.

Substringing might be another option. As a matter of fact, if the database you are using has some feature-rich string functions, you could do it all in your SQL statement. An example, using MySQL:

SELECT 
 UPPER(CONCAT( 
 DATE_FORMAT( 
  CONCAT( 
   SUBSTRING_INDEX('1-12-2004','-',-1), 
   '-', 
   SUBSTRING_INDEX(SUBSTRING_INDEX('1-12-2004','-',2),'-',-1), 
   '-', 
   SUBSTRING_INDEX('1-12-2004','-',1) 
  ), 
  '%d-%b-%y' 
 ) 
 )) 
;

Just wanted to throw some other ideas at you today :)

[shaan1980]

hey there VectorJ
The regular expression u gave didn't work
what is $date here is this an arbitrary variabe or is it the string that we get from the web interface

[coopster]

There is an extra parenthesis in there. I'm guessing VectorJ may have been going toward the option of allowing for a two digit or four digit year to be entered:

/(\d{1,2})-(\d{1,2})-(\d{2})(\d{2})?/ 
... 
$year = ($4) ? $4 : $3;

[VectorJ]

Sorry 'bout that. It actually should have been:

$date =~ m/ (\d+)-(\d+)-(\d{2})(\d{2})?/;  
$day = $1;  
$month_num = $2;  
$year = $+;  

And yeah, $date is just an arbitrary variable name, holding whatever was in the web form.

[shaan1980]

got it thanks ...