Transact SQL (T-SQL)
When it comes to SQL I tend to lean on the SQL I have used the most over the years, which is Oracle. Today was no exception, I found myself trying to use the TO_CHAR command in SQL Server to format a date, which of course does not work. So, after a little thought, here are some examples of how you can the SQL Server Convert Command the achieve the equivalent result.
Example SQL Server Date Conversion SQL
Example SQL Server Date Conversion SQL Code
This SQL of examples runs, as is, no from table required.
Select
CONVERT(VARCHAR(10), GETDATE(), 20) as
‘YYYY-MM-DD’
,CONVERT(VARCHAR(19), GETDATE(), 20) as ‘YYYY-MM-DD HH24:MI:SS’
,CONVERT(VARCHAR(8), GETDATE(), 112) as YYYYMMDD
,CONVERT(VARCHAR(6), GETDATE(), 112) as YYYYMM
,CONVERT(VARCHAR(12), DATEPART(YEAR, GETDATE()))+ RIGHT(‘0’+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2)
as
YYYYMM_Method_2
,CONVERT(VARCHAR(4), GETDATE(), 12) as YYMM
,CONVERT(VARCHAR(4), GETDATE(), 112) as YYYY
,CONVERT(VARCHAR(4), DATEPART(YEAR, GETDATE())) as YYYY_Method_2
,CONVERT(VARCHAR(4), YEAR(GETDATE())) as YYYY_Method_3
,RIGHT(‘0’+CAST(MONTH(GETDATE()) AS VARCHAR(2)),2) as Two_Digit_Month
,SUBSTRING(ltrim(CONVERT(VARCHAR(4), GETDATE(), 12)),3,2) as Two_Digit_Month_2
,CONVERT(VARCHAR(10), GETDATE(), 111) as ‘YYYY/MM/DD’
,CONVERT(VARCHAR(5), GETDATE(), 8) as ‘HH24:MI’
Map TO_CHAR formats to SQL Server
You can map an Oracle TO_CHAR formats to SQL Server alternative commands as follows:
TO_CHAR |
VARCHAR |
SQL |
YYYY-MM-DD |
VARCHAR(10) |
20, |
YYYY-MM-DD |
VARCHAR(19) |
20, |
YYYYMMDD |
VARCHAR(8) |
112 |
YYYYMM |
VARCHAR(6) |
112 |
YYMM |
VARCHAR(4) |
12 |
YYYY |
VARCHAR(4) |
112 |
MM |
VARCHAR(2) |
12 |
YYYY/MM/DD |
VARCHAR(10) |
111 |
HH24:MI |
VARCHAR(5) |
8, |
HH24:MI:SS |
VARCHAR(8) |
8, |
Translating the formats commands
Here are some example of translating the formats commands.
Format |
SQL |
YYYY-MM-DD |
CONVERT(VARCHAR(10), |
YYYY-MM-DD |
CONVERT(VARCHAR(19), |
YYYYMMDD |
CONVERT(VARCHAR(8), |
YYYYMM |
CONVERT(VARCHAR(6), |
YYMM |
CONVERT(VARCHAR(4), |
YYYY |
CONVERT(VARCHAR(4), |
YYYY |
CONVERT(VARCHAR(4), |
YYYY |
CONVERT(VARCHAR(4), |
MM |
RIGHT(‘0’+CAST(MONTH(GETDATE()) |
MM |
SUBSTRING(ltrim(CONVERT(VARCHAR(4), |
YYYY/MM/DD |
CONVERT(VARCHAR(10), |
HH24:MI |
CONVERT(VARCHAR(5), |
HH24:MI:SS |
CONVERT(VARCHAR(8), |
Related Reference
- SQL server table Describe (DESC) equivalent
- Microsoft SQL Server – Useful links
- SQL Server – Basic SQL Server Object Naming Convention Guidance