SQL Server Extract Year Month Or Day From DATE Data Type? – 2 simple and useful queries!

SQL Server extract year month or day from date data type? - 2 simple and useful queries!
Photo by rafzin p on Unsplash
Share this post and Earn Free Points!

If you used databases like Oracle or Teradata, you can be surprised that very popular function like [ SQL Server extract year month or day from date ] extract(year from date) not work for Microsoft’s databases. In this post I show you how to extract only a day, month or year from Microsoft SQL Server or Azure SQL Database. The both of these databases use Transact-SQL language.

Introduction

Microsoft SQL Server Database

Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is primarily used to store and manage data for a wide range of applications, including business, web, and mobile applications.

SQL Server is available in a number of editions, including Enterprise, Standard, and Express, each of which is suited for different types and sizes of applications. SQL Server is available for Windows and Linux, and supports a range of programming languages, including T-SQL, .NET, and Python.

SQL Server offers a range of features and tools to help you manage and work with your data, including:

  • A graphical user interface (GUI) for managing database objects and configuring server settings
  • A database engine for storing and retrieving data
  • A SQL language for querying and manipulating data
  • A set of built-in functions and stored procedures for performing common tasks
  • A security model for controlling access to data and database objects
  • A set of tools for backing up and restoring data, monitoring server performance, and more

In addition to these core features, SQL Server also offers a range of additional tools and features for managing and working with data, including support for business intelligence, data warehousing, and analytics.

Data Types In SQL Server

Microsoft SQL Server supports a range of data types for storing different types of data in a database. Here are some of the most commonly used data types in SQL Server:

  1. char and varchar: These data types are used to store character strings of fixed and variable lengths, respectively.
  2. nchar and nvarchar: These data types are similar to char and varchar, but they store Unicode character strings.
  3. int: This data type is used to store integers (whole numbers) with a range of -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647).
  4. bigint: This data type is used to store larger integers with a range of -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807).
  5. decimal and numeric: These data types are used to store precise decimal numbers with a fixed precision and scale.
  6. float: This data type is used to store approximate decimal numbers with a floating point.
  7. real: This data type is similar to float, but with a smaller range and precision.
  8. date, time, and datetime2: These data types are used to store date and time values.
  9. xml: This data type is used to store XML data.
  10. bit: This data type is used to store a single bit (0 or 1) of data.

SQL Server Extract Year Month Or Day From DATE Data Type

In Microsoft SQL Server, you can use the YEAR, MONTH, and DAY functions to extract the year, month, and day from a DATE data type.

Here’s an example of how you can use these functions:

SELECT
  YEAR(date_column) AS year,
  MONTH(date_column) AS month,
  DAY(date_column) AS day
FROM your_table;

This will return the year, month, and day for each row in the your_table table. The output will be in the form of integer values.

You can also use these functions to extract the year, month, and day from a DATETIME data type.

It doesn’t matter if you put attribute with date data type or text in appopriate format:

e.g.:

SELECT YEAR(LOAD_DATE) FROM CUSTOMER;

SELECT YEAR('20191218');

Extract MONTH Or DAY In SQL Server

Analogously, for extract MONTH and DAY you can use:

MONTH ( your_date ) 
DAY ( your_date )

Summary

I hope it helped you and now you know how to extract year from date sql.

If you will have any question about how to extract year from date in SQL Server for instance please don’t hesitate to ask me.

SQL Server extract year month or day from date, extract year from date sql, extract year sql
Could You Please Share This Post? 
I appreciate It And Thank YOU! :)
Have A Nice Day!

How useful was this post?

Click on a star to rate it!

Average rating 4.9 / 5. Vote count: 751

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?