SQL Server takes the date part of the time Column

Extract the date part of the DateTime column in the database field:

(1) select Datename (year, GetDate () + '-' + Datename (month, GetDate () + '-' + Datename (day, GetDate ())

 

(2) select convert (varchar (10), getdate (), 120)

 

(3) select * from MyDsk _ reminder where year (reservation time) = '000000' and month (reservation time) = '8' and Day (reservation time) = '29' and reminder ID = '123'

(4) select * from MyDsk _ reminder where convert (varchar (10), reservation time, 120) = '2017-08-29 'and reminder ID = '2016'

Extract the time part of the DateTime column in the database field:

Cast (datepart (hh, reservation time) as varchar)-hour

Cast (datepart (mi, reservation time) as varchar) -- minute

Cast (datepart (ss, reservation time) as varchar) -- seconds

 

[Convert] SQL Server Date and Time Functions

 

1. Common date methods (the following GetDate () = '2017-11-08 13:37:56. 123 ')

(1) DATENAME (datepart, date)

Returns the string that represents the specified date part of the specified date. For details about Datepart, see the following list.

SELECT DateName (day, Getdate ()-returns 8

(2) DATEPART (datepart, date)

Returns an integer that represents the specified date of a specified date.

Select datepart (year, Getdate ()-returns 2006

(3) DATEADD (datepart, number, date)

Returns the new datetime value after the specified date plus a time interval.

Select dateadd (week, 1, GetDate () -- the date after the current date plus one week

(4) DATEDIFF (datepart, startdate, enddate)

Returns the number of date boundary and time boundary that span two specified dates.

Select datediff (month, '2014-10-11 ', '2014-11-01') -- returns 1

(5) DAY (date)

Returns an integer representing the day datepart part of the specified date.

SELECT day (GetDate ()-returns 8

(6) GETDATE ()

Returns the current system date and time in the standard internal format of SQL Server 2005 with datetime value.

SELECT GetDate () -- returns 13:37:56. 233

(7) MONTH (date)

Returns an integer that represents the month of the specified date.

Select month (GETDATE () -- returns 11

(8) YEAR (date)

Returns an integer that represents the year of the specified date.

Select year (GETDATE () -- returns 2006

2. Get a specific date

(1) obtain the current day of the week

SELECT DateName (weekday, Getdate () -- Wednesday

(2) Calculate the day of the week

Select dateadd (week, DATEDIFF (week, '2017-01-01 ', getdate (), '2017-01-01') -- returns 00:00:00. 000
Or
Select dateadd (week, DATEDIFF (week, 0, getdate (), 0)

(3) The first day of the current quarter

Select dateadd (quarter, DATEDIFF (quarter, 0, getdate (), 0)-returns 00:00:00. 000

(4) how to obtain the number of days in a month

SELECT Day (dateadd (MS,-3, DATEADD (mm, DATEDIFF (m, 0, '2017-02-03 ') + 2006)-returns 28

(5) How many days of a quarter

Declare @ m tinyint, @ time smalldatetime

Select @ m = month (getdate ())
Select @ m = case when @ m between 1 and 3 then 1
When @ m between 4 and 6 then 4
When @ m between 7 and 9 then 7
Else 10 end

Select @ time = datename (year, getdate () + '-' + convert (varchar (10), @ m) + '-01'
Select datediff (day, @ time, dateadd (mm, 3, @ time)-return 92

(6) obtain the year, month, and day (yyyy-MM-dd)

Select convert (VARCHAR (10), GETDATE (), 120)-returns

3. Others

(1) -- The following example specifies the date as a number.

The Database Engine interpreted 0 as January 1, 1900.

Select month (0), DAY (0), YEAR (0)-1 1 1900 is returned

-- The following two statements are equivalent.
Select datename (WEEKDAY, 0)
Select datename (WEEKDAY, '2017-01-01 ')

(2) set datefirst {number | @ number_var}

Set the first day of a week to a number ranging from 1 to 7.

Set datefirst 1 -- indicates that the first day of a week is "Monday"
Select datename (WEEKDAY, GETDATE () -- Wednesday
Select datepart (weekday, GETDATE () -- 3 is returned.
-- View Current Settings
Select @ DATEFIRST

(3) set dateformat {format | @ format_var}

Set the order of dates (Months, days, and years) used to input datetime or smalldatetime data.
... Valid parameters include mdy, dmy, ymd, ydm, myd, and dym.
... This setting is only used in the interpretation of converting a string to a date value. It does not affect the display of date values.
... Set dateformat is SET during execution or runtime, rather than during analysis.
... Set dateformat will overwrite the implicit date format setting of set language.
The following is an example:
-- Set date format to year, day, month.
Set dateformat ydm;
GO
DECLARE @ datevar DATETIME;
SET @ datevar = '2014/1/12 ';
SELECT @ datevar AS DateVar;
GO
-- Set date format to year, month, day.
Set dateformat ymd;
GO
DECLARE @ datevar DATETIME;
SET @ datevar = '2014/1/31 ';
SELECT @ datevar AS DateVar;

GO

(4) list of dates

Date
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

 
Data Type
Range

 
Datetime
January 1-9, 1753

 
Smalldatetime
January 1-20, 1900-June 6