In SQL Server, the CAST and CONVERT functions are used to change an expression from one data type to another. They are particularly useful when handling date and time data types. Here’s how to use both functions for date and time conversions:
1. CAST Function
The CAST function is a standard SQL function that can convert a date and time expression to another data type. Its syntax is:
CAST(expression AS data_type)
Example:
To convert a string to a date:
SELECT CAST('2023-10-15' AS DATE) AS ConvertedDate;
Or, to convert a DATETIME to a VARCHAR:
SELECT CAST(GETDATE() AS VARCHAR(20)) AS ConvertedString;
2. CONVERT Function
The CONVERT function is SQL Server-specific and provides additional formatting options for date and time. Its syntax is:
CONVERT(data_type, expression [, style])
- data_type: The type to which you want to convert.
- expression: The value that you want to convert.
- style (optional): An integer that specifies the format of the output.
Example:
To convert a DATETIME to a VARCHAR with a specific style:
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS ConvertedString; -- 'YYYY-MM-DD'
You can also convert a string to DATETIME using CONVERT:
SELECT CONVERT(DATETIME, '2023-10-15', 120) AS ConvertedDate;
Different Style Options
The style parameter in the CONVERT function allows for various formats. Here are some common styles for converting DATETIME:
- 1 or 101: USA format (MM/DD/YYYY)
- 3 or 103: British/French format (DD/MM/YYYY)
- 4 or 104: German format (DD.MM.YYYY)
- 10 or 110: USA format with punctuation (MM-DD-YYYY)
- 120: ODBC canonical (YYYY-MM-DD HH:MI:SS)
- 121: ODBC canonical with milliseconds (YYYY-MM-DD HH:MI:SS.mmm)
Example with Style:
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS USFormat; -- MM/DD/YYYY
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS UKFormat; -- DD/MM/YYYY
Summary
- Use CAST when you need a simple data type conversion without formatting.
- Use CONVERT when you need to format the datetime or when working with specific SQL Server formats.
- Remember to choose the appropriate style for the desired date display format when using CONVERT.
Example in a Full Query
SELECT
CAST('2023-10-15' AS DATE) AS CastExample,
CONVERT(VARCHAR(10), GETDATE(), 101) AS ConvertUSFormat,
CONVERT(VARCHAR(10), GETDATE(), 103) AS ConvertUKFormat;
This will output converted date values in different formats for comparison.