keywords:
Bookmark and Share



Front Back
  NOW()    Returns the current date and time,
CREATE TABLE Orders ( OrderId int NOT NULL, ProductName varchar(50) NOT NULL, OrderDate datetime NOT NULL DEFAULT NOW(), PRIMARY KEY (OrderId) )
CURDATE() Returns the current date 2014-11-22
CURTIME()    Returns the current time 12:45:34
DATE()    Extracts the date part of a date or date/time expression
SELECT ProductName, DATE(OrderDate) AS OrderDate FROM Orders WHERE OrderId=1

==========  Jarlsberg Cheese        2014-11-22
EXTRACT()    Returns a single part of a date/time SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, YEAR_MONTH SECOND_MICROSECOND, MINUTE_MICROSECOND, MINUTE_SECOND, HOUR_MICROSECOND HOUR_SECOND, HOUR_MINUTE, DAY_MICROSECOND, DAY_SECOND, DAY_MINUTE, DAY_HOUR MICROSECOND
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear, EXTRACT(MONTH FROM OrderDate) AS OrderMonth, EXTRACT(DAY FROM OrderDate) AS OrderDay FROM Orders WHERE OrderId=1
OrderYear OrderMonth OrderDay 2014 11 22
DATE_ADD()    Adds a specified time interval to a date (Expiration Date)

SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, YEAR_MONTH 

DATE_ADD(date,INTERVAL expr type) 2014-11-22
  SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 30 DAY) AS OrderPayDate FROM Orders  
1 2014-12-22 13:23:44.657
DATE_SUB()    Subtracts a specified time interval from a date (Expiration Date)

SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, YEAR, YEAR_MONTH 

DATE_SUB(date,INTERVAL expr type) 2014-4-22
SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 5 DAYAS SubtractDate FROM Orders
OrderId SubtractDate 1 2014-11-17 13:23:44.657
DATEDIFF()    Returns the number of days between two dates DATEDIFF(date1,date2)
SELECT DATEDIFF('2014-11-30','2014-11-29') AS DiffDate Result: 1 This can go Negative also
DATE_FORMAT()    Displays date/time data in different formats (%a-... mean different things) DATE_FORMAT(date,format)
DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') DATE_FORMAT(NOW(),'%m-%d-%Y') DATE_FORMAT(NOW(),'%d %b %y') DATE_FORMAT(NOW(),'%d %b %Y %T:%f')  Nov 04 2014 11:45 PM 11-04-2014 04 Nov 14 04 Nov 2014 11:45:34:243
Date_Format()

Select count(a.AccountID) As New_EFCO_Accounts,
Sum(af.LicenseCount) As New_User_License_Count, Date_Format(a.CreationDate, '%M %Y')
From Accounts a
INNER JOIN AccountFeatures af
on a.AccountID = af.AccountID
WHERE Month(a.CreationDate) = Month(Now()) And Year(a.CreationDate) = Year(Now()) And af.FeatureID=1 And a.AccountType <>'Backup'
Results 

April 2017
x of y cards