preload preload

MSSQL Server – Extract date dd/mm/yyyy part from datetime

MSSQL Server don’t have a like operator. Like operator is really helpful to do the pattern matching on a column data especially when the column has a datetime part. It is a common requirement among the developers to extract the date part from the datetime column in the database for some logic flow. It was very easy to run that in MySQL with like operator i.e.

select * from temp_table where datetime_col like '10/06/2010%';

Now, to achieve the same result in MSSQL i.e. to extract the date part from datetime column, we can use the convert function provided in MSSQL.

Convert function in MSSQL converts the expression of one datatype to the another. The syntax for the MSSQL convert function is

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

The data_type is the target data type in which you want to convert the expression data type. The last parameter style is significant for datetime data types.

The style parameter is the key for the extraction of date part from the datetime data type column. It has some predefined values which allows you to convert the datetime in different standard date formats such as British, USA, German, Italian etc.

Now, lets make work it out. The following is the MSSQL query which extracts the date part from the datetime column and similar to the query which we wrote above for MySQL.

select * from temp_table where convert(varchar(10), datetime_col, 1) = '10/06/2010';

Dirty but good workaround although!!!

Liked the content? Then why not share with your pals

  • Leave a Reply

    * Required
    ** Your Email is never shared