SQL Select Query returning DATE without TIME inside SQL but i when i run my ASP project it keeps adding "12:00:00 AM"

Question

SELECT 
    Lname + ' ' + Ename  as Name, 
    DateOfBirth,  
    DATEDIFF(hour,dateOfBirth,GETDATE())/8766 AS Age 
FROM EmpTBL 
WHERE 
    DATEADD( Year, DATEPART( Year, GETDATE()) - DATEPART( Year, DateOfBirth), DateOfBirth) 
    BETWEEN CONVERT( DATE, GETDATE()) AND CONVERT( DATE, GETDATE() + 30);

The above query is what i use when i was testing inside the SQL it seems to work but when i add it to my ASP.net project the result is different, i already add "Convert(date, getdate()) " How to select date without time in SQL

Output from my project

Output from my SQL

Answer 1

When we fetch date from database to asp.net it's in Date-Time format not in Date so you can convert it to custom date as you want:

<%# Convert.ToDateTime(Eval("DateOfBirth")).ToString("yyyy/MM/dd")%> 

You can use any Format specifier/Custom Date and Time Format String according to your requirement.

Answer 2

change your query to

select CONVERT(VARCHAR(10),dateOfBirth,101) FROM EmpTBL  WHERE <>

Answer 3

You can use the CONVERT function of ASP.Net, you can convert into string then format it.

Some thing like adding .ToString("MM/dd/yyyy").

I Think this post can help you: String Format Date - C# or VB.NET

Answer 4

.net has no such datatype for date only. Your simply getting the default time in your datetime object.

Sql has date, time and datetime concepts so Sql can just show the date.

You can ignore the time in code or choose not to show the time on the UI by formatting it.

Answer 5

Use the DataFormatString property in the GridView. If you set it to {0:d}, you should just get a short date format.