keywords:
Bookmark and Share



Front Back
GETDATE()    Returns the current date and time SELECT GETDATE() AS CurrentDateTime
CREATE TABLE Orders ( OrderId int NOT NULL PRIMARY KEY, ProductName varchar(50) NOT NULL, OrderDate datetime NOT NULL DEFAULT GETDATE() ) Notice that the OrderDate column specifies GETDATE() as the default value. As a result, when you insert a row into the table, the current date and time are automatically inserted into the column.
DATEPART()    Returns a single part of a date/time Year (yy, yyyy), quarter    (qq, q), month (mm, m) dayofyear (dy, y), day (dd, d), week (wk, ww) weekday (dw, w), hour (hh), minute (mi, n), second (ss, s)
SELECT DATEPART(yyyy,OrderDate) AS OrderYear, DATEPART(mm,OrderDate) AS OrderMonth, DATEPART(dd,OrderDate) AS OrderDay FROM Orders WHERE OrderId=1
OrderYear OrderMonth OrderDay 2014 11 22
DATEADD() Adds or subtracts a specified time interval from a date, DATE_ADD(date,INTERVAL expr type) SECOND , MINUTE , HOUR, DAY , WEEK , MONTH , QUARTER , YEAR , MICROSECOND , DAY_SECOND , DAY_MINUTE , DAY_HOUR , YEAR_MONTH , MINUTE_SECOND , HOUR_SECOND ,SECOND_MICROSECOND, MINUTE_MICROSECOND, HOUR_MICROSECOND, HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND
DATEDIFF()    Returns the time between two dates Year (yy, yyyy), quarter    (qq, q), month (mm, m) dayofyear (dy, y), day (dd, d), week (wk, ww) weekday (dw, w), hour (hh), minute (mi, n), second (ss, s)
DATEDIFF(datepart,startdate,enddate) SELECT DATEDIFF(day,'2014-06-05','2014-08-05') AS DiffDate
DiffDate 61
CONVERT()    Displays date/time data in different formats
0 or 100 mon dd yyyy hh:miAM (or PM) Default   1 101 1 = mm/dd/yy 101 = mm/dd/yyyy  
CONVERT(VARCHAR(19),GETDATE()) CONVERT(VARCHAR(10),GETDATE(),10) CONVERT(VARCHAR(10),GETDATE(),110) CONVERT(VARCHAR(11),GETDATE(),6) CONVERT(VARCHAR(11),GETDATE(),106) CONVERT(VARCHAR(24),GETDATE(),113) Nov 04 2014 11:45 PM 11-04-14 11-04-2014 04 Nov 14 04 Nov 2014 04 Nov 2014 11:45:34:243
FORMAT()    Formats how a field is to be displayed SELECT FORMAT(column_name,format) FROM table_name;
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate FROM Products;
ProductName Price PerDate Chais  18  2017-03-30  Chang  19  2017-03-30  Aniseed Syrup  10  2017-03-30  Chef Anton's Cajun Seasoning  22  2017-03-30 
NOW()    Returns the current system date and time SELECT NOW() FROM table_name;
SELECT ProductName, Price, Now() AS PerDate FROM Products;
ProductName Price PerDate Chais  18  3/30/2017 9:15:39 AM  Chang  19  3/30/2017 9:15:39 AM 
x of y cards