get only date from date variable sql server

Question

Date Format

enter image description here

Problem: I just want date - no time.

SQL statement:

SELECT SubjectIndex, LetterNo, DateOfIssue 
FROM [CircularKeeper]  
ORDER BY DateOfIssue DESC

After removing the time part I want it to be ordered descending.

I am binding the data using a SqlDataSource to a listsview and displaying the date in a label control.

Possible duplicate : http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype — Alex, Feb 24 '12 at 08:42
possible duplicate of [Best approach to remove time part of datetime in SQL Server](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server) — gbn, Feb 24 '12 at 08:44

AdaTheDev · Accepted Answer · 2012-02-24T08:53:17.783

3

You can do it a number of ways, in 2008 I'd go with:

SELECT SubjectIndex,LetterNo, CAST(DateOfIssue AS DATE) AS DateOfIssue
FROM [CircularKeeper]  
ORDER BY DateOfIssue DESC

As there is now a DATE data type - if you don't actually store times in that field in the table, then you might want to consider changing the datatype from DATETIME to DATE instead - saves the CASTing like this and uses less space (3 bytes instead of 8).

You could also just do this in the UI by returning the DATETIME as you currently are, and making sure you format the dates appropriately before rendering.

Update: I would recommend returning the DATE as a DATE from the query - so return the data in it's strongly typed form. If for presentation purposes you need it formatted a certain way, I'd recommend applying the formatting in your presentation logic.

edited Feb 24 '12 at 08:53

answered Feb 24 '12 at 08:42

AdaTheDev

142,592
28
206
200

actually i am using DATE but still i get the time part :( – user1150440 Feb 24 '12 at 08:45
@user1150440 - in that case, you need to format the data returned into the format you want in the code that's binding/rendering the data to produce the output. You could return a formatted string from the query, but IMHO it's usually best to return the data as-is and leave the formatting down to the presentation layer – AdaTheDev Feb 24 '12 at 08:48
@user1150440: that is a *display format* problem, not a SQL problem – gbn Feb 24 '12 at 08:49
how can i fix this?? kindly help me. – user1150440 Feb 24 '12 at 08:51
also i want the date in dd/mm/yyyy format ...kindly guide me – user1150440 Feb 24 '12 at 08:53
`SELECT SubjectIndex,LetterNo,CONVERT(CHAR(11),DateOfIssue,106) AS DateOfIssue FROM [CircularKeeper] ORDER BY DateOfIssue DESC` this converts the date but ODER BY dosent work as it is converted to varchar...how can i make the order by work?? – user1150440 Feb 24 '12 at 08:59
If you're binding the data to an ASP.NET gridview, see: http://forums.asp.net/t/1001482.aspx/1 - if not, please update your Question with details on how the data is rendered – AdaTheDev Feb 24 '12 at 08:59
@AdaTheDev i have updated the question ...kindly give it a look – user1150440 Feb 24 '12 at 09:04
`` – user1150440 Feb 24 '12 at 09:09

score 0 · Answer 2 · answered Feb 24 '12 at 08:43

You can use Formats like below in your select statement

Option - 1

select Convert(varchar, GETDATE(), 101) --02/23/2012
select Convert(varchar, GETDATE(), 102) --2012.02.23
select Convert(varchar, GETDATE(), 103) --23/02/2012
select Convert(varchar, GETDATE(), 104) --23.02.2012
select Convert(varchar, GETDATE(), 105) --23-02-2012

Option - 2

select CAST(GETDATE()as date) --2012-02-23

C# Code

DateTime dt;
string Temp1 = "Your Date";
if (DateTime.TryParse(Temp1, out dt))
{
     // If it is a valid date
     string date = dt.ToShortDateString();
     string time = dt.ToShortTimeString();
}

*never* use varchar... http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server/1177529#1177529 — gbn, Feb 24 '12 at 08:45

get only date from date variable sql server

2 Answers2