CONVERT

Description

Convert an expression of one data type to another. (CONVERT in Transact-SQL).

Sample Source Pattern

Syntax

SQL Server

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )  

Snowflake SQL

Snowflake SQL Documentation

CAST( <source_expr> AS <target_data_type> )

Examples

SQL Server

IN -> SqlServer_01.sql

SELECT CONVERT(INT, '1998') as MyDate

Result

MyDate |
-------+
1998   |

Snowflake SQL

OUT -> SqlServer_01.sql

SELECT
CAST('1998' AS INT) as MyDate;

Result

MYDATE |
-------+
1998   |

Casting date type to varchar

SQL Server

IN -> SqlServer_02.sql

SELECT CONVERT(varchar, getdate(), 1) AS RESULT;

Result

RESULT   |
---------+
12/08/22 |

Swowflake SQL

OUT -> SqlServer_02.sql

SELECT
TO_VARCHAR(CURRENT_TIMESTAMP() :: TIMESTAMP, 'mm/dd/yy') AS RESULT;

Result

RESULT   |
---------+
12/08/22 |

Casting date type to varchar with size

SQL Server

IN -> SqlServer_03.sql

SELECT CONVERT(varchar(2), getdate(), 1) AS RESULT;

Result

RESULT   |
---------+
07       |

Snowflake SQL

OUT -> SqlServer_03.sql

SELECT
LEFT(TO_VARCHAR(CURRENT_TIMESTAMP() :: TIMESTAMP, 'mm/dd/yy'), 2) AS RESULT;

Result

RESULT   |
---------+
07       |

The supported formats for dates casts are:

Date formats

Code	Format
1	mm/dd/yy
2	yy.mm.dd
3	dd/mm/yy
4	dd.mm.yy
5	dd-mm-yy
6	dd-Mon-yy
7	Mon dd, yy
10	mm-dd-yy
11	yy/mm/dd
12	yymmdd
23	yyyy-mm-dd
101	mm/dd/yyyy
102	yyyy.mm.dd
103	dd/mm/yyyy
104	dd.mm.yyyy
105	dd-mm-yyyy
106	dd Mon yyyy
107	Mon dd, yyyy
110	mm-dd-yyyy
111	yyyy/mm/dd
112	yyyymmdd

Time formats

Code	Format
8	hh:mm:ss
14	hh:mm:ss:ff3
24	hh:mm:ss
108	hh:mm:ss
114	hh:mm:ss:ff3

Date and time formats

0	Mon dd yyyy hh:mm AM/PM
9	Mon dd yyyy hh:mm:ss:ff3 AM/PM
13	dd Mon yyyy hh:mm:ss:ff3 AM/PM
20	yyyy-mm-dd hh:mm:ss
21	yyyy-mm-dd hh:mm:ss:ff3
22	mm/dd/yy hh:mm:ss AM/PM
25	yyyy-mm-dd hh:mm:ss:ff3
100	Mon dd yyyy hh:mm AM/PM
109	Mon dd yyyy hh:mm:ss:ff3 AM/PM
113	dd Mon yyyy hh:mm:ss:ff3
120	yyyy-mm-dd hh:mm:ss
121	yyyy-mm-dd hh:mm:ss:ff3
126	yyyy-mm-dd T hh:mm:ss:ff3
127	yyyy-mm-dd T hh:mm:ss:ff3

Islamic calendar dates

Code	Format
130	dd mmm yyyy hh:mi:ss:ff3 AM/PM
131	dd mmm yyyy hh:mi:ss:ff3 AM/PM

If there is no pattern matching with the current code, it will be formatted to yyyy-mm-dd hh:mm:ss

Related Ewis

No related EWIs.