How to get DATE from DATETIME column?

Question

I want to get date from DateTime column.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
SelectCommand="SELECT [name], [book_id], [book_name], [taken_date], [last_date], [renewed_date], [status], [comment], [fine_amount] FROM [library] WHERE ([admn_no] = @admn_no)">
<SelectParameters>
   <asp:ControlParameter ControlID="TextBox1" Name="admn_no" PropertyName="Text" 
   Type="String" />
</SelectParameters>
</asp:SqlDataSource>

From the above these [taken_date],[last_date],[renewed_date] columns showing date and time

(eg: 4/14/2015 12:00:00 AM).

How do I display only date

(eg: 4/14/2015)?

Solution

you can use CAST(column_name AS DATE) for Sql-server 2008 and above version

SELECT [name], [book_id], [book_name], 
cast([taken_date] as date) as [taken_date], 
cast([last_date] as date) as [last_date], 
cast([renewed_date] as date) as [renewed_date], 
[status], [comment], [fine_amount] 
FROM [library] WHERE ([admn_no] = @admn_no)

EDIT 1

for earlier version before sqlserver 2008 you can do it like

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

so the whole query will go like this

SELECT [name], [book_id], [book_name], 
CONVERT(VARCHAR(10),[taken_date],111) as [taken_date], 
CONVERT(VARCHAR(10),[last_date],111) as [last_date], 
CONVERT(VARCHAR(10),[renewed_date],111) as [renewed_date], 
[status], [comment], [fine_amount] 
FROM [library] WHERE ([admn_no] = @admn_no)

EDIT 2

for formatting your date from yyyy/dd/mm to dd/mm/yyyy you can change to 101 rather than 111

SELECT CONVERT(VARCHAR(10),GETDATE(),101)

so query will be

SELECT [name], [book_id], [book_name], 
CONVERT(VARCHAR(10),[taken_date],101) as [taken_date], 
CONVERT(VARCHAR(10),[last_date],101) as [last_date], 
CONVERT(VARCHAR(10),[renewed_date],101) as [renewed_date], 
[status], [comment], [fine_amount] 
FROM [library] WHERE ([admn_no] = @admn_no)

Comments

[Reshma]

while using the above code showing output as " 2015/04/14". I want to display it as "04/14/2015"

[Reena]

instead of 111 use 101 in CONVERT(VARCHAR(10),[last_date],111)