Shulou(Shulou.com)06/01 Report--

The time type is actually a floating-point Double type with a size of 8 bytes.

Time units:1 day =1, 1 hour =1/24, 1 minute =1/60/24, 1 second =1/60/60/24

Date and time functions in SQL Server:

select getdate() --Current system date and time select GETUTCDATE() --GetUTCDate Get UTC time value select year(getdate()) --Get year information YEAR (year function returns year information for a specific date in int data type format)select month(getdate()) --Get month information MONTH(month function returns month information for a specific date in int data type format)select day(getdate()) --Get day information day(the day function returns the number of days for a specific date in the format of int data type)

datepart( datepart , date field) --Gets any time part DatePart(datepart function returns a specified part of a datetime data in the format of int data type)datepart: the format code of the specific data to be returned

The values of the Datepart section are as follows:

Year, YYYY, YY

Return Month, MM, M

Return date information Day, DD, D

Return to Week, WK, WW

Return to weekday information WeekDay, DW

Return to Quarterly Information Quarter, QQ, Q

Returns information about the day of the year DayOfYear, DY, Y

Return Hour Information Hour, HH

Minute, MI, N

Second, SS, SS

MillSecond, MS

select dateadd(day,2,'2010-10- 15')--, Return: 2010-10-17 00:00:00.000(add 2 days to date)select dateadd(day,-2,'2010-10- 15')--Return: 2010-10-13 00:00:00.000(subtract 2 days from date)select dateadd(month,-2,'2010-10- 15')--Return: 2010-08-15 00:00:00.000(subtract February from date)select datediff(day,'2004-09- 01','2004 - 09- 18') --datediff , returns: 17(returns the number of date and time boundaries spanning two specified dates)select * from test where datediff(day,Rq,getdate()) = 0 --where Rq is the date column, what does this line code mean? getdate() subtracts the value of column Rq to 0, the value of column Rq is subtracted from today, the number is 0 select DATEPART(month,getdate()) --returns 10(datepart returns the integer of the month part of the current date) select DATEPART(Day,getdate()) --Returns the integer of the days part of the current date select DATEPART(hour,getdate()) --Returns the integer of the hour part of the current date select DATEPART(minute,getdate()) --Returns an integer representing the minute portion of the current date select datename(weekday,getdate()) --Returns: Friday (datename returns a string representing the specified date portion of the specified date)select datename(dw,getdate()) --select datepart(Quarter,getdate()) --select the current quarter =datename(week,getdate()) , day of the week =datename(weekday,getdate())select current date =convert(varchar(10),getdate(),120) , current time =convert(varchar(8),getdate(),114)(day(), month(),year() --can be compared with datepart)

New date-time functions in SQL Server 2008:

SysDateTime and SysUTCDate are more accurate in fractional seconds than GetDate and GetUTCDate. SysTimeOffset contains the system time zone offset. SysDateTime, SysUTCDate, and SysDateTimeOffset can be assigned to variables of any date and time type.

The sysdatetime function returns the date and time of the current system in the format datetime2(7) data type. The syntax is sysdatetime(). The return value has seven decimal places, but is accurate to only 10 milliseconds. Database offset is not included.

The sysdatetimeoffset function returns the date and time of the current system in the format datetimeoffset(7) data type. The syntax is sysdatetimeoffset(). The return value has seven decimal places, but is accurate to only 10 milliseconds. Database time zone offset included.

ISDATE(expression) --Determine whether it is date data isDateSELECT SYSDATETIME() --Get system time SysDateTimeSELECT sysdatetimeoffset() --Get current date and time SysDateTimeOffsetselect SysUTCDateTime() --Get system UTC time SysUTCDateTime`SELECT CURRENT_TIMESTAMP --Current_TimeStamp Current database system timestamp SELECT SWITCH OFFSET (SysDateTimeOffset(), '+07: 00')--SWITCH OFFSET Change time offset SELECT TodateTimeOffset(GetDate(),'+07: 00') --TodateTimeOffset datetime2 to datetimeoffset

statistical statement

1. --Statistics of current [> data after 00:00 of the day]

SELECT * FROM TABLE WHERE CONVERT(Nvarchar, Date field, 111) = CONVERT(Nvarchar, GETDATE(), 111) ORDER BY Date field DESC

2-Statistics this week

SELECT * FROM TABLE WHERE datediff(week,[date field],getdate())=0

3. --Statistics for this month

SELECT * FROM TABLE WHERE datediff(month,[date field],getdate())=0

4. Current statistics

SELECT * FROM TABLE WHERE datediff(day,[datefield1],getdate())=0Select * From [SQL Server]. [nbbs]. [dbo]. [TopicCurrent] with(nolock) Where Convert(varchar(10),[datefield2],120) = Convert(varchar(10),getDate(),120)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.