Home > Documentation > Miscellaneous
SQL queries often require conversion of dates to text and backward. This articles explores how to convert varchar to date in SQL of different DBMS.
MySQL offers function STR_TO_DATE to convert varchar to date.
The function has the following syntax:
STR_TO_DATE(string, format)
where the first parameter is the string to be converted into date and the second parameter specifies the format that is combination of the following parts:
%a Weekday short name (Sun, Mon, ...) %b Month short name (Jan, Feb, ...) %d Day of month numeric (1, 2, 3, ...) %f Microseconds (000000 ... 999999) %H Hour (00 ... 23) %h Hour (00 ... 12) %i Minutes (00 ... 59) %j Day of the year (001 ... 366) %M Full month name (January, February, ...) %m Month numeric (01 ... 12) %p AM or PM %r Full time in 12 hour AM/PM (hh:mm:ss AM/PM) %S Seconds (00 ... 59) %T Full time in 24 hour format (hh24:mm:ss) %W Full weekday name (Sunday, Monday, ...) %w Numeric day of week (Sunday=0 and Saturday=6) %Y 4-digit numeric year %y 2-digit numeric year
Example of use: SELECT str_to_date('31/12/2022', '0/%m/%Y')
PostgreSQL provides two function: TO_DATE(text, format)
to convert string to date and TO_TIMESTAMP(text, format)
to convert string to timestamp. Both functions accept two parameters:
the string value to convert to a date and the date/timestamp format.
Format is the combination of the following parts:
YYYY/YY 4-digits / 2-digits year MONTH Uppercase month name (JANUARY, FEBRUARY, ...) Month Capitalized month name (January, February, ...) MON Uppercase month short name (JAN, FEB, ...) Mon Month short name (Jan, Feb, ...) MM Numeric month 01...12 DAY Full uppercase day name (SUNDAY, MONDAY, ...) Day Full capitalized day name (Sunday, Monday, ...) DY Abbreviated uppercase day name (SUN, MON, ...) Dy Abbreviated capitalized day name (Sun, Mon, ...) DDD Numeric day of year 001...366 DD Numeric day of month 01...31 D Numeric day of the week (Sunday=1, ..., Saturday=7) H24 Hours 00...23 H Hours 00...12 MI Minutes 00...59 SS Seconds 00...59
Example of use: SELECT to_timestamp('2022-02-28 15:13:11', 'YYYY-MM-DD HH24:MI:SS')
To convert varchar to date in SQL Server, use CONVERT function that has the following syntax:
CONVERT(datetime, string [, style ])
The first argument is the constant specifying the target type, the second argument is varchar value containing string representation of date and the third argument is the style specification:
# format example -------------------------------------- 1 mm/dd/yy 12/29/21 2 yy.mm.dd 21.12.29 3 dd/mm/yy 29/12/21 4 dd.mm.yy 29.12.21 5 dd-mm-yy 29-12-21 6 dd-Mon-yy 29 Dec 21 7 Mon dd, yy Dec 29, 21 10 mm-dd-yy 12-29-21 11 yy/mm/dd 21/12/29 12 yymmdd 211229 23 yyyy-mm-dd 2021-12-29 31 yyyy-dd-mm 2021-29-12 32 mm-dd-yyyy 12-29-2021 33 mm-yyyy-dd 12-2021-29 34 dd-mm-yyyy 29-12-2021 35 dd-yyyy-mm 29-2021-12 101 mm/dd/yyyy 12/29/2021 102 yyyy.mm.dd 2021.12.29 103 dd/mm/yyyy 29/12/2021 104 dd.mm.yyyy 29.12.2021 105 dd-mm-yyyy 29-12-2021 106 dd Mon yyyy 29 Dec 2021 107 Mon dd, yyyy Dec 29, 2021 110 mm-dd-yyyy 12-29-2021 111 yyyy/mm/dd 2021/12/29 112 yyyymmdd 20211229
Example of use: SELECT CONVERT(datetime, '2022.12.01', 102)
Have questions? Contact us