Cast, convert, datediff, and dateadd functions in SQL Server

1. Cast

Cast (data as data_type)

 

Example: declare @ data int

Set @ DATA = 5

Select cast (@ data as char (2) + 'abc'

 

-- Result: 5 ABC

 

2. Convert

Convert (data_type, expresses (, style) -- style is the style of the specified date

• Select convert (varchar, getdate (), 120)
  -- 15:10:02
• Select Replace (replace (convert (varchar, getdate (), 120 ),'-',''),'',''),':','')
  -- 20090315151201
• Select convert (varchar (12), getdate (), 111)
  -- 2009/03/15
• Select convert (varchar (12), getdate (), 112)
  -- 20090315
• Select convert (varchar (12), getdate (), 102)
  -- 2009.03.15
• Select convert (varchar (12), getdate (), 108)
  -- 15:13:26
• Other date format conversion methods that are not commonly used:
• Select convert (varchar (12), getdate (), 101)
  -- 03/15/2009
• Select convert (varchar (12), getdate (), 103)
  -- 15/03/2009
• Select convert (varchar (12), getdate (), 104)
  -- 15.03.2009
• Select convert (varchar (12), getdate (), 105)
  -- 15-03-2009
• Select convert (varchar (12), getdate (), 106)
  -- 15 03 2009
• Select convert (varchar (12), getdate (), 107)
  -- 15,200 9
• Select convert (varchar (12), getdate (), 109)
  -- 03 15 2009
• Select convert (varchar (12), getdate (), 110)
  -- 03-15-2009
• Select convert (varchar (11), getdate (), 113)
  -- 15 03 2009
• Select convert (varchar (12), getdate (), 114)
  -- 15: 15: 55: 717

3. datediff

Datediff (datepart, startdate, enddate)

Parameter: datepart list

 

Date	Abbreviations
Year	YY, yyyy
Quarter	QQ, Q
Month	Mm, m
Dayofyear	Dy, y
Day	DD, d
Week	Wk, WW
Hour	HH
Minute	Mi, n
Second	SS, S
Millisecond	MS

 

 

4. dateadd (datepart, number, date)

View the first day of the week set by the system -- select @ datefirst

 

Examples of combining datediff and dateadd:

-- Query the first day of the current month

Select dateadd (mm, datediff (mm, 0, getdate (), 0)

 

-- Query Monday of the week

Select dateadd (wk, datediff (wk, 0, getdate (), 0)

 

-- Query the first day of a year

Select dateadd (YY, datediff (YY, 0, getdate (), 0)

 

-- Query the first day of a quarter

Select dateadd (QQ, datediff (QQ, 0, getdate (), 0)

 

-- Query the last day of the last month (equivalent to the first day of the month minus one day)

Select dateadd (day,-1, dateadd (mm, datediff (mm, 0, getdate (), 0 ))

 

-- Query the last day of last year

Select dateadd (day,-1, dateadd (YY, datediff (YY, 0, getdate (), 0 ))

 

-- Query the last day of the month
Select dateadd (day,-1, dateadd (mm, datediff (mm, 0, getdate () + 1, 0 ))

 

-- Query the last day of the year

Select dateadd (day,-1, dateadd (YY, datediff (YY, 0, getdate () + 1, 0 ))

 

 

-- Query the first Monday of the month

Another function is required here.

Datepart (datepart, date)-- Return the integer part of the specified type of the specified date.

Select dateadd (wk, datediff (wk, 0, dateadd (DD, 6-datepart (day, getdate (), getdate (), 0)