Ms-sql operation of Time

Source: Internet
Author: User
Tags date1

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.