Getting Year, Month and Day Out of DATETIME Values in SQL Server

Q

How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL?

✍: FYIcenter.com

A

You can use DATEPART() to get any part of the DATETIME value. But to get year, month and day, you can use 3 specific functions: YEAR(), MONTH() and DAY(). These functions are equivalent to DATEPART() as:

YEAR(date) = DATENAME(year, date)
MONTH(date) = DATENAME(month, date)
DAY(date) = DATENAME(day, date)

In following tutorial example shows you how display a birth date in a format of yyyy.m.d:

DECLARE @birth_date DATETIME;
SET @birth_date = '1987-05-19 16:10:41.403';
SELECT 'You were born on '
   + CONVERT(VARCHAR(10),YEAR(@birth_date))
   + '.'
   + CONVERT(VARCHAR(10),MONTH(@birth_date))
   + '.'
   + CONVERT(VARCHAR(10),DAY(@birth_date));
GO
You were born on 1987.5.19

⇒ Difference Between GETDATE() and GETUTCDATE() in SQL Server

⇐ Getting Parts of DATETIME Values as Integers in SQL Server

⇑ Date/Time Operations and Functions in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials

2017-02-14, 4334🔥, 0💬

Difference Between GETDATE() and GETUTCDATE() in SQL Server
What Is the Difference Between GETDATE() and GETUTCDATE() in SQL Server Transact-SQL? The difference between GETDATE() and GETUTCDATE() is time zone number of the SQL Server machine. The tutorial exercise below gives you a good example: DECLARE @local_time DATETIME; DECLARE @gmt_time DATETIME; SET @... 2017-02-14, 5198🔥, 0💬

DATEADD() Function Usage Examples in SQL Server
How To Use DATEADD() Function in SQL Server Transact-SQL? DATEADD() is a very useful function for manipulating date and time values. The following tutorial exercise shows you some good DATEADD() usage examples: -- Incrementing 1 year on a leap date DECLARE @birth_date DATETIME; SET @birth_date = '20... 2017-02-20, 5028🔥, 0💬

Formatting Time Zone in +/-hh:mm Format in SQL Server
How To Format Time Zone in +/-hh:mm Format in SQL Server Transact-SQL? From the previous tutorial, you know how to calculate the time zone value by using GETDATE() and GETUTCDATE() functions. But how can you format that time zone value in a nice looking format like +05:45 or -04:00? Unfortunately, S... 2017-02-08, 4673🔥, 0💬

CONVERT() - Formatting DATETIME Values to Strings in SQL Server
How To Format DATETIME Values to Strings with the CONVERT() Function in SQL Server Transact-SQL? SQL Server 2005 offers no functions to format DATETIME values in your own format patterns. But it does provide you a number of pre-defined format patterns that you can use with the CONVERT(char_type, dat... 2017-02-08, 4602🔥, 0💬

Getting Year, Month and Day Out of DATETIME Values in SQL Server
How To Get Year, Month and Day Out of DATETIME Values in SQL Server Transact-SQL? You can use DATEPART() to get any part of the DATETIME value. But to get year, month and day, you can use 3 specific functions: YEAR(), MONTH() and DAY(). These functions are equivalent to DATEPART() as: YEAR(date) = D... 2017-02-14, 4335🔥, 0💬

DATEDIFF() - Calculating DATETIME Value Differences in SQL Server
How To Calculate DATETIME Value Differences Using the DATEDIFF() Function in SQL Server Transact-SQL? If you want to calculate the difference between two date and time values, you can use the DATEDIFF() function in the following format: DATEDIFF(datepart, startdate, enddate) returns INT: "startdate"... 2017-02-20, 4273🔥, 0💬

Setting New Values to Parts of a DATETIME Value in SQL Server
How To Set Different Parts of a DATETIME Value in SQL Server Transact-SQL? In SQL Server, you can get different parts of a DATETIME value with the DATEPART() functions. But there is no function that allows you to set different parts to a DATETIME value. For example, you a date_of_birth column as DAT... 2017-02-08, 2817🔥, 0💬

Truncating DATETIME Values to Dates without Time in SQL Server
How To Truncate DATETIME Values to Dates without Time in SQL Server Transact-SQL? Assuming that you have some date and time, DATETIME, values, and you want to work with dates only. You want to truncate them to dates without time, or with time of 00:00:00.000. Again, SQL Server offers no simple solut... 2017-02-08, 2546🔥, 0💬

Getting Month and Weekday Names from DATATIME Values in SQL Server
How To Get Month and Weekday Names from DATETIME Values in SQL Server Transact-SQL? In DATETIME values, the month part and weekday part have names. You can use the DATENAME() function to get the month name and weekday name from a DATETIME value in the following format: DATENAME(datepart, date) retur... 2017-02-14, 2153🔥, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.