Skip to content

Frequently Asked Questions - SQL Server Dates (DATETIME and SMALLDATETIME)

How can I get the current system date and time?

To get the current system date and time in SQL Server, you will use the GETDATE() date function.

SELECT GETDATE() AS [CurrentDateTime]

GETDATE() returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.

What's the equivalent of Oracle's SYSDATE in SQL Server?

The equivalent of Oracle's SYSDATE in SQL Server is the GETDATE() date function.

SELECT GETDATE() AS [SYSDATE]

Similar to Oracle's SYSDATE, GETDATE() returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. Date functions can be used in the SELECT statement select list or in the WHERE clause of a query.

How do I add or subtract days in a date?

There are 2 ways to add or subtract a certain number of days from a date. The first option is to simply add or subtract the number of days you want using the addition (+) or subtraction (-) mathematical operators. Here's an example on how to do it:

SELECT GETDATE() - 7 AS [SevenDaysAgo]
SELECT GETDATE() + 3 AS [ThreeDaysFromToday]

The other way is to use the DATEADD date function. The DATEADD date function returns a new datetime value based on adding an interval to the specified date.

SELECT DATEADD(DD, -7, GETDATE())AS [SevenDaysAgo]
SELECT DATEADD(DD, 3, GETDATE()) AS [ThreeDaysFromToday]

The syntax of the DATEADD date function is as follows:

DATEADD ( datepart , number, date )

datepart is the parameter that specifies on which part of the date to return a new value. For days, you can use either DD, D, or DAY. number is the value used to increment datepart. date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Regardless of which of these two methods is used, both of them will return a datetime value that includes the time part.

How can I extract the day, month and year parts of a DateTime column?

There are a couple of ways of extracting either the day, month or year parts from a datetime column. The first method is the use of the DAY, MONTH and YEAR date functions. The DAY date function returns an integer representing the day datepart of the specified date. The MONTH date function returns an integer that represents the month part of a specified date. Lastly, the YEAR date function returns an integer that represents the year part of a specified date.

The syntax of these date functions are as follows:

DAY ( date )
MONTH ( date )
YEAR ( date )

In all three date functions, the date parameter is an expression returning a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753. Here's an example of how to use it to get the month, day and year of the current system date:

SELECT MONTH(GETDATE()) AS [CurrentMonth],
       DAY(GETDATE())   AS [CurrentDay],
       YEAR(GETDATE())  AS [CurrentYear]

The second method of extracting the day, month or year from a datetime data type is with the use of the DATEPART date function. The DATEPART date function returns an integer representing the specified datepart of the specified date.

The syntax of the DATEPART date function is as follows:

DATEPART ( datepart , date )

The datepart is the parameter that specifies the part of the date to return. For the day, you can specify either DAY, DD, or D. For the month, you can specify either MONTH, MM, or M. Lastly, for the year, you can specify either YEAR, YYYY, or YY. The date is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753.

Here's an example of how to use the DATEPART date function to extract the month, day and year from the current system date:

SELECT DATEPART(MONTH, GETDATE()) AS [CurrentMonth],
       DATEPART(DAY, GETDATE())   AS [CurrentDay],
       DATEPART(YEAR, GETDATE())  AS [CurrentYear]

Choosing between the two methods, it is preferred to use the MONTH, DAY and YEAR date functions over the DATEPART date function simply because of simplicity of use. The three functions take only 1 parameter while DATEPART takes 2.

How can I get the date part only of a DateTime field similar to Oracle's TRUNC function?

In SQL Server, there is no single function that is the equivalent of Oracle's TRUNC function, which can be used to get just the date part of a datetime data type. But there are different ways of getting just the date part of a datetime data type and this is discussed on the following link:

Get Date Only User-Defined Function

How can I get just the time part of a DateTime data type?

To get just the time part of a DateTime data type, the CONVERT function can be used. The CONVERT function explicitly converts an expression of one data type to another. The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type. length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types. Lastly, style is the style of date format used to convert datetime or smalldatetime to character data.

