CONVERT(data_ type(<length>), expression, <style>)
The length option is only used on the following data types: nchar, char, varchar, nvarchar, varbinary, and binary.
SQL Server interprets two digit years as follows:
if the year is less than 49 then the year is considered to be 20XX
while anything 50 and over is considered 19XX.
12> SELECT
13> CAST('11/11/72' as smalldatetime) AS '11/11/72',
14> CAST('6/5/40' as smalldatetime) as '6/5/40'
15> GO
11/11/72 6/5/40
-------------------- --------------------
1972-11-11 00:00:00 2040-06-05 00:00:00
(1 rows affected)
1> SELECT
2> CONVERT(varchar, getdate(), 1),
3> CONVERT(varchar, getdate(), 2),
4> CONVERT(varchar, getdate(), 3),
5> CONVERT(varchar, getdate(), 4),
6> CONVERT(varchar, getdate(), 5),
7> CONVERT(varchar, getdate(), 6)
8> GO
------------------------------ ------------------------------ ------------------------------ ---------------------------
--- ------------------------------ ------------------------------
10/21/07 07.10.21 21/10/07 21.10.07
21-10-07 21 Oct 07
(1 rows affected)
13.3.CONVERT |
| 13.3.1. | CONVERT function syntax |
| 13.3.2. | Values for the style argument of the CONVERT function when you convert a datetime expression to a character expression. |
| 13.3.3. | select CONVERT (INTEGER , '123') |
| 13.3.4. | The syntax of the CONVERT function CONVERT(data_type, expression [, style]) |
| 13.3.5. | CONVERT(varchar,BillingDate,100) 'Mmm dd yyyy hh:mmdp' |
| 13.3.6. | CONVERT(varchar,BillingDate,101) 'mm/dd/yyyy' |
| 13.3.7. | CONVERT(varchar,BillingDate,103) 'dd/mm/yyyy' |
| 13.3.8. | CONVERT() function requires two arguments: the first for the target data type and the second for the source value. |
| 13.3.9. | SELECT 'Default Date: ' + CONVERT(VarChar(50), GETDATE(), 100) |
| 13.3.10. | SELECT 'US Date: ' + CONVERT(VarChar(50), GETDATE(), 101) |
| 13.3.11. | SELECT 'ANSI Date: ' + CONVERT(VarChar(50), GETDATE(), 102) |
| 13.3.12. | SELECT 'UK/French Date: ' + CONVERT(VarChar(50), GETDATE(), 103) |
| 13.3.13. | SELECT 'German Date: ' + CONVERT(VarChar(50), GETDATE(), 104) |
| 13.3.14. | SELECT CONVERT(VarChar(50), @Num, 0) |
| 13.3.15. | CONVERT(datetime, '20000704') |
| 13.3.16. | SELECT CONVERT(datetime, '10.12.99',1) |
| 13.3.17. | SELECT CONVERT(datetime, '10.12.99',4) |
| 13.3.18. | CONVERT(char(10), GETDATE(), 112) |
| 13.3.19. | CONVERT(varchar(10), advance, 2) |
| 13.3.20. | CONVERT(varchar(20), GETDATE()) |
| 13.3.21. | The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. |
| 13.3.22. | CONVERT(char(8), BillingDate, 1) and CONVERT(varchar(9), PaymentTotal, 1) |
| 13.3.23. | Conversion failed when converting the varchar value 'abc' to data type int. |
| 13.3.24. | CONVERT(varchar(12), OrderDate, 5) |
| 13.3.25. | Compare date type value after converting |
| 13.3.26. | Use convert function with variables |