How to Write a Query to Get Records with a Specific Date Format in SQL Server?

Question

Ravi Vishwakarma · Answer

To write a query to retrieve records with a specific date format in SQL Server, you typically use the CONVERT function to convert the date column to the desired format. Here’s a general approach:

Let's assume you have a table named YourTable with a column named DateColumn that stores dates in a specific format, say 'YYYY-MM-DD'.

SQL Server comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number

Here’s how you can write the query:

SELECT CONVERT(VARCHAR, DateOfBirth, 23)
FROM Users

In this query:

CONVERT(VARCHAR, DateColumn, 23) converts the DateColumn to a VARCHAR (string) in format 23, which corresponds to 'YYYY-MM-DD'.
'YYYY-MM-DD' is the specific format you're interested in.

Replace YourTable and DateColumn with your actual table and column names. Adjust the format code (23 in this case) according to the specific date format you are targeting. Here are some common format codes:

23 - 'YYYY-MM-DD'
101 - 'MM/DD/YYYY'
102 - 'YYYY.MM.DD'
120 - 'YYYY-MM-DD HH:MI'

Choose the appropriate format code based on your date format. This method allows you to filter records based on a specific date format effectively in SQL Server.

forum

How to Write a Query to Get Records with a Specific Date Format in SQL Server?

Can you answer this question?

1 Answers