Data types in SQL Server 2012 and HTML forms with PHP: date and monetary values

Question

How can I put a mask in an HTML form, in the date and monetary value fields, then send this data to the database without having to convert the strings into types date and money?

Explain:

I have a form with date mask in the format DD/MM/AAAA, however, my database (BD) SQL Server 2012 uses the format AAAA-MM-DD. When I submit the form for PHP to insert in the BD, the data is sent in the form of string (e.g.: '31/10/2012'), so I need to make a conversion:

$data = '31/10/2012';
$data_formatada = str_replace('/', '-', $data);
echo date('Y-m-d', strtotime($data_formatada));

The other case is money type data.

The form mask is 9.999.999.999.999,99 and the database has the format 999999999999.99. When I submit the PHP pro form to do the insertion, I have to delete the dots and replace the semicolon by dot only to insert the data in the database afterwards.

Question: Is there a more efficient way to enter date and monetary values in the database than the ones I described?

Answer 1

Yes there is a more practical way to do this, use specialized functions for this, do not treat the situation as a text formatting problem.

For dates use the Datetime class, it has the method createFromFormat() which receives a date in the desired format and returns a Datetime object, use the method format() to change the date format of d/m/Y for Y-m-d

$data =  DateTime::createFromFormat('d/m/Y', '31/12/2015');
echo $data->format('Y-m-d');

For monetary values use the internationalization library Intl is available since php5.3, enable it by php.ini. parseCurrency(), receives a string in currency format and removes all formatting. formatCurrency() does the opposite process from a number returns a string with the applied currency value.

$arr=array('R$530.077,99','R$31.459,89','R$2.899,39','R$600,51','R$13,00','R$9,00','R$0,25');
$formatter = new NumberFormatter('pt_BR',  NumberFormatter::CURRENCY);
foreach($arr as $item){
    echo  $formatter->parseCurrency($item, $valor_puro) . '<br>';
}

Answer 2

SQL Server has no format, it stores data, all databases work like this, format is something the application should use to present the data. It can even be the application and use a format when making a query, but storage is something else. If you look there in the file you will see that it is not even this format that you think it has. Obviously if you make a query it will present in some way more readable by a human and this is a standard format, so there is already a conversion, and you will need to do another whenever you want a specific format. There’s no way out of this.

PHP has a few options and you can use whatever is most convenient. But you don’t have to worry about efficiency. What has to be done needs to be done. The cost is low and micro-optimization is meaningless, especially in PHP.

Answer 3

Using HTML5, but no IE support:

<input type="tel" required="required" maxlength="15" 
name="valor" pattern="([0-9]{1,3}\.)?[0-9]{1,3},[0-9]{2}$" />