1. current system date, Time Select getdate () 2. dateadd returns a new datetime on the basis of adding a period of time to a specified date Values for example: Add 2 days to the date Select dateadd (day,2, ' 2004-10-15 ') --return:2004-10-17 00:00:00.000 3. datediff returns the number of date and time boundaries across two specified dates. Select datediff (Day, ' 2004-09-01 ', ' 2004-09-18 ') --return:17 4. datepart Returns an integer representing the specified date portion of the specified date. Select datepart (month, ' 2004-10-15 ') --back 10 5. datename Returns a string representing the specified date portion of the specified date select datename (weekday, ' 2004-10-15 ') --returned: Friday 6. day (), Month (), year () --can compare with datepart select current date =convert (varchar), GETDATE (), 120), Current time =convert ( varchar (8), GETDATE (), 7. select datename (DW, ' 2004-10-15 ') select How many weeks of the year =datename ( Week,getdate ()), today is Weeks =datename (Weekday,getdate ()) functions Parameters/Functions getdate ( ) -- Returns the current date and time of the system DateDiff (INTERVAL,DATE1,DATE2) --in interval specified way, returns date2 Difference between Date1 and two dates date2-date1 dateadd (interval,number,date) --the date specified in the interval, plus number after the DatePart ( Interval,date) ---Return date, interval the integer value corresponding to the specified part DateName (interval,date) --returns the date in Interval the specified part of the string name parameter interval set the value as follows: value indent Write (sql server) access ASP description Year Yy yyyy year &NBSP;1753&NBSP;~&NBSP;9999&NBSP;QUARTER&NBSP;QQ q season 1 ~ 4 Month Mm m Month 1 ~ 12 day of year dy y Day of the year, Day 1-366 Day Dd d Day of the year, 1-31&NBSP;WEEKDAY&NBSP;DW w the day of the week, the day of the week 1-7 Week Wk ww Week, the week of the year 0 ~ 51 hour Hh h 0 ~ 23 minute mi n min 0 ~ 59 second ss s seconds 0 ~ 59 Millisecond Ms - milliseconds 0 ~ 999 Date () in access and asp And now () get the system date time; where Datediff,dateadd,datepart is also used in Access and ASP, these functions are also similar to examples: 1. GetDate () for Sql server :select getdate () 2.datediff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') The return value is 514592 s DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') return value 5 days The return value of 3.DatePart (' W ', ' 2005-7-25 22:56:32 ') is 2 Monday (Sunday is 1, Saturday is 7) DatePart (' d ', ' 2005-7-25 22:56:32 ') The return value is 25 25th # datepart (' y ', ' 2005-7-25 22:56:32 ') The return value is 206 that is the No. 206 day of the Year datepart (' yyyy ', ' 2005-7-25 22:56:32 ') returns a value of 2005 that is 2005 sql server datepart () function return SQLServer Part of the datetime field. The syntax for the sql server datepart () function is: datepart (portion, datetime) which datetime is the name of the SQLServer datetime field and section is one of the following: ms for millisecondsyy for YearQq for Quarter of the YearMm for MonthDy for the day of the Yeardd for day of the monthwk for weekdw for the day of the weekhh for hourmi for minutess for second --1. Writing functions, Realization according to ' Month Day, days of the week, morning afternoon evening ' output time information (March 16, 2009 Monday pm) select datename (Yy,getdate ()) + ' year ' + datename (Mm,getdate ()) + ' month ' + datename (DD, GETDATE ()) + ' Day ' + datename (Weekday,getdate ()) + case when datename (Hh,getdate ()) < 12 then ' Morning ' else ' PM ' end --2. Write the function, depending on the input time. The output of the day is the first days of the Year Select datepart (Dy,getdate ())--3. Find out the random output character ' A-zselect char (97+abs (Checksum ())) newid) Select char (97+rand () *26)
Ms-sql operation of Time