Skip to main content

Convert Datetime to Date

Summary: 
From this post you will learn how to use date and time conversion function.

SQL Server provides a number of options you can use to format a date/time string.

How to get different SQL Server date formats:
  • Use the date format option along with CONVERT function.
  • To get YYYY-MM-DD use SELECT CONVERT(varchar, getdate(), 23).
  • To get MM/DD/YYYY use SELECT CONVERT(varchar, getdate(), 1).
  • Check out the chart to get a list of all format options.
Convert datetime to date using the CONVERT() function

This statement uses the CONVERT() function to convert a datetime to a date:

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

expression - Any valid expression.

data_type - The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

length - An optional integer that specifies the length of the target data type, for data types that allow a user specified length. The default value is 30.

style - An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

Below is a list of formats and an example of the output.  The date used for all of these examples is "2006-12-30 00:38:54.840".

Date Only Formats

Format # : 1
Query: select convert(varchar, getdate(), 1)
Sample: 12/30/06

Format # : 2
Query: select convert(varchar, getdate(), 2)
Sample: 06.12.30

Format # : 3
Query: select convert(varchar, getdate(), 3)
Sample: 30/12/06

Format # : 4
Query: select convert(varchar, getdate(), 4)
Sample: 30.12.06

Format # : 5
Query: select convert(varchar, getdate(), 5)
Sample: 30-12-06

Format # : 6
Query: select convert(varchar, getdate(), 6)
Sample: 30 Dec 06

Format # : 7
Query: select convert(varchar, getdate(), 7)
Sample: Dec 30, 06

Format # : 10
Query: select convert(varchar, getdate(), 10)
Sample: 12-30-06

Format # : 11
Query: select convert(varchar, getdate(), 11)
Sample: 06/12/30

Format # : 12
Query: select convert(varchar, getdate(), 12)
Sample: 061230

Format # : 23
Query: select convert(varchar, getdate(), 23)
Sample: 2006-12-30

Format # : 101
Query: select convert(varchar, getdate(), 101)
Sample: 12/30/2006

Format # : 102
Query: select convert(varchar, getdate(), 102)
Sample: 2006.12.30

Format # : 103
Query: select convert(varchar, getdate(), 103)
Sample: 30/12/2006

Format # : 104
Query: select convert(varchar, getdate(), 104)
Sample: 30.12.2006

Format # : 105
Query: select convert(varchar, getdate(), 105)
Sample: 30-12-2006

Format # : 106
Query: select convert(varchar, getdate(), 106)
Sample: 30 Dec 2006

Format # : 107
Query: select convert(varchar, getdate(), 107)
Sample: Dec 30, 2006

Format # : 110
Query: select convert(varchar, getdate(), 110)
Sample: 12-30-2006

Format # : 111
Query: select convert(varchar, getdate(), 111)
Sample: 2006/12/30

Format # : 112
Query: select convert(varchar, getdate(), 112)
Sample: 20061230

Time Only Formats

Format # : 8
Query: select convert(varchar, getdate(), 8)
Sample: 00:38:54

Format # : 14
Query: select convert(varchar, getdate(), 14)
Sample: 00:38:54:840

Format # : 24
Query: select convert(varchar, getdate(), 24)
Sample: 00:38:54

Format # : 108
Query: select convert(varchar, getdate(), 108)
Sample: 00:38:54

Format # : 114
Query: select convert(varchar, getdate(), 114)
Sample: 00:38:54:840

Date & Time Formats

Format # : 0
Query: select convert(varchar, getdate(), 0)
Sample: Dec 12 2006 12:38AM

Format # : 9
Query: select convert(varchar, getdate(), 9)
Sample: Dec 30 2006 12:38:54:840AM

Format # : 13
Query: select convert(varchar, getdate(), 13)
Sample: 30 Dec 2006 00:38:54:840AM

Format # : 20
Query: select convert(varchar, getdate(), 20)
Sample: 2006-12-30 00:38:54

Format # : 21
Query: select convert(varchar, getdate(), 21)
Sample: 2006-12-30 00:38:54.840

Format # : 22
Query: select convert(varchar, getdate(), 22)
Sample: 12/30/06 12:38:54 AM

Format # : 25
Query: select convert(varchar, getdate(), 25)
Sample: 2006-12-30 00:38:54.840

Format # : 100
Query: select convert(varchar, getdate(), 100)
Sample: Dec 30 2006 12:38AM

Format # : 109
Query: select convert(varchar, getdate(), 109)
Sample: Dec 30 2006 12:38:54:840AM

Format # : 113
Query: select convert(varchar, getdate(), 113)
Sample: 30 Dec 2006 00:38:54:840

Format # : 120
Query: select convert(varchar, getdate(), 120)
Sample: 2006-12-30 00:38:54

Format # : 121
Query: select convert(varchar, getdate(), 121)
Sample: 2006-12-30 00:38:54.840

Format # : 126
Query: select convert(varchar, getdate(), 126)
Sample: 2006-12-30T00:38:54.840

Format # : 127
Query: select convert(varchar, getdate(), 127)
Sample: 2006-12-30T00:38:54.840
Labels:

Popular posts from this blog

What is SQL Server?

SQL Server SQL Server is a relational database management system (RDBMS) developed by Microsoft. SQL Server supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. SQL Server supports ANSI SQL, which is the standard SQL (Structured Query Language) language. However, SQL Server comes with its own implementation of the SQL language, T-SQL. Why SQL Server? Microsoft SQL Server is a comprehensive database server and information platform offering a complete set of enterprise-ready technologies and tools that help people derive the most value from information at the lowest total-cost-of-ownership. Enjoy high levels of performance, availability, and security; employ more productive management and development tools; and deliver pervasive insight with self-service business intelligence (BI). A complete and integrated platform, Microsoft SQL Server brings it all together to get more value out of existing IT skills and asse
Read more

Download SQL Server Management Studio (SSMS)

Summary:  In this post, you will learn &  able to download  SSMS. About  SQL Server Management Studio ( SSMS ):  SSMS is Free!! SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts. Use SSMS to query, design and manage your databases and data warehouses, wherever they are - on your local computer, or in the cloud.   SSMS -  Prerequisites for Installation: SSMS supports the following 64-bit platforms when used with the latest available service pack: Supported Operating Systems: Windows 10 (64-bit) version 1607 (10.0.14393) or later Windows 8.1 (64-bit) Windows Server 2019 (64-bit) Windows Server 2016 (64-bit) Windows Server 2012 R2 (64-bit) Windows Server 2012 (64-bit) Windows Ser
Read more

Mastering Common Table Expression or CTE in SQL Server

WITH common_table_expression (Transact-SQL) Summary:  From this post you will learn about Common Table Expression or   CTE  in SQL Server using the with Clause.  About  CTE  Statement:  Specifies a temporary named result set, known as a common table expression (CTE). This is derived from a simple query and defined within the execution scope of a single SELECT , INSERT , UPDATE , DELETE or MERGE statement. A common table expression can include references to itself. This is referred to as a recursive common table expression.  Syntax: [ WITH <common_table_expression> [ ,...n ] ]   <common_table_expression>::=       expression_name [ ( column_name [ ,...n ] ) ]       AS       ( CTE_query_definition )   Arguments expression_name Is a valid identifier for the common table expression. expression_name must be different from the name of any other common table expression defined in the same WITH <common_table_expression> clause, but expression_name can be the same as the name
Read more