To get the time part of a datetime data type, the style to use is 108, which will format the datetime expression to HH:MI:SS format.

SELECT CONVERT(VARCHAR(10), GETDATE(), 108) AS [CurrentTime]

If you want to include the millisecond (HH:MI:SS.MMM), the style to use is 114:

SELECT CONVERT(VARCHAR(13), GETDATE(), 114) AS [CurrentTime]

In both cases, the returned time part is of varchar data type. If you want to return the time part as a datetime data type, a CAST needs to be performed:

SELECT CAST(CONVERT(VARCHAR(10), GETDATE(), 108) AS DATETIME) AS [CurrentTime]
SELECT CAST(CONVERT(VARCHAR(13), GETDATE(), 114) AS DATETIME) AS [CurrentTime]

Casting the time part to a datetime data type will include a date part with a value of 01/01/1900 in the result. Here's an example:

CurrentTime
-------------------------
1900-01-01 09:30:25.000

How can I display the date in MM/DD/YYYY format?

To display a date in the MM/DD/YYYY format, you will be using the CONVERT function. The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type. length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types. Lastly, style is the style of date format used to convert datetime or smalldatetime to character data.

To display the date in the MM/DD/YYYY format, the value to be passed to the style parameter will be 101:

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]

If today is December 25, 2023, the output of this will be:

MM/DD/YYYY
------------
12/25/2023

It should be noted that the output is now of VARCHAR data type and not DATETIME data type.

How can I get the first day of the month?

There's no built-in function in SQL Server that will return the first day of the month for any given date. However it can easily be determined using the different date and mathematical functions that already exist in SQL Server. The basic concept is simply replacing the day part of the given date with 1. The following link discusses a few ways of getting the first day of the month:

Get First Day of the Month User-Defined Function

How can I get the difference in days between two dates?

To get the difference in days between two dates, you will be using the DATEDIFF date function. The DATEDIFF date function returns the number of date and time boundaries crossed between two specified dates and its syntax is as follows:

DATEDIFF ( datepart , startdate , enddate )

datepart is the parameter that specifies on which part of the date to calculate the difference. Since we are looking for the difference in days, we will be passing a value of DAY, DD or D in this parameter. startdate is the beginning date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format. enddate is the ending date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.

To get the number of days between the Fourth of July and Christmas day:

DECLARE @FourthOfJuly    DATETIME
DECLARE @Christmas       DATETIME

SET @FourthOfJuly = '2024/07/04'
SET @Christmas    = '2024/12/25'

SELECT DATEDIFF(DAY, @FourthOfJuly, @Christmas) AS [DateDifference]

The output of this script is 174 days.

What's the difference between smalldatetime and datetime and when do I use each?

A DATETIME data type is date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds

On the other hand, a SMALLDATETIME data type is a date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

Values with the DATETIME data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The SMALLDATETIME data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

SMALLDATETIME is usually used when you don't need to store the time of the day such as in cases of effectivity dates and expiration dates. DATETIME is used if the time of the day is needed and up to the second accuracy is required.

How can I get the number of days in a month?

In SQL Server, there's no built-in function that will return the number of days in a month for a given date. Here's a link to a user-defined function that returns the number of days in a month for the given input date:

Get Nuber of Days in a Month User-Defined Function

I have a table with a varchar column which contains dates. How can I determine if the column contains an invalid date?

To determine if an input expression or a varchar column is a valid date, you can use the ISDATE date function. The ISDATE date function determines whether an input expression is a valid date and returns 1 if the input expression is a valid date; otherwise, it returns 0. Here's the syntax of the ISDATE date function

ISDATE ( expression )

expression is an expression to be validated as a date and it should be any expression that returns a VARCHAR data type.

To use the ISDATE date function to identify invalid dates in a VARCHAR column, you can do the following:

SELECT * FROM [dbo].[Transactions]
WHERE ISDATE([TransDate]) = 0

How can I get the difference in hours between 2 datetime or smalldatetime values?

