Date but not time

I would like to know how to do a select on a datetime field that will return to me only the Date but not the time. Please help out. Thanks.

I ran through all the built in date functions and couldn't find anything for this task. I still can't fathom why there isn't a date function for this simple but a must have option.

blumonde

Try this:

DateTime dt = DateTime.Now;

dt.ToShortDateString();

OR

dt.ToString("dd/MMM/yyyy");

|||

Try the link below for the DATEPART function. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms174420.aspx

|||

The T-SQL Convert function provides formatting functionality, e.g.:

CONVERT(CHAR(10),GETDATE(),101)

...would produce the current date in mm/dd/yyyy format.

See here or the BOL for more:

http://doc.ddart.net/mssql/sql70/ca-co_1.htm

That said, you may be better off doing the formatting/trimming the date on the application- rather than the database- server.

|||

You are probably better off just asking for the field, and displaying how you want.

I believe the format {0:d} will only display the date portion of a datetime field, and most asp controls have a format property if they can be databound.

|||

Thank you all for your help. The Convert function does the job with the Select statement but it won't work if I have the parameter "Distinct" in it. I can't use Datepart because it can only do either month, day or year. The option "{0:d}" has been used on the dropdown listbox for the datetime field; however, it is showing duplicated dates due to the diference of timestamp. My goal here is to be able to display unique dates on the dropdown list. In order to have that, I need to eliminate the timestamp. Any ideas anyone?

blumonde

|||I don't think its the best idea, BUT. If you use a nvarchar field and input just the date, then you can compare it. Otherwise i think you will run into problems because sql might always store a time with the date|||

Do this:

select distinct(cast(datediff(day, 0, MyDateColumn) as datetime))
from MyTable

order by
cast(datediff(day, 0, MyDateColumn) as datetime)

|||

Ian Bost:

Do this:

select distinct(cast(datediff(day, 0, MyDateColumn) as datetime))
from MyTable

order by
cast(datediff(day, 0, MyDateColumn) as datetime)

Thank you Ian Bost. That did it.

Could you explain on how your code got rid of the timestamp.

blumonde

|||

Yeah it's just a little SQL trick

datediff returns the number of time boundaries between two specified dates. In this case I used "day" as the interval that I want.

So if I did:

select datediff(day, 0, getdate())

It would return 38910

this is the # of days between time 0 (January 1, 1900 aka the base date) and today's date.

Now internally SQL stores datetime values as two 4-byte integers. The first integer represents the # of days before or after the base date (jan 1, 1900). The second integer represents the time of day as milliseconds past midnight.

Anyway the datediff function gives me the # of days since jan 1 1900, and when you cast that integer to a datetime you will arrive at the date you want without the time portion.

|||

Ian Bost:

Yeah it's just a little SQL trick

datediff returns the number of time boundaries between two specified dates. In this case I used "day" as the interval that I want.

So if I did:

select datediff(day, 0, getdate())

It would return 38910

this is the # of days between time 0 (January 1, 1900 aka the base date) and today's date.

Now internally SQL stores datetime values as two 4-byte integers. The first integer represents the # of days before or after the base date (jan 1, 1900). The second integer represents the time of day as milliseconds past midnight.

Anyway the datediff function gives me the # of days since jan 1 1900, and when you cast that integer to a datetime you will arrive at the date you want without the time portion.

Thanks for the clarification. I really appreciate it. Everyone is so helpful here. Thank you all.

blumonde