SQL Learning Summary

I. Automatic conversion functions for TIME types

CONVERT (varchar), GETDATE (), ()

Original Address http://penpy.blog.163.com/blog/static/132546191201151011458482/

A. SQL Get Time method:

1. GETDATE () Gets the current system date, time
   Select GETDATE ()
   2. DateAdd

returns a new datetime value based on adding a period of time to a specified date
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 ')--return 10
5. Datename

Returns a string representing the specified date part of a specified date
How many weeks this year =datename (Week,getdate ())
today is =datename (Weekday,getdate ())
Select Datename (DW, ' 2004-10-15 ')
SELECT Datename (Weekday, ' 2004-10-15 ')--return: Friday

6. Day (), month (), year ()--can be compared with datepart

function	function
GetDate ()	Return system current date and time
DateDiff (interval,date1,date2)	Returns the difference between Date2 and date1 two dates, in the way specified by interval date2-date1
DATEADD (interval,number,date)	interval specified by  , plus the date after number;
DatePart (interval,date)	Returns the integer value   for the specified part of the date, interval;
Datename (interval,date)	Return Date D Ate, interval the string name corresponding to the specified part

where the value of the parameter interval is set as follows

Parameter values	Meaning
Year|yy|yyyy	Get year
quarter| Qq|q	Quarter Get season
month| Mm|m	Month 1 ~ 12
Day of year| Dy |y	The number of days in a year, the day of the year 1-366
Weekday|dw|w	The number of days in a week, the day of the week 1-7
Hour Hh H	Time 0 ~ 23
Minute|mi|n	Min 0~59
Second Ss S	seconds 0 ~ 59
Millisecond Ms	Milliseconds 0 ~ 999

Get system datetime with date () and now () in Access and ASP

which Datediff,dateadd,datepart can also be used in Access and ASP, and the usage of these functions is similar
Example:
1.GetDate () for SQL Server

Select GetDate ()
2.DateDiff (' s ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 514,592 seconds
DateDiff (' d ', ' 2005-07-20 ', ' 2005-7-25 22:56:32 ') returns a value of 5 days
3. DatePart (' W ', ' 2005-7-25 22:56:32 ') returns a value of 2 that is Monday DatePart (' d ', ' 2005-7-25 22:56:32 ') with a return value of 25 or 25th
DatePart (' y ', ' 2005-7-25 22:56:32 ') returns a value of 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
Instance

1.write functions that implement the following' Date, day of the week, morning and 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 ()) < "I ' am ' Else ' PM ' End

2: Write the function, depending on the input time. The day of the year is output
Select DATEPART (dy,getdate ())
3. Find random output characters ' A-Z
Select char (97+abs (checksum (NEWID))%26)
Select char (97+rand () *26)

Two Date formatting processing

set the [datetime] field value for table tables in SQL Server ' 2007-11-07 16:41:35.033 '

Convert to the specified format

CONVERT (varchar (), GETDATE (), parameter values)

101	09/12/2004
102	2004.09.12
103	12/09/2004
104	12.09.2004
105	12-09-2004
106	12 09 2004
107	09 12, 2004
108	11:06:08
109	09 12 2004 1
110	09-12-2004
112	20040912
113	12 09 2004 1
114	11:06:08.177
120	2007-11-07 00:00:00

Current date =convert (varchar), GETDATE (), 120)
Current time =convert (varchar (8), GETDATE (), 114)

SQL Learning Summary