Formatting Dates and Times in Navicat

Dec 1, 2023 by Robert Gravelle

One of the most common questions Navicat Support receives from users is how to format dates and times in both Grid and form View. It’s actually quite simple! In today’s blog, we’ll go over the steps to change date and time formats globally in Navicat Premium.

You’ll find display formats for dates and times on the Records screen of the Options dialog. It is accessible via the Tools -> Options… command from the main menu:

options_command (43K)

We can see the Date, Time, and DateTime formats in the Display Format section of the Records screen (highlighted with a red border):

date_and_time_display_formats_on_the_records_screen (70K)

Let’s go ahead and update the DateTime format using the Sakila Sample Database as an example. Many of its tables contain a DateTime field called last_update that is used for auditing purposes. We can see it in this screen capture of the actor table (again highlighted with a red border):

last_update_column_in_sakila_actor_table (120K)

By default, Navicat displays dates and times in whatever format they are defined in the database. In the case of MySQL, it displays DateTime values in ‘YYYY-MM-DD hh:mm:ss’ format, for example ‘2019-10-12 14:35:18’ (Notice the use of the 24 hour clock).

Standard SQL and ODBC Date and Time Literals

If you’re unsure of the meaning of the letters in the ‘YYYY-MM-DD hh:mm:ss’ string, those are part of the Standard SQL and ODBC Date and Time Literals. These are standardized ways of representing date and time values in SQL queries. They provide a consistent and platform-independent method for specifying date and time values in SQL statements. Here’s a list of each letter pattern and their meaning. You’ll want to get acquainted with them because Navicat also uses them to set date and time formats:

c – Display the date using the format given by the system global variable, followed
by the time using the format given by the system global variable. The time is not
displayed if the date-time value indicates midnight precisely.
d – Display the day as a number without a leading zero (1-31).
dd – Display the day as a number with a leading zero (01-31).
ddd – Display the day as an abbreviation (Sun-Sat).
dddd – Display the day as a full name (Sunday-Saturday).
ddddd – Display the date using the format given by the system global variable.
dddddd – Display the date using the format given by the system global variable.
m – Display the month as a number without a leading zero (1-12). If the m specifier
immediately follows an h or hh specifier, the minute rather than the month is
displayed.
mm – Display the month as a number with a leading zero (01-12). If the mm specifier
immediately follows an h or hh specifier, the minute rather than the month is
displayed.
mmm – Display the month as an abbreviation (Jan-Dec) using the strings given by the
system global variable.
mmmm – Display the month as a full name (January-December) using the strings given
by the system global variable.
yy – Display the year as a two-digit number (00-99).
yyyy – Display the year as a four-digit number (0000-9999).
h – Display the hour without a leading zero (0-23).
hh – Display the hour with a leading zero (00-23).
n – Display the minute without a leading zero (0-59).
nn – Display the minute with a leading zero (00-59).
s – Display the second without a leading zero (0-59).
ss – Display the second with a leading zero (00-59).
t – Display the time using the format given by the system global variable.
tt – Display the time using the format given by the system global variable.
am/pm – Use the 12-hour clock for the preceding h or hh specifier, and display ‘am’ for
any hour before noon, and ‘pm’ for any hour after noon. The am/pm specifier
can use lower, upper, or mixed case, and the result is displayed accordingly.
a/p – Use the 12-hour clock for the preceding h or hh specifier, and display ‘a’ for any
hour before noon, and ‘p’ for any hour after noon. The a/p specifier can use
lower, upper, or mixed case, and the result is displayed accordingly.
ampm – Use the 12-hour clock for the preceding h or hh specifier, and display the
contents of the system global variable for any hour before noon, and the
contents of the system global variable for any hour after noon.
/ – Date separator. In some locales, other characters may be used to represent the
date separator.
: – Time separator. In some locales, other characters may be used to represent the
time separator.
‘xx’/”xx” – Characters enclosed in single or double quotes are displayed as-is, with no
formatting changes.

Now, let’s change the global Navicat DateTime format to utilize the numeric day without a leading zero, the three letter month abbreviation, and the 12-hour clock with the AM/PM indicator.

Using the above instructions as our guide, that would give us a format string of “mmm d, yyyy hh:mm:ss AM/PM”. We can see the results in real-time in the Output field as we type:

output_field (16K)

After closing the Options dialog via the OK button, all DateTime fields should now be using our custom DateTime format. Here is the last_update column of the actor table mentioned previously:

last_update_column_in_sakila_actor_table_with_new_format (73K)

Remember that the new format will apply globally across all databases. To confirm this, let’s take a look at the orders table from the classicmodels database. It contains three DateTime columns, but only sets the date portion. These columns also display their values according to our new format:

classicmodels_orders_table_with_new_format (146K)

In this blog, we learned how to easily change date and time formats globally in the Options dialog. While we used Navicat Premium here today, note that other Navicat products, such as Navicat for MySQL or Navicat for SQL Server, would work in exactly the same way.