# DATE\_FORMAT

Convert the input datetime object to a string according to the specified format.

## Syntax

```
DATE_FORMAT (dateobj, format)

```

## Arguments

* dateobj: a valid date, datetime, or parsable date string
* format: a string containing format symbols

**Year arguments**

| Symbol | Output                     |
| ------ | -------------------------- |
| %Y     | Year, numeric, four digits |
| %y     | Year, numeric (two digits) |
| %j     | Day of year (001 to 366)   |

**Month arguments**

| Symbol | Output                             |
| ------ | ---------------------------------- |
| %b     | Abbreviated month (Jan, Feb, Mar…) |
| %M     | Month name (January to December)   |
| %m     | Month number, padded (00 to 12)    |
| %c     | Month number (0 to 12)             |

**Day arguments**

| Symbol | Output                                            |
| ------ | ------------------------------------------------- |
| %D     | Day of the month with suffix (0th, 1st, 2nd, etc) |
| %d     | Day of the month, padded (00 to 31)               |
| %e     | Day of the month (0 to 31)                        |

**Hour arguments**

| Name | Description                               |
| ---- | ----------------------------------------- |
| %H   | Hour of day, padded 24h format (00 to 23) |
| %h   | Hour of day, padded 12h format (01 to 12) |
| %I   | (alias for %h)                            |
| %k   | Hour of day, 24h format (0 to 23)         |
| %l   | Hour of day, 12h format (1 to 12)         |

**Minute arguments**

| Symbol | Output                    |
| ------ | ------------------------- |
| %i     | Minute of hour (00 to 59) |

**Second arguments**

| Symbol | Output             |
| ------ | ------------------ |
| %S     | Seconds (00 to 59) |
| %s     | Seconds (00 to 59) |

**Microsecond arguments**

| Symbol | Output                          |
| ------ | ------------------------------- |
| %f     | Microseconds (000000 to 999999) |

**Time arguments**

| Symbol | Output                                        |
| ------ | --------------------------------------------- |
| %p     | AM or PM                                      |
| %r     | Time, 12-hour (hh:mm:ss followed by AM or PM) |
| %T     | Time, 24-hour (hh:mm:ss)                      |

**Week arguments**

| Symbol | Output                                 |
| ------ | -------------------------------------- |
| %U     | Week (00 to 53) starting Sunday        |
| %u     | Week (00 to 53) starting Monday        |
| %V     | Week (01 to 53) starting Sunday        |
| %v     | Week (01 to 53) starting Monday        |
| %X     | Year corresponding to week given by %V |
| %x     | Year corresponding to week given by %v |

**Weekday arguments**

| Symbol | Output                                       |
| ------ | -------------------------------------------- |
| %W     | Weekday name (Sunday to Saturday)            |
| %w     | Day of the week (0=Sunday to 6=Saturday)     |
| %a     | Abbreviated day of week (Sun, Mon, Tue, etc) |

**Escape arguments**

| Symbol | Output                |
| ------ | --------------------- |
| %%     | A literal % character |

## Return Type

`String`

## Examples

```sql
SELECT DATE_FORMAT(NOW(), '%M %D, %Y');

```

```output

+---------------------------------+
| DATE_FORMAT(NOW(), '%M %D, %Y') |
+---------------------------------+
| March 3rd, 2015                 |
+---------------------------------+

```

```sql
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s');

```

```output

+-----------------------------------------+
| DATE_FORMAT(NOW(), '%Y-%m-%d %h:%i:%s') |
+-----------------------------------------+
| 2015-03-03 11:37:07                     |
+-----------------------------------------+
```

```sql
SELECT DATE_FORMAT(NOW(), '%W, the %D day of %M in that remarkable year %Y') AS florid;

```

```output

+------------------------------------------------------------+
| florid                                                     |
+------------------------------------------------------------+
| Tuesday, the 3rd day of March in that remarkable year 2015 |
+------------------------------------------------------------+
```

***

Modified at: May 31, 2023

Source: [/db/v9.1/reference/sql-reference/date-and-time-functions/date-format/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/date-and-time-functions/date-format/)

(An index of the documentation is available at /llms.txt)