1

Possible Duplicate:
How to return the date part only from a SQL Server datetime datatype

I need to fetch only date from sql server eventhough i have used date datatype i am getting time also. but in database n_strat_date column has only date value not the time value.

My table:

enter image description here

My Output:

enter image description here

Community
  • 1
  • 1
Nirmala
  • 91
  • 1
  • 8
  • 16

2 Answers2

0

Regardless of how you store the dates in SQL, if you load this data in C# DateTime values, they will always have a time part (which is 12:00:00 AM, unless otherwise specified).

Your problem here has nothing to do with SQL; it's a formatting issue in your application.

Concretely, if you had the DateTime value in a variable called startDate, instead of printing out to the form startDate or startDate.ToString(), use an explicit format, like this: startDate.ToString("M/d/yyyy").

Here's an online test to see how it works.

Cristian Lupascu
  • 39,078
  • 16
  • 100
  • 137
  • here i am filling dataset value to string strStartDate .so when i use as you suggestion its giving error. My code: string strStartDate = dsViewTrip.Tables[0].Rows[i][4].ToString("m/d/yyyy"); – Nirmala Dec 08 '12 at 08:46
  • 1
    Should I guess what error? Or can you give me a hint? – Cristian Lupascu Dec 08 '12 at 09:34
  • I got the result only date, when i was loading from dataset to string...... follow the code below string strStartDate = dataset.Tables[0].Rows[i][4].ToString().Substring(0,10); – Nirmala Dec 10 '12 at 09:28
  • @Nirmala that's unreliable; the output of `DateTime.ToString()` depends on the system's regional settings. – Cristian Lupascu Dec 10 '12 at 10:07
  • to apply the formatted ToString() overload you need to first cast that value to a `DatetTime`. So, try this instead: `strStartDate = ((DateTime)dataset.Tables[0].Rows[i][4]).ToString("M/d/yyyy")` – Cristian Lupascu Dec 10 '12 at 10:08
  • also, in the format string, the `M` is a capital one. If you use lowercase (`m`) you may get minutes instead of months. – Cristian Lupascu Dec 10 '12 at 10:09
0

try it, it will work select CONVERT(date,column_name) from table_name

Saleem Akhtar
  • 79
  • 6