Date Type Cast & Convert : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

Format : https://docs.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver15

-- MSSQL날짜 검색시 문자열로 검색해도 date타입 컬럼 검색이 된다.

SELECT SYSDATETIME()
      ,SYSDATETIMEOFFSET()
      ,SYSUTCDATETIME()
      ,CURRENT_TIMESTAMP
      ,GETDATE()
      ,GETUTCDATE();


SELECT  CAST('2017-01-01'   AS DATE)
,       CAST('20170101'     AS DATE)
,       CAST('2017-01-01'   AS DATETIME)
,       CAST('20170101'     AS DATETIME)

,       CONVERT(DATE        , '2017-01-01')
,       CONVERT(DATE        , '20170101')
,       CONVERT(DATETIME    , '2017-01-01')
,       CONVERT(DATETIME    , '20170101')
;

2017-01-01
2017-01-01
2017-01-01 00:00:00.000
2017-01-01 00:00:00.000

2017-01-01
2017-01-01
2017-01-01 00:00:00.000
2017-01-01 00:00:00.000

날짜포멧변환

SELECT  GETDATE()                                   AS RAW_DATETIME

,       CONVERT(NVARCHAR, GETDATE(), 20)            AS "YYYY-MM-DD HH:MI:SS"        -- 120
,       CONVERT(NVARCHAR, GETDATE(), 21)            AS "YYYY-MM-DD HH:MI:SS.mmm"    -- 121
,       CONVERT(NVARCHAR, GETDATE(), 23)            AS "YYYY-MM-DD"
,       CONVERT(NVARCHAR, GETDATE(), 8)             AS "HH:MI:SS"

,       CONVERT(NVARCHAR, GETDATE(), 112)           AS "YYYYMMDD"

,       YEAR(GETDATE())                             AS "YEAR"
,       MONTH(GETDATE())                            AS "MONTH"
,       DAY(GETDATE())                              AS "DAY"
,       DATENAME(DW, GETDATE())                     AS "DATENAME"

,       DATEPART(YEAR   , GETDATE())                AS YYYY
,       DATEPART(MONTH  , GETDATE())                AS MM
,       DATEPART(DAY    , GETDATE())                AS DD
,       DATEPART(HOUR   , GETDATE())                AS HH
,       DATEPART(MINUTE , GETDATE())                AS MI
,       DATEPART(SECOND , GETDATE())                AS SS
,       DATEPART(DW     , GETDATE())                AS DayOfWeek -- (1:Sunday ~ 7:Saturday)

,       FORMAT(GETDATE(), N'yyyy-MM-dd HH:mm:ss')   AS "FORMATE_DATETIME"
;


Name                   |Value                  |
-----------------------|-----------------------|
RAW_DATETIME           |2020-10-29 14:01:58    |
YYYY-MM-DD HH:MI:SS    |2020-10-29 14:01:58    |
YYYY-MM-DD HH:MI:SS.mmm|2020-10-29 14:01:58.983|
YYYY-MM-DD             |2020-10-29             |
HH:MI:SS               |14:01:58               |
YYYYMMDD               |20201029               |
YEAR                   |2020                   |
MONTH                  |10                     |
DAY                    |29                     |
DATENAME               |Thursday               |
YYYY                   |2020                   |
MM                     |10                     |
DD                     |29                     |
HH                     |14                     |
MI                     |1                      |
SS                     |58                     |
DayOfWeek              |5                      |
FORMATE_DATETIME       |2020-10-29 14:01:58    |