SQL Date Formats
SQL supports various date and time data types and formats. The specific date format depends on the database system you are using.
Here are some standard date and time data types and formats in SQL:
- DATE: YYYY-MM-DD
- TIME: HH:MI:SS
- DATETIME: YYYY-MM-DD HH:MI:SS
- TIMESTAMP: YYYY-MM-DD HH:MI:SS
- INTERVAL: This data type stores a duration or interval of time in various formats.
You can use the FORMAT() function to format a date. Here is an example:
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy')
In this example, GETDATE() retrieves the current date and time, and the format string ‘MM/DD/yyyy’ specifies the desired date format.
Here are some date format codes in the format string:
- yyyy: Year with century (e.g., 2023)
- yy: Year without century (e.g., 23)
- MM: Month as a two-digit number (e.g., 02 for February)
- MMM: Abbreviated month name (e.g., Feb)
- MMMM: Full month name (e.g., February)
- dd: Day of the month as a two-digit number (e.g., 03)
- ddd: Abbreviated day name (e.g., Tue)
- dddd: Full day name (e.g., Tuesday)
You can use these codes to create custom date formats that suit your needs. It’s also important to remember that formatting the date can affect the sorting and filtering of the data, so it’s essential to use a consistent format throughout your queries.