sql cast date

1-- NOTE: this is for SQL-Oracle specifically
2
3/*
4<...> : Your personal entry
5*/
6
7-- syntax:
8CAST(<variable> as <variable-type>) -- in this case: <variable-type> = INTEGER
9
10-- example:
11SELECT CAST(MEMBER_NO as INTEGER)
12FROM DUAL;
13

1Example 
2SELECT CONVERT(varchar, '2017-08-25', 101);
3
4CONVERT(data_type(length), expression, style)
5
6Converting datetime to character:
7Without century	With century	Input/Output	Standard
80	100	mon dd yyyy hh:miAM/PM	Default
91	101	mm/dd/yyyy	US
102	102	 yyyy.mm.dd	ANSI
113	103	 dd/mm/yyyy	British/French
124	104	dd.mm.yyyy	German
135	105	 dd-mm-yyyy	Italian
146	106	dd mon yyyy	-
157	107	Mon dd, yyyy	-
168	108	hh:mm:ss	-
179	109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default + millisec
1810	110	mm-dd-yyyy	USA
1911	111	 yyyy/mm/dd	Japan
2012	112	 yyyymmdd	ISO
2113	113	dd mon yyyy hh:mi:ss:mmm	Europe (24 hour clock)>
2214	114	hh:mi:ss:mmm	24 hour clock
2320	120	yyyy-mm-dd hh:mi:ss	ODBC canonical (24 hour clock)
2421	121	yyyy-mm-dd hh:mi:ss.mmm	ODBC canonical (24 hour clock)
25 	126	yyyy-mm-ddThh:mi:ss.mmm	ISO8601
26 	127	yyyy-mm-ddThh:mi:ss.mmmZ	ISO8601 (with time zone Z)
27 	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
28 	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri
29

1CAST('2006-04-25T15:50:59.997' AS date) AS Date,  
2
3CAST('2006-04-25T15:50:59.997' AS time) AS Time,  
4
5CAST('2006-04-25T15:50:59.997' AS datetime) AS Datetime, 

1DECLARE @counter INT = 0
2DECLARE @date DATETIME = '2006-12-30 00:38:54.840'
3
4CREATE TABLE #dateFormats (dateFormatOption int, dateOutput nvarchar(40))
5
6WHILE (@counter <= 150 )
7BEGIN
8   BEGIN TRY
9      INSERT INTO #dateFormats
10      SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) 
11      SET @counter = @counter + 1
12   END TRY
13   BEGIN CATCH;
14      SET @counter = @counter + 1
15      IF @counter >= 150
16      BEGIN
17         BREAK
18      END
19   END CATCH
20END
21
22SELECT * FROM #dateFormats