sql cast datetime to date code example

Example 1: sql datetime as date

SELECT 
    CONVERT(DATE, GETDATE()) date;

Example 2: sql to date

-- Oracle:TO_DATE(string, format)
SELECT TO_DATE('2012-06-05', 'YYYY-MM-DD') FROM dual;
SELECT TO_DATE('05/06/2012 13:25:12', 'DD/MM/YYYY HH24:MI:SS') FROM dual;
-- SQL Server: CONVERT(data_type, string, style). Cf source link for style codes.
SELECT CONVERT(DATETIME, '2012-06-05', 102);		-- Raises error if impossible
SELECT TRY_CONVERT(DATETIME, '2012-06-05', 102);	-- Returns Null if impossible
-- MySQL: STR_TO_DATE(string, format): 
SELECT STR_TO_DATE('2012-06-05','%Y-%m,%d');

Example 3: convertir datetime a date

declare @fecha datetime 

set @fecha = GETDATE()

select convert(date ,@fecha)

Example 4: sql convert date

Example 
SELECT CONVERT(varchar, '2017-08-25', 101);

CONVERT(data_type(length), expression, style)

Converting datetime to character:
Without century	With century	Input/Output	Standard
0	100	mon dd yyyy hh:miAM/PM	Default
1	101	mm/dd/yyyy	US
2	102	 yyyy.mm.dd	ANSI
3	103	 dd/mm/yyyy	British/French
4	104	dd.mm.yyyy	German
5	105	 dd-mm-yyyy	Italian
6	106	dd mon yyyy	-
7	107	Mon dd, yyyy	-
8	108	hh:mm:ss	-
9	109	mon dd yyyy hh:mi:ss:mmmAM (or PM)	Default + millisec
10	110	mm-dd-yyyy	USA
11	111	 yyyy/mm/dd	Japan
12	112	 yyyymmdd	ISO
13	113	dd mon yyyy hh:mi:ss:mmm	Europe (24 hour clock)>
14	114	hh:mi:ss:mmm	24 hour clock
20	120	yyyy-mm-dd hh:mi:ss	ODBC canonical (24 hour clock)
21	121	yyyy-mm-dd hh:mi:ss.mmm	ODBC canonical (24 hour clock)
 	126	yyyy-mm-ddThh:mi:ss.mmm	ISO8601
 	127	yyyy-mm-ddThh:mi:ss.mmmZ	ISO8601 (with time zone Z)
 	130	dd mon yyyy hh:mi:ss:mmmAM	Hijiri
 	131	dd/mm/yy hh:mi:ss:mmmAM	Hijiri

Example 5: sql cast date

DECLARE @counter INT = 0
DECLARE @date DATETIME = '2006-12-30 00:38:54.840'

CREATE TABLE #dateFormats (dateFormatOption int, dateOutput nvarchar(40))

WHILE (@counter <= 150 )
BEGIN
   BEGIN TRY
      INSERT INTO #dateFormats
      SELECT CONVERT(nvarchar, @counter), CONVERT(nvarchar,@date, @counter) 
      SET @counter = @counter + 1
   END TRY
   BEGIN CATCH;
      SET @counter = @counter + 1
      IF @counter >= 150
      BEGIN
         BREAK
      END
   END CATCH
END

SELECT * FROM #dateFormats

Example 6: sql server format datetime to short date

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