remove date from date time sql server

1First thing's first, if your dates are in varchar format change that, store dates as dates it will save you a lot of headaches and it is something that is best done sooner rather than later. The problem will only get worse.
2
3Secondly, once you have a date DO NOT convert the date to a varchar! Keep it in date format and use formatting on the application side to get the required date format.
4
5There are various methods to do this depending on your DBMS:
6
7SQL-Server 2008 and later:
8
9SELECT  CAST(CURRENT_TIMESTAMP AS DATE)
10SQL-Server 2005 and Earlier
11
12SELECT  DATEADD(DAY, DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), 0)
13SQLite
14
15SELECT  DATE(NOW())
16Oracle
17
18SELECT  TRUNC(CURRENT_TIMESTAMP)
19Postgresql
20
21SELECT  CURRENT_TIMESTAMP::DATE
22If you need to use culture specific formatting in your report you can either explicitly state the format of the receiving text box (e.g. dd/MM/yyyy), or you can set the language so that it shows the relevant date format for that language.
23
24Either way this is much better handled outside of SQL as converting to varchar within SQL will impact any sorting you may do in your report.
25
26If you cannot/will not change the datatype to DATETIME, then still convert it to a date within SQL (e.g. CONVERT(DATETIME, yourField)) before sending to report services and handle it as described above.

1SELECT CONVERT(varchar(8), GETDATE(), 108) AS TimeOnly
2CONVERT(VARCHAR(7),@AppDate,111) date year only

1SELECT CONVERT(VARCHAR(5),[DATE COLUMN],108) AS TIME