MySQL Cell date type with dash

Started by Piyush, Sep 16, 2022, 01:07 AM

Previous topic - Next topic

PiyushTopic starter

Hey there! I have a question regarding the formatting of MySQL cells for numeric inputs that allow dash or dot separators. Is there a specific format I should use besides Varchar? For instance, if I want to enter a date as 01.09.2022 or 01-09-2022, what numeric format should I use?
  •  

offka

When storing a date, it's recommended to use either the date or datetime field type, or an iso-8601 formatted string. This ensures consistent data storage and allows for easy manipulation and analysis of the data.

If you need to change the format of the date when reading the data, you can do so without any issues. Alternatively, you can store the date in a text field like CHAR or TINYTEXT, but using the DATE type with the desired representation (e.g. 2022-09-01) is generally considered best practice. This can help avoid any complications that may arise from using a text field for something that could be easily represented in a more structured format.
  •  

arreliale

If you want to enter a date in the format 01.09.2022 or 01-09-2022 into a MySQL database, you should use the `DATE` data type instead of a numeric format like `VARCHAR`. The `DATE` data type is specifically designed to store dates and allows you to perform various date-related operations.

To store the date in the format 01.09.2022 or 01-09-2022, you can use the `STR_TO_DATE()` function when inserting or updating the data. For example, to insert the date as 01.09.2022, you can use the following query:

```sql
INSERT INTO your_table (date_column) VALUES (STR_TO_DATE('01.09.2022', '%d.%m.%Y'));
```

And to insert the date as 01-09-2022, you can use the following query:

```sql
INSERT INTO your_table (date_column) VALUES (STR_TO_DATE('01-09-2022', '%d-%m-%Y'));
```

Make sure to adjust the column names and table name according to your specific case.


If you want to store numeric values that allow dash or dot separators, such as decimal numbers, you can use the `DECIMAL` or `FLOAT` data types. These data types are specifically designed to store numeric values with decimal points or separators.

For example, if you want to store a number like 12345.67 or 12,345.67, you can define your column with the `DECIMAL` data type and specify the precision and scale to accommodate the maximum number of digits before and after the decimal point. Here's an example query that creates a table with a `decimal_column` column:

```sql
CREATE TABLE your_table (
    decimal_column DECIMAL(8,2)
);
```

In this example, `8` represents the total number of digits (both before and after the decimal point) that can be stored, and `2` represents the number of digits after the decimal point.

You can then insert values into this column using either a dot or a comma as the decimal separator. For example:

```sql
INSERT INTO your_table (decimal_column) VALUES (12345.67);
INSERT INTO your_table (decimal_column) VALUES (12,345.67);
```

Both values will be stored correctly in the `decimal_column` with the specified precision and scale.

It's important to note that MySQL does not automatically handle regional differences in decimal separators. You need to ensure consistent formatting when inserting and querying decimal values with dot or dash separators.
  •