Sql Date Functions

 1️⃣ Current Date & Time

    GETDATE()              -- current date & time
    CURRENT_TIMESTAMP      -- same as GETDATE()
    SYSDATETIME()          -- higher precision
    GETUTCDATE()           -- UTC date & time.    

✅ Use GETDATE() when:

• You only need date & time

• Precision is not critical

• Legacy systems or existing DATETIME columns

  Typical ETL load timestamps

✅ Use SYSDATETIME() when:

• You need high precision timestamps

• Logging, auditing, CDC, or incremental loads

• Using DATETIME2 columns (recommended)

 

2️⃣ Extract Parts of Date

YEAR(GETDATE())
MONTH(GETDATE())
DAY(GETDATE())

DATEPART(HOUR, GETDATE())
DATEPART(MINUTE, GETDATE())
DATEPART(SECOND, GETDATE())
 
Add or Subtract Dates
DATEADD(DAY, 5, GETDATE())     -- add 5 days
DATEADD(MONTH, -1, GETDATE()) -- subtract 1 month
DATEADD(YEAR, 1, GETDATE())   -- add 1 year
 
Difference Between Dates
DATEDIFF(DAY, start_date, end_date)
DATEDIFF(MONTH, start_date, end_date)
DATEDIFF(YEAR, start_date, end_date)
Example:
SELECT DATEDIFF(DAY, '2025-01-01', GETDATE());
 
Convert / Format Dates
CAST(GETDATE() AS DATE)        -- remove time
CAST(GETDATE() AS TIME)

CONVERT(VARCHAR, GETDATE(), 120) -- yyyy-mm-dd hh:mi:ss

First / Last Day of Month
--- First day of current month
DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

-- Last day of current month
EOMONTH(GETDATE())
 
 Date Validation

ISDATE('2025-02-28')  -- returns 1 or 0


8️⃣ Age Calculation
DATEDIFF(YEAR, dob, GETDATE())

🔹 Other Databases (Quick Comparison)
Oracle :
SYSDATE
CURRENT_DATE
ADD_MONTHS(date, n)
MONTHS_BETWEEN(d1, d2)

MySQL :
NOW()
CURDATE()
DATE_ADD(date, INTERVAL 1 DAY)
DATEDIFF(d1, d2)


How to get last 7 days records.
SELECT *
FROM orders
WHERE order_date >= DATEADD(DAY, -7, GETDATE()); 
Includes 7 * 24 days. 
SELECT *
FROM orders
WHERE order_date >= CAST(DATEADD(DAY, -7, GETDATE()) AS DATE);
✔ Includes full dates 
Using DATEDIFF
SELECT *
FROM orders
WHERE DATEDIFF(DAY, order_date, GETDATE()) <= 7;