Open In App

Numeric and Date-time data types in SQL Server

Last Updated : 15 Sep, 2020
Improve
Improve
Like Article
Like
Save
Share
Report

MS SQL Server supports a wide range of data types. There are a few more important data types that are included in the article. In this article, we will cover numeric SQL server data types and different date-time data types. Let’s discuss one by one.

  1. bit :
    A bit is the smallest unit of a computer system. A bit can be either 0 or 1. The bit datatype can take NULL values as well.

    Syntax –

    column_name bit; 

    A bit can take up to 8 bytes of storage while 2 bits can take up to 16 bits and the cycle continues.

  2. int :
    A data type that can store integer values(positive and negative). The storage size is up to 8 bytes(-2⁶³ to 2⁶³-1). It is sub-categorized into tinyint, int, smallint, bigint. They can be used according to the number of bytes that can be stored.

    • (i). bigint –
      A numeric integer datatype that has the maximum storage of 8 bytes.(-2⁶³ to 2⁶³-1). It can store positive and negative values as well. It can be used for storing huge numbers.



      Syntax –

      column_name bigint; 
    • (ii). int –
      A numeric integer datatype having a storage size of 4 bytes.

      Syntax –

      column_name int; 
    • (iii). smallint –
      A numeric integer type that stores 2 bytes of data.

      Syntax –

      column_name smallint; 
    • (iv). tinyint –
      A numeric datatype that stores 1 byte.

      Syntax –

      column_name tinyint; 

      For example, a student roll number in a class table can be assigned as follows.

      rollnumber int;  

      SQL server Numeric Data Types Table :

      Numeric Data Type Integer size (In Bytes)
      bit  Value(0, 1 or NULL)
      tinyint
      smallint
      int
      bigint
      decimal(p,s) 5 to 17 
      numeric(p,s) 5 to 17 
      smallmoney
      money
      float(n) 4 or 8 
      real
  3. decimal :
    A data type that can store decimal values. This datatype can be used for storing percentage values.

    Syntax –

    column_name decimal(precision, scale) 

    For example,

    percentage(4,3)  

    Precision is a term used for describing the number of digits to be stored from left to right while the scale is a term used for storing the number of digits after the decimal point.

    Take the instance of e-commerce where the product delivery date and time are to be stored in the database. For such cases, there are few data types supported by MS SQL SERVER:

  4. date :
    It stores the date in the format of yyyy-mm-dd.

    Syntax –

    date 
  5. time :
    It stores the time based on 24 hours clock.

    Syntax –

    time 
  6. datetime2 :
    It stores the date and time as well in the format of yyyy-mm-dd hh:mm: ss.

    Syntax –

    datetime2 

    There are data types that can store money, unique identifiers, XML data, and much more. However, in the future versions of SQL Server, some data types will be removed for some reason. Make sure to use the data types that are available in the SQL Server. Different types of date-time data types table in the SQL server as follows:

    Data type Size (In Bytes)
    datetime
    datetime2 6 to 8 
    smalldatetime
    date
    time 3 to 5
    datetimeoffset 8 to 10 
    timestamp Unique no.

Similar Reads

How to Write a SQL Query For a Specific Date Range and Date Time?
In SQL, some problems require us to retrieve rows based on their dates and times. For such cases, we use the DATETIME2 datatype present in SQL. For this article, we will be using the Microsoft SQL Server as our database. Note - Here, we will use the WHERE and BETWEEN clauses along with the query to limit our rows to the given time. The pattern of s
2 min read
Various String, Numeric, and Date & Time functions in MySQL
A function is a special type of predefined command set that performs some operation and returns a single value. Functions operate on zero, one, two or more values that are provided to them. The values that are provided to functions are called parameters or arguments. The MySQL functions have been categorized into various categories, such as String
3 min read
Configure SQL Jobs in SQL Server using T-SQL
In this article, we will learn how to configure SQL jobs in SQL Server using T-SQL. Also, we will discuss the parameters of SQL jobs in SQL Server using T-SQL in detail. Let's discuss it one by one. Introduction :SQL Server Agent is a component used for database task automation. For Example, If we need to perform index maintenance on Production ser
7 min read
SQL SERVER | Bulk insert data from csv file using T-SQL command
In this article, we will cover bulk insert data from csv file using the T-SQL command in the SQL server. And will also cover the way is more useful and more convenient to perform such kind of operations. Let's discuss it one by one. Introduction :Sometimes there is a scenario when we have to perform bulk insert data from .csv files into SQL Server
3 min read
Update Date Field in SQL Server
The UPDATE statement in SQL is used to update the data of an existing table in the database. We can update single columns as well as multiple columns using the UPDATE statements as per our requirement. With this article, we will learn how to Update the Date Field in SQL Server. In this article, we will be making use of the Microsoft SQL Server as o
2 min read
How to Find Day Name From Date in SQL Server?
There are multiple ways to find a day name from a date in SQL Server. Below we will a few methods to find the day name from any date from the past to the future: DATENAME() FunctionFORMAT() FunctionMethod 1: Use DATENAME() Function This function in SQL Server is used to find a given part of the specified date. Moreover, it returns the output value
2 min read
SQL SERVER – Input and Output Parameter For Dynamic SQL
An Input Parameter can influence the subset of rows it returns from a select statement within it. A calling script can get the value of an output parameter. An aggregate function or any computational expression within the stored process can be used to determine the value of the output parameter. A parameter whose value is given into a stored proced
3 min read
How to Get Current Date and Time in SQL?
There are many built-in functions to get the current date and time in SQL. In this article, we will discuss how to get the current date and time in SQL with examples. SQL Get Current Date and TimeThree in-built SQL functions to fetch current date and time are: SQL GETDATE() FunctionSQL CURRENT_TIMESTAMP() FunctionSQL SYSDATETIME() FunctionSQL GETDA
2 min read
Date and Time Functions in PL/SQL
Date and Time Functions in PL/SQL are useful for date and time operations like extracting date components, performing date arithmetic, and converting between date formats. Date and Time Functions in PL/SQLThere are many useful Date and Time functions in PL/SQL. A table of such functions along with their description is given below: S.noFunctionDescr
4 min read
SQL Query to Check if Date is Greater Than Today in SQL
In this article, we will see the SQL query to check if DATE is greater than today's date by comparing date with today's date using the GETDATE() function. This function in SQL Server is used to return the present date and time of the database system in a ‘YYYY-MM-DD hh:mm: ss. mmm’ pattern. Features: This function is used to find the present date a
2 min read
Article Tags :