When to Use Varchar and Date/Datetime

When to use VARCHAR and DATE/DATETIME

Why not put screws in with a hammer?

Because it isn't the right tool for the job.

Some of the disadvantages of the VARCHAR version:

• You can't easily add / subtract days to the VARCHAR version.
• It is harder to extract just month / year.
• There is nothing stopping you putting non-date data in the VARCHAR column in the database.
• The VARCHAR version is culture specific.
• You can't easily sort the dates.
• It is difficult to change the format if you want to later.
• It is unconventional, which will make it harder for other developers to understand.
• In many environments, using VARCHAR will use more storage space. This may not matter for small amounts of data, but in commercial environments with millions of rows of data this might well make a big difference.

Of course, in your hobby projects you can do what you want. In a professional environment I'd insist on using the right tool for the job.

Comparison between a DATE and a VARCHAR: How does it work?

You have multiple strange things going on. In terms of conversion:

WHERE ORDER_DATE >= TO_DATE(CURRENT_DATE, 'YYYY-MM-DD')
----------------------------^ CURRENT_DATE is converted to a string because TO_DATE() expects a string as the first argument
--------------------^ TO_DATE then converts the string to a date using the specified format
-----------------^ The comparison converts the ORDER_DATE to a date (and you might get a type conversion error).

Note: This is based on the rules of Standard SQL. I assume that Exasol follows these rules (which are consistent across databases).

Presumably, your ORDER_DATE is in the format YYYY-MM-DD. If so, it is comparison safe and you can use:

ORDER_DATE >= TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD')

varchar vs datetime in sql

Change datatype of your column to datetime. You can do your query IF you'll use datetime instead of varchar in where clause:

select *
from test
where timeStamp between convert(datetime, '2013-09-05 18:23:57', 120) and convert(datetime, '2013-09-05 18:23:59', 120)

I'm pretty sure it would work even with implicit cast if you use ISO format of date:

select *
from test
where timeStamp between '2013-09-05 18:23:57' and '2013-09-05 18:23:59'

Here's more info about cast and convert.

MySQL database field is varchar when it should be datetime

What is the best approach for migrating the field?

Possible way:

Step 1. Add generated column which converts string-valued dates to DATE datatype:

ALTER TABLE tablename
  ADD COLUMN dSomeDate DATE AS (STR_TO_DATE(SomeDate, {converting pattern})) STORED;

Step 2. Find all queries which selects from old column (including ones in functions, procedures, triggers, events, ...), rewrite them to new column usage and apply.

Step 3. Find all modifying queries and mark them, rewrite them to new column usage, but not apply.

Step 4. Stop the system. Make complete backup and ensure that it is consistent.

Step 5. Alter table, remove generated column but add regular one. Fill it with converted value with according UPDATE. Remove old column.

Step 6. Apply all rewrited data modifying code.

Step 7. Start the system and ensure that everything works without problems.

Repeat this procedure for each column to be altered. Of course, you may apply this method to a lot of columns (or to all of them) at the same time, but this increases the probability of the problem due to something was not taken into account.

Should I use the datetime or timestamp data type in MySQL?

Timestamps in MySQL are generally used to track changes to records, and are often updated every time the record is changed. If you want to store a specific value you should use a datetime field.

If you meant that you want to decide between using a UNIX timestamp or a native MySQL datetime field, go with the native DATETIME format. You can do calculations within MySQL that way
("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") and it is simple to change the format of the value to a UNIX timestamp ("SELECT UNIX_TIMESTAMP(my_datetime)") when you query the record if you want to operate on it with PHP.

Converting Varchar to Date and date Comparison

You can just convert those 2 varchars to the DATE type, then compare them.

You can find the date/datetime styles here

For those DD/MM/YYYY datestamps the 103 style would fit.

And to calculate the most recent between them, just wrap it in a CASE.

Example snippet:

declare @T table (
  id int identity(1,1) primary key, 
  datestamp1 varchar(10), 
  datestamp2 varchar(10)
);

insert into @T (datestamp1, datestamp2) values 
 ('5/9/2018','17/9/2018')
,('9-10-2018','16-10-2018')
,('15-10-2018','13-10-2018')
;

SELECT *,
TRY_CONVERT(DATE, datestamp1, 103) as date1, 
TRY_CONVERT(DATE, datestamp2, 103) as date2,
CASE 
WHEN TRY_CONVERT(DATE, datestamp1, 103) >= TRY_CONVERT(DATE, datestamp2, 103) THEN datestamp1
WHEN TRY_CONVERT(DATE, datestamp2, 103) IS NULL THEN datestamp1
ELSE datestamp2
END AS MostRecentDatestamp
FROM @T;

Returns:

id  datestamp1  datestamp2  date1       date2       MostRecentDatestamp
1   5/9/2018    17/9/2018   2018-09-05  2018-09-17  17/9/2018
2   9-10-2018   16-10-2018  2018-10-09  2018-10-16  16-10-2018
3   15-10-2018  13-10-2018  2018-10-15  2018-10-13  15-10-2018

Converting varchar date field to datetime datatype

Provided that all your data is in the format yyyy-MM-ddThh:mm:ss.nnnnnnn then you can just change the data type of the column:

ALTER TABLE dbo.YourTABLE ALTER COLUMN YourColumn datetime2(7);

If you need the timezone in there, then use datetimeoffset(7) instead.

What difference between the DATE, TIME, DATETIME, and TIMESTAMP Types

DATE: It is used for values with a date part but no time part. MySQL retrieves and displays DATE values in YYYY-MM-DD format. The supported range is 1000-01-01 to 9999-12-31.

DATETIME: It is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in YYYY-MM-DD HH:MM:SS format. The supported range is 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

TIMESTAMP: It is also used for values that contain both date and time parts, and includes the time zone. TIMESTAMP has a range of 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC.

TIME: Its values are in HH:MM:SS format (or HHH:MM:SS format for large hours values). TIME values may range from -838:59:59 to 838:59:59. The hours part may be so large because the TIME type can be used not only to represent a time of day (which must be less than 24 hours), but also elapsed time or a time interval between two events (which may be much greater than 24 hours, or even negative).

---

Related Topics