12.SQL SERVER - DATE FUNCTIONS
Date functions are functions that help to format dates and carry out date-related calculations on your data. They are usually only effective on data types that are in date format on SQL.
----------------------------------------------------
--
--------------------------- Date Functions -------------------------------
-- GETDATE() -- Retrive todays date
--SYSDATETIME --
--CURRENT_TIMESTAMP
--SYSUTCDATETIME -- UTC DATETIME OF SYSTEM
--SYSDATETIMEOFFSET -- CURRENT TIMEZONE DATE STAMP
--datename
--datepart
--day
--month
--year
--dateadd
--datediff
--EOMONTH
--ISDATE
SELECT GETDATE() AS CurrentTime,
SYSDATETIME() as systime,
CURRENT_TIMESTAMP as cstamp,
SYSUTCDATETIME() as utc,
SYSDATETIMEOFFSET() as offset
select SYSTEM_USER -- Log in user
select CURRENT_USER -- db user
-- day functions
SELECT DAY(GETDATE()) AS Day,
month(getdate()) as Month,
year(getdate()) as year
-- DATENAME
--select datename(date_part,yourdate)
--date_part abbreviations
--year yy, yyyy
--quarter qq, q
--month mm, m
--dayofyear dy, y
--day dd, d
--week wk, ww
--weekday dw
--hour hh
--minute mi, n
--second ss, s
--millisecond ms
--microsecond mcs
--nanosecond ns
--TZoffset tz
--ISO_WEEK isowk, isoww
--
select datename(yyyy,getdate()) as year
select datename(yy,getdate()) as year
select datename(q,getdate()) as qtr
select datename(mm,getdate()) as month
select datename(m,getdate()) as month
select datename(dd,getdate()) as month
select datename(d,getdate()) as month
--select datename(DAYOFYEAR()) AS MONTH
select datename(dd,getdate())+'/'+DATEPART(mm,getdate())+
'/'+datename(yyyy,getdate()) as 'DD/MM/YYYY'
-- DATEPART
SELECT DATEPART(year,GETDATE())
SELECT DATEPART(mm,GETDATE())
SELECT DATEPART(Q,GETDATE())
-- difference between daptepart and datetime is return the datatype
-- DATEPART() returns the integer
-- DATENAME() RETURNS THE CHAR/VARHCAR
SELECT GETDATE()
-- https://tableplus.com/blog/2018/09/sq...
-- CUSTOM DATE FUNCTIONS I.E (DD/MM/YYYY OR MM/DD/YYY OR MM, YYY)
-- CONVERT (VARCHAR,GETDATE(),101 OR 1) 101 TO 120 AND 1 TO 20
SELECT CONVERT(VARCHAR,GETDATE(),101)
SELECT CONVERT(VARCHAR,GETDATE(),102)
SELECT CONVERT(VARCHAR,GETDATE(),111)
SELECT CONVERT(VARCHAR,GETDATE(),112)
SELECT CONVERT(VARCHAR,GETDATE(),4)
SELECT CONVERT(VARCHAR,GETDATE(),1)
-- DATEADD (Used to adds a value to the DATE gives you new format of the date)
--syntax : DATEADD(DATE_PART,VALUE,YOURDATE/INPUT_DATE)
SELECT DATEADD(D,5,'2023-02-27')
SELECT DATEADD(DAY,5,'2023-02-27')
SELECT DATEADD(M,5,'2023-02-27')
SELECT DATEADD(y,1,'2023-02-27')
SELECT DATEADD(DY,1,'2023-02-27')
select top 100 * from [Sales].[SalesOrderDetail]
select salesorderid,
ModifiedDate,
DATEADD(MM,2,ModifiedDate) AS NDATE,
CONVERT(VARCHAR,ModifiedDate,112) 'DATEKEY',
CONVERT(VARCHAR,ModifiedDate,4) AS [KEY],
EOMONTH('2024-02-01') AS EOM
from [Sales].[SalesOrderDetail]
-- DATEDIFF Calculate difference between the date ranges
-- syntax: DATEDIFF( date_part , start_date , end_date)
DECLARE @start_dt datetime=getdate()-250,
@end_dt datetime=getdate()
select @start_dt,@end_dt
SELECT DATEDIFF(year, @start_dt, @end_dt) diff_in_year,
DATEDIFF(quarter, @start_dt, @end_dt) diff_in_quarter,
DATEDIFF(month, @start_dt, @end_dt) diff_in_month,
DATEDIFF(dayofyear, @start_dt, @end_dt) diff_in_dayofyear,
DATEDIFF(day, @start_dt, @end_dt) diff_in_day,
DATEDIFF(week, @start_dt, @end_dt) diff_in_week,
DATEDIFF(hour, @start_dt, @end_dt) diff_in_hour,
DATEDIFF(minute, @start_dt, @end_dt) diff_in_minute,
DATEDIFF(second, @start_dt, @end_dt) diff_in_second,
DATEDIFF(millisecond, @start_dt, @end_dt) diff_in_millisecond
SELECT ISDATE(GETDATE())
select salesorderid,
ModifiedDate,
ISDATE(MODIFIEDDATE) AS DATECHECK,
ISDATE('AAS') AS CH,
DATEADD(MM,2,ModifiedDate) AS NDATE,
CONVERT(VARCHAR,ModifiedDate,112) 'DATEKEY',
CONVERT(VARCHAR,ModifiedDate,4) AS [KEY],
EOMONTH('2024-02-01') AS EOM
from [Sales].[SalesOrderDetail]
---------------------------------------------------------------------------------------------------------
#sqlserver #dbms #database #data #date #rdbms #dataanalytics