How to cast a datetime format to date as string in SQL Server

Question

I am struggling to find a solution for the below issue.

date1 = 31-08-2017 12:10:00

I want to cast it as string and need to take date (31-08-2017) alone.

This is my SQL statement:

select * 
from table_name  
where cast(date1 as varchar) = '2017-08-30'

Here '2017-08-30' is string; when I ran the above select command it's showing o records as date1 is varchar but time also is included.

Can anyone tell me how to split date column alone as a string?

Answer 1

this will help you :-

select * from table_name  WHERE CONVERT(varchar(23), [YourDateColumn], 121)= '2017-08-30 00:00:00.000'

above will accept both date and time

if you want to use only date then try this :-

   select * from table_name  WHERE CONVERT(varchar(10), [YourDateColumn], 20)= '2017-08-30 00:00:00.000'

Answer 2

If your date1's type is DateTime, Try this,

select * 
from table_name  
where CAST(date1 AS DATE) = CAST('2017-08-30' AS DATE)

Answer 3

This will help you.

select * 
from table_name   
Where CONVERT(VARCHAR(100),date1,102) = '2017.08.30'

date1 is column name value= '30-08-2017 12:10:00'

Output will be 30-08-2017