To get the difference in hours between two dates, you will be using the DATEDIFF date function. The DATEDIFF date function returns the number of date and time boundaries crossed between two specified dates and its syntax is as follows:

DATEDIFF ( datepart , startdate , enddate )

datepart is the parameter that specifies on which part of the date to calculate the difference. Since we are looking for the difference in hours, we will be passing a value of HOUR or HH in this parameter. startdate is the beginning date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format. enddate is the ending date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.

To illustrate, to get the number of hours between the time an employee times in for his shift until he times out:

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2024/01/12 08:00 AM'
SET @TimeOut = '2024/01/12 05:00 PM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut) AS [ShiftHours]

The output of this script is 9 hours.

ShiftHours
----------------
9

It is worth to note that when getting the difference in hours using the DATEDIFF with the HOUR specified as the datepart in the parameter, it does not consider the minutes in computing for the hours, as can be seen from this example:

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2024/01/12 08:45 AM'
SET @TimeOut = '2024/01/12 05:00 PM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut) AS [ShiftHours]

The output of this is also 9 hours even if the @TimeIn is 8:45 AM. Only the hour of both datetime variables is considered when computing for the difference.

To further illustrate, the following will return a value of 0 hours because the hours part of both datetime values are the same.

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2024/01/12 08:00 AM'
SET @TimeOut = '2024/01/12 08:45 AM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut) AS [ShiftHours]
GO

On the other hand, the following will return a value of 1 hour even if the actual difference between the 2 datetime values is just 1 minute.

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2024/01/12 08:59 AM'
SET @TimeOut = '2024/01/12 09:00 AM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut) AS [ShiftHours]
GO

How can I get the difference in hours with partial minutes as decimal value between 2 datetime values?

To get the difference in hours between two dates, you will be using the DATEDIFF date function. The DATEDIFF date function returns the number of date and time boundaries crossed between two specified dates and its syntax is as follows:

DATEDIFF ( datepart , startdate , enddate )

datepart is the parameter that specifies on which part of the date to calculate the difference. Since we are looking for the difference in hours with partial minutes as decimals, we will be passing a value of MINUTE, MI or N in this parameter instead of HOUR or HH, then divide the result by 60.0 to get the difference in hours. startdate is the beginning date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format. enddate is the ending date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.

To illustrate the difference between the use of HOUR and MINUTE in determining the difference in hours with decimal part, here's a script to get the number of hours between the time an employee times in for his shift until he times out:

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2024/01/12 08:15 AM'
SET @TimeOut = '2024/01/12 05:00 PM'

SELECT DATEDIFF(HOUR,   @TimeIn, @TimeOut)        AS [Hour Parameter],
       DATEDIFF(MINUTE, @TimeIn, @TimeOut) / 60.0 AS [Minute Parameter]

The output of this script is as follows:

Hour Parameter    Minute Parameter
----------------  ----------------
9                 8.750000

When getting the difference in hours using the DATEDIFF with the HOUR specified as the datepart in the parameter, it does not consider the minutes in computing for the hours, as can be seen from the example. To get a more accurate result, use the difference in minutes and divide it by 60.0. It should be noted here that 60.0 (with the decimal place) be used instead of just 60 because dividing 2 integer values will result into an integer value as well with the decimal truncated, as can be seen from the following:

DECLARE @TimeIn    DATETIME
DECLARE @TimeOut   DATETIME

SET @TimeIn  = '2024/01/12 08:15 AM'
SET @TimeOut = '2024/01/12 05:00 PM'

SELECT DATEDIFF(HOUR, @TimeIn, @TimeOut)          AS [Hour Parameter],
       DATEDIFF(MINUTE, @TimeIn, @TimeOut) / 60.0 AS [Minute Decimal],
       DATEDIFF(MINUTE, @TimeIn, @TimeOut) / 60   AS [Minute Integer]

The output of this script is as follows:

Hour Parameter    Minute Decimal    Minute Integer
----------------  ----------------  ----------------
9                 8.750000          8

