Date and Time Function

2023-07-19 20:49:17.540
YYYY-MM-dd HH:MI:SS.ms 

-----------------------Date parameters
Year-   YY,YYYY
Quarter QQ,Q
Month   MM,M
Day     DD,D
Week WW,WK
WeekDay DW,W
DayOFYear DY,Y

-----------------------Time parameters
hour hh
minute mi, n
second ss, s
millisecond ms
microsecond mcs
nanosecond ns
DateAdd
DateDiff
DatePart
DateName

-----------------------DateAdd()-----------------------
select Getdate() --get current date and time.

--2023-07-19 20:53:54.760

----DateAdd function

Add year,month,quarter(90 days),days, week(7 days) into date.

Syntax

Select DateAdd(Date / time parameter, value to add , Date)

Select DateAdd(YY,1,Getdate())

Select DateAdd(YYYY,1,Getdate())

Select DateAdd(Year,1,Getdate())

---2024-07-23 14:24:01.403

---Quarter --add 90 Day into Date

Select DateAdd(QUARTER,1,Getdate())

Select DateAdd(QQ,1,Getdate())

Select DateAdd(Q,1,Getdate())

---2023-10-23 14:26:29.377

Select DateAdd(MONTH,1,Getdate())

Select DateAdd(MM,1,Getdate())

Select DateAdd(M,1,Getdate())

2023-08-23 14:29:55.797

Select DateAdd(DAY,1,Getdate())

Select DateAdd(DD,1,Getdate())

Select DateAdd(D,1,Getdate())

2023-07-24 14:30:43.950

Select DateAdd(WEEK,1,Getdate())

Select DateAdd(WW ,1,Getdate())

Select DateAdd(WK,1,Getdate())

2023-07-30 14:31:17.210

--------------------------DateDiff------------------------

--Difference between 2 dates based on parameter - in below example- Day.

Select DateDiff(DAY,'2017-03-30 14:52:35.613',Getdate())

--2306 postive Result

--------------------------DateName------------------------

select DateName(Month,GETDATE())

select DateName(MM,GETDATE())

select DateName(M,GETDATE())

---July

select DateName(YEAR,GETDATE())

select DateName(YY,GETDATE())

select DateName(YYYY,GETDATE())

--2023

select DateName(QUARTER,GETDATE())

select DateName(QQ,GETDATE())

select DateName(Q,GETDATE())

--3

select DateName(Day,GETDATE())

select DateName(DD,GETDATE())

select DateName(D,GETDATE())

--23

select DateName(WEEK,GETDATE())

select DateName(WW,GETDATE())

select DateName(WK,GETDATE())

--30

select DateName(WEEKDAY,GETDATE())

select DateName(DW,GETDATE())

select DateName(W,GETDATE())

--Sunday

select DateName(DayOFYear,GETDATE())

select DateName(DY,GETDATE())

select DateName(Y,GETDATE())

--204

--------------------------DatePart------------------------

select DatePart(YEAR,GETDATE())

select DatePart(YY,GETDATE())

select DatePart(YYYY,GETDATE())

--2023

select DatePart(QUARTER,GETDATE())

select DatePart(QQ,GETDATE())

select DatePart(Q,GETDATE())

--3

select DatePart(Month,GETDATE())

select DatePart(MM,GETDATE())

select DatePart(M,GETDATE())

--7

select DatePart(Day,GETDATE())

select DatePart(DD,GETDATE())

select DatePart(D,GETDATE())

--23

select DatePart(WEEK,GETDATE())

select DatePart(WW,GETDATE())

select DatePart(WK,GETDATE())

--30

select DatePart(WEEKDAY,GETDATE())

select DatePart(DW,GETDATE())

select DatePart(W,GETDATE())

--1

select DatePart(DayOFYear,GETDATE())

select DatePart(DY,GETDATE())

select DatePart(Y,GETDATE())

--204

-----------------------------------------------

select Isdate(getdate())--1

select Day(getdate())   --23

select Month(getdate()) --7

select Year(getdate())  --2023

-----------------------------------------------

select Getdate()

--2023-07-23 14:41:33.787

select GetUTCDate()

--2023-07-23 09:11:44.143

select SYSUTCDATETIME()

--2023-07-23 09:11:51.7428843

--The difference between GETDATE() and GETUTCDATE() is time zone number of the SQL Server machine.

--GETUTCDATE () can be used to store the timestamp that is independent of Time Zones. 

select Getdate()-GetUTCDate()-------------1900-01-01 05:30:00.000

select Current_TimeStamp

--O/p same as getdate()

--2023-07-23 14:42:15.580

select SYSDATETIME() 

--2023-07-23 14:42:36.9746876

select SYSDATETIMEOFFSET() 

--2023-07-23 14:42:57.0469087 +05:30

select SYSUTCDATETIME()

--2023-07-23 09:13:05.8327637

----------------------------------------Date key Table

How to generate Date key Table table.

CREATE TABLE [dbo].[DateKeyTable](

[ID] [bigint] IDENTITY(1,1) NOT NULL,

[Date] [datetime] NOT NULL,

[DateKeyValue] [varchar](10) primary key,

[Year] [int] NULL,

[Quarter] [int] NULL,

[MonthNo] [int] NULL,

[DayNoMonth] [int] NULL,

[Year_Name] [varchar](5) NULL,

[Quarter_Name] [varchar](10) NULL,

[Month_Name] [varchar](10) NULL,

[Date_Name] [varchar](15) NULL,

[Status] [bit] NULL DEFAULT ((0))

) 

Select * from DailyMilkCollection

--2023-12-04 00:00:00.000

--20231204

Select 365*5

Query to Insert Data-

Declare @var bigint

Declare @date DateTime

set @var=1

set @date='2020-01-01'

while (@var<=1827)

Begin

print @date

--print Convert(varchar(10),@date,112)

--print Year(@date)

--print Datepart(Q,@date)

--print Month(@date)

--print 'Q-'+DateName(Q,@date)+' '+DateName(year,@date)

--print Substring(DateName(month,@date),1,3)+' '+DateName(year,@date)

--print DateName(d,@date)+' '+Substring(DateName(month,@date),1,3)+' '+DateName(year,@date)

insert into DateKeyTable 

([Date],[DateKeyValue],[Year],[Quarter],[MonthNo],[DayNoMonth],[Year_Name],[Quarter_Name],[Month_Name],[Date_Name])

values

(

 @date,

 Convert(varchar(10),@date,112),

 Year(@date),

 Datepart(Q,@date),

 Month(@date),

 Day(@date),

 Convert(varchar(5),Year(@date)),

 'Q-'+DateName(Q,@date)+' '+DateName(year,@date),

 Substring(DateName(month,@date),1,3)+' '+DateName(year,@date),

 DateName(d,@date)+' '+Substring(DateName(month,@date),1,3)+' '+DateName(year,@date)

 )

 

set @date=@date+1

set @var=@var+1

End

Select * from [DateKeyTable]