datename sql

One, SQL Server date-time functionsDate and time functions in SQL Server1. Current system date, timeSelect GETDATE ()2. DateAdd returns a new datetime value based on adding a period of time to the specified dateExample: Add 2 days to the dateSelect DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.0003. DateDiff returns the number of date and time boundaries across two specified dates.Select Date

query, result is a flute Descartes product Select S.id, S.name, C.id, c.name from student s cross join classes C --where s.cid = c.id; 6, from the connection (the same table to connect query) --Self-connected Select distinct s.* from student s, student S1 where S.id Ø function 1. Aggregate function Max Max, min minimum, count statistic, avg mean, sum sum, var variance Select Max (age) Max_age, Min (age) Min_age, Count (age) Count_age, AVG (age) Avg_age, SUM (age) Sum_age, VAR (age) Var_age from

SQL Time function, the need for friends can refer to the next. 1. Current system date, time select GETDATE ()2. DateAdd returns a new datetime value based on adding a period of time to the specified date for example: Add 2 days to the date select DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.000For example: Query current time last three days contents in descending order select * from table where time between DateAdd (Day,-3,getdate ())

dateExample: Add 2 days to the date[SQL] View plaincopyprint?Select DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.0003. DateDiff returns the number of date and time boundaries across two specified dates.[SQL] View plaincopyprint?Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 174. DatePart returns an integer representing the specified date portion of the specified date.[

Methods for obtaining various times for SQL statements1. Current system date, timeSelect GETDATE ()2. DateAdd returns a new datetime value based on adding a period of time to the specified dateExample: Add 2 days to the dateSelect DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.0003. DateDiff returns the number of date and time boundaries across two specified dates.Select DateDiff (Day, ' 2004-09-01 ', ' 2004-09-18 ')--return: 174. DatePa

') Select how many weeks of the year =datename (week, ' 2004-10-15 ') , today is the week =datename (weekday, ' 2004-10-15 ') function parameters/Functions GetDate () returns the current date and time of the system DateDiff (INTERVAL,DATE1,DATE2) returns the difference between Date2 and date1 two dates, as specified by interval date2-date1 DATEADD (interval,number,date) in the manner specified by interval,

,DATE2) returns the difference between Date2 and date1 two dates, as specified by interval Date2-date1 DATEADD (interval,number,date) in the manner specified by interval, plus the date after number DatePart (interval,date) returns the integer value of the specified part of the date, interval Datename (Interval,date) returns the string name of the specified part of the date, interval The set values for the parameter interval are as follows: value

Date and time functions in SQL Server 1. Current system date, time Select GETDATE () 2. DateAdd returns a new datetime value on the basis of adding a period of time to the specified date For example: Add 2 days to date Select DATEADD (day,2, ' 2004-10-15 ')--back: 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.

and time as a datetime value in the SQL Server 2005 standard internal format.SELECT GetDate ()--Return 2006-11-08 13:37:56.233(7) MONTH (date)Returns an integer that represents the month portion of a specified date.SELECT MONTH (GETDATE ())--Return 11(8) Year (date)Returns an integer that represents the year part of the specified date.SELECT year (GETDATE ())--Return 20062. Take a specific date(1) get the current date is the day of the weekSELECT

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,

Label:1, the common date method (below getdate () = ' 2006-11-08 13:37:56.233 ')(1) Datename (DatePart, date)Returns a String that represents the specified date portion of the specified date. DatePart See the list below.SELECT Datename (Day,getdate ()) – returns 8(2)DATEPART (DatePart, date)Returns an integer that represents the specified date portion of the specified date.SELECT DATEPART (Year,getdate ())

1, the common date method (below getdate () = ' 2006-11-08 13:37:56.233 ')(1) Datename (datepart, date)Returns a String that represents the specified date portion of the specified date. DatePart See the list below.SELECT Datename (Day,getdate ()) – returns 8(2) DATEPART (DATEPART, date)Returns an integer that represents the specified date portion of the specified date.SELECT DATEPART (Year,getdate ()) – ret

hour -- Returns the number of date boundary and time boundary between two specified dates. Select dateDiff (day, '2017-06-20 ', getDate ()); -- Difference in seconds Select dateDiff (second, '2017-06-22 11:00:00 ', getDate ()); -- Hours of difference Select dateDiff (hour, '2017-06-22 10:00:00 ', getDate ()); Select dateName (month, getDate (); -- current month Select dateName (minute, getDate (); -- curre

, specifying its default value as the return value of the function getdate (), as in this case: CREATE TABLE Site_log (Username VARCHAR (40),Useractivity VARCHAR (100),EntryDate DATETIME DEFAULT GETDATE ()) Convert Date and timeThe return value of the function getdate () is displayed only to seconds. In fact, the SQL sever internal time can be accurate to the millisecond level (to be exact, it can be exactly 3.33 milliseconds).To get the date and time

------------------------------------------------------------------------------------------------------------11 | 111 Japan | Yy/mm/dd------------------------------------------------------------------------------------------------------------12 | ISO | Yymmdd------------------------------------------------------------------------------------------------------------13 | 113 Euro Default | DD Mon yyyy hh:mi:ss:mmm (24 hour system)---------------------------------------------------------------------

date part of the specified dateSELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday6. Day (), month (), year ()--can be compared with datepartSelect Current date =convert (varchar), GETDATE (), 120), Current time =convert (varchar (8), GETDATE (), 114)Select Datename (DW, ' 2004-10-15 ')Select how many weeks of the year =datename (week, ' 2004-10-15 '), tod

. cid = c. id;6. Self-join (query connections for the same table)-- Self-connectionSelect distinct s. * from student s, student s1 where s. id Ø Functions1. Aggregate functionsMax maximum, min minimum, count statistics, avg average, sum, var varianceSelectMax (age) max_age,Min (age) min_age,Count (age) count_age,Avg (age) avg_age,Sum (age) sum_age,Var (age) var_ageFrom student;2. Date and Time FunctionsSelect dateAdd (day, 3, getDate (); -- add daySelect dateAdd (year, 3, getDate (); -- add year

(CHARINDEX (‘Mon‘,@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, ‘Mon‘,LEFT(DATENAME(MM, @Datetime),3)) IF (CHARINDEX (‘MM‘,@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, ‘MM‘,RIGHT(‘0‘+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2)) IF (CHARINDEX (‘M‘,@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, ‘M‘,CONVERT(VARCHAR,DATEPART(MM, @Datetime))) IF (CHARINDEX (‘DD‘,@StringDate) > 0) SET @StringDate = REPLACE(@StringDate, ‘DD‘

I. Automatic conversion functions for TIME typesCONVERT (varchar), GETDATE (), ()Original Address http://penpy.blog.163.com/blog/static/132546191201151011458482/A. SQL Get Time method: GETDATE () Gets the current system date, timeSelect GETDATE ()2. DateAdd returns a new datetime value based on adding a period of time to a specified dateExample: Add 2 days to the dateSelect DATEADD (day,2, ' 2004-10-15 ')--return: 2004-10-17 00:00:00.000

Tags: des style blog http color using OS strongSELECT * FROM table WHERE convert (Nvarchar, DateAndTime, 111) = CONVERT (Nvarchar, GETDATE (), 111) ORDER by DateAndTime DESCRecord of the Month SELECT * FROM table WHERE DateDiff (Month,[dateadd],getdate ()) =0 Week record SELECT * FROM table WHERE DateDiff (Week,[dateadd],getdate ()) =0 Day recordSELECT * FROM table WHERE DateDiff (Day,[dateadd],getdate ()) =0time functions in