How can I determine if it is a leap year?

In SQL Server, there's no built-in function that will determine if a given year is a leap year or not. Here's a link to a user-defined function that determines whether a given date is a leap year or not:

Determine Leap Year

How can I display the date in YYYY/MM/DD format?

To display a date in the YYYY/MM/DD format, you will be using the CONVERT function. The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type. length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types. Lastly, style is the style of date format used to convert datetime or smalldatetime to character data.

To display the date in the YYYY/MM/DD format, the value to be passed to the style parameter will be 111, which is the Japan date standard:

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]

If today is December 25, 2023, the output of this will be:

YYYY/MM/DD
------------
2023/12/25

It should be noted that the output is now of VARCHAR data type and not DATETIME data type.

How do I add or subtract months to a datetime or smalldatetime value?

To add or subtract months to a datetime or smalldatetime value, you will use the DATEADD date function. The DATEADD date function returns a new datetime value based on adding an interval to the specified date. The syntax of the DATEADD date function is as follows:

DATEADD ( datepart , number, date )

datepart is the parameter that specifies on which part of the date to return a new value. For months, you can use either MONTH, MM, or M. number is the value used to increment datepart. date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Here's an example on how to use the DATEADD date function to increase or decrease a datetime value by a certain number of months:

SELECT DATEADD(MONTH, -12, GETDATE())AS [TwelveMonthsAgo]
SELECT DATEADD(MM, 6, GETDATE()) AS [SixMonthsFromToday]

How can I get the first day of the week?

In SQL Server, there's no built-in function that will return the first day of the week for any given date. Here's a link to a user-defined function that returns the date of the first day of the week for any given input date:

Get First Day of the Week

How do I get the name of the month instead of the month number?

To get the name of the month instead of the month number, you will use the DATENAME date function. The DATENAME date function returns a character string representing the specified datepart of the specified date and its syntax is as follows:

DATENAME ( datepart, date )

datepart is the parameter that specified the part of the date to return. For months, you can use either MONTH, MM or M. date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Here's an example on how to use the DATENAME date function to get the name of the month of a given date:

DECLARE @ChristmasDay    DATETIME
SET @ChristmasDay = '2023/12/25'

SELECT DATENAME(MONTH, @ChristmasDay) AS [Month Name]

Month Name
---------------
December

How do I get the name of the day of the week, such as Sunday or Monday?

To get the name of the day of the week such as Sunday or Monday, you will use the DATENAME date function. The DATENAME date function returns a character string representing the specified datepart of the specified date and its syntax is as follows:

DATENAME ( datepart, date )

datepart is the parameter that specified the part of the date to return. For the day of the week, you can use either WEEKDAY or DW. date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Here's an example on how to use the DATENAME date function to get the name of the day of the week of a given date:

DECLARE @ChristmasDay DATETIME    SET
@ChristmasDay = '2023/12/25' SELECT

DATENAME(WEEKDAY, @ChristmasDay) AS [Day Of Week]

Day Of Week
---------------
Monday

How do I convert a date stored as decimal or numeric in the YYYYMMDD format into a datetime or smalldatetime data type?

To convert a date stored as decimal or numeric in the YYYYMMDD format into a datetime or smalldatetime data type, you can use the CAST function. The CAST function explicitly converts an expression of one data type to another.

But since the date is stored as a decimal or numeric data type, two calls to the CAST function needs to be done. The first CAST will convert the decimal or numeric data type to VARCHAR. The second call of the CAST function will convert the VARCHAR into DATETIME or SMALLDATETIME data type.

To illustrate, to convert the date 20240704 which is stored as a decimal or numeric data type into a datetime or smalldatetime data type:

SELECT CAST(CAST(20240704 AS VARCHAR(8)) AS DATETIME)

If the decimal or numeric date is converted straight to datetime without converting it first to VARCHAR, the following error will be encountered:

SELECT CAST(20240704 AS DATETIME)

Server: Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type datetime.

How do I get the first day of the quarter?

In SQL Server, there's no built-in function that will return the first day of the quarter for any given date. Here's a link to a user-defined function that returns the date of the first day of the quarter for any given input date:

Get First Day of the Quarter

How can I display a date in DD/MM/YYYY format?

To display a date in the DD/MM/YYYY format, you will be using the CONVERT function. The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

expression is any valid Microsoft® SQL Server™ expression. data_type is the target system-supplied data type. length is an optional parameter of nchar, nvarchar, char, varchar, binary or varbinary data types. Lastly, style is the style of date format used to convert datetime or smalldatetime to character data.

To display the date in the DD/MM/YYYY format, the value to be passed to the style parameter will be 103, which is the British or French date standard:

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]

If today is December 25, 2023, the output of this will be:

DD/MM/YYYY
------------
25/12/2023

It should be noted that the output is now of VARCHAR data type and not DATETIME data type.

How do I add or subtract hours to a datetime or smalldatetime value?

To add or subtract hours to a datetime or smalldatetime value, you will use the DATEADD date function. The DATEADD date function returns a new datetime value based on adding an interval to the specified date. The syntax of the DATEADD date function is as follows:

DATEADD ( datepart , number, date )

datepart is the parameter that specifies on which part of the date to return a new value. For hours, you can use either HOUR or HH. number is the value used to increment datepart. date is an expression that returns a datetime or smalldatetime value, or a character string in a date format.

Here's an example on how to use the DATEADD date function to increase or decrease a datetime value by a certain number of hours:

SELECT DATEADD(HOUR, -12, GETDATE())AS [TwelveHoursAgo]
SELECT DATEADD(HH, 6, GETDATE()) AS [SixHoursLater]

How can I get the last day of the month?

In SQL Server, there's no built-in function that will return the last day of the month for any given date. Here's a link to a user-defined function that returns the date of the last day of the month for any given input date:

Get Last Day of the Month

How can I get the difference in months between two datetime or smalldatetime values?

To get the difference in months between two DATETIME or SMALLDATETIME values, you will be using the DATEDIFF date function. The DATEDIFF date function returns the number of date and time boundaries crossed between two specified dates and its syntax is as follows:

DATEDIFF ( datepart , startdate , enddate )

datepart is the parameter that specifies on which part of the date to calculate the difference. Since we are looking for the difference in months, we will be passing a value of MONTH, MM, or M in this parameter. startdate is the beginning date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format. enddate is the ending date for the calculation and it's an expression that returns a datetime or smalldatetime value, or a character string in a date format.

How do I get the day of the year for a given date?

To get the day of the year for a given date you will make use of the DATEPART date function. The DATEPART date function returns an integer representing the specified datepart of the specified date.

The syntax of the DATEPART date function is as follows:

DATEPART ( datepart , date )

The datepart is the parameter that specifies the part of the date to return. For the day of the year, you will specify either DY or Y. The date is an expression that returns a datetime or smalldatetime value, or a character string in a date format. Use the datetime data type only for dates after January 1, 1753.

Here's an example of how to use the DATEPART date function to extract the day of the year from the current system date:

SELECT DATEPART(DY, GETDATE()) AS [CurrentDayOfTheYear]

How do I store a date value before January 1, 1753?

Since the minimum date value that a DATETIME data type can have is January 1, 1753, you have to use either INT, DECIMAL/NUMERIC or VARCHAR data type to hold a date value before January 1, 1753. Of these three data types, the best data type to use is the INT data type because it will only require 4 bytes to store the data. DECIMAL or NUMERIC data type will require 5 bytes to store the date value in YYYYMMDD format while a VARCHAR data type will use 8 bytes.

Regardless of the data type you will use, it is important that the format to use to store the date value is in the YYYYMMDD format. If the date value is stored in this format, sorting it by this value will still result in a correct chronological order.

How do I get the number of days in a year?

In SQL Server, there's no built-in function that will return the number of days in a year for a given date. Here's a link to a user-defined function that returns the number of days in a year for the given input date:

Get Number of Days in a Year