0

I am using datatime datatype to set date in database but it gives the complete data time and hrs and minutes I want to set only date in the format of dd-mm-yyyy

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Shaikh Nadeem
  • 105
  • 2
  • 4
  • 10
  • cast(getDate() As Date) from this post: http://stackoverflow.com/questions/923295/how-can-i-truncate-a-datetime-in-sql-server – evgenyl Mar 27 '13 at 06:56
  • *I am using SQL Server 9.0.3042* --> that is SQL Server **2005**, not 2008 ... see [SQL Server versions and builds](http://sqlserverbuilds.blogspot.ch/) – marc_s Mar 27 '13 at 07:31

4 Answers4

6

Perhaps use date instead of datetime?

nkvu
  • 5,551
  • 2
  • 16
  • 12
  • 1
    Yes. See [the date and time types of SQL Server](http://msdn.microsoft.com/en-us/library/ms186724.aspx). – Jeppe Stig Nielsen Mar 27 '13 at 06:58
  • there is no such data type available for date only in sqlserver2008 – Shaikh Nadeem Mar 27 '13 at 07:01
  • 1
    Hi, the `DATE` data type was introduced in SQL Server 2008 (c.f. the link I posted which is set to SQL Server 2008 as the version). I don't think this is it but what edition are you running? Also, what [compatibility mode](http://msdn.microsoft.com/en-us/library/bb933794(v=sql.100).aspx) is your database in? (it wouldn't be in SQL 2005 compatibility mode by any chance? If you're in 2005 compatibility mode then the `DATE` data type won't be available - it needs to be in 2008 compatibility mode) – nkvu Mar 27 '13 at 07:04
  • @ShaikhNadeem: you're said in your original post in the tags that you **are** using SQL Server **2008** .... – marc_s Mar 27 '13 at 07:07
  • 1
    SQL Server 2008 does indeed have the Date datatype – Moho Mar 27 '13 at 07:08
  • i am using sql server 9.0.3042, is it the same compatible sql server 2008? or can u help me to set compatibility to 2008? – Shaikh Nadeem Mar 27 '13 at 07:19
  • That's SQL Server 2005 (please see [this](http://support.microsoft.com/kb/321185) link). In that case then yes there is no `DATE` data type. The other answers which SO users have provided to your question, and which don't rely on the `DATE` data type, will likely be more relevant to you, then – nkvu Mar 27 '13 at 07:22
2

a) in 2008 and later, there is a Date datatype you can use

b) if you can't change the datatype, you can convert a datetime to date to 'truncate' the time. e.g.: convert( date, GETDATE() ) will return '2013-03-27', and when inserted into the datetime column, it will have time 00:00:00.000

c) if you simply don't want to display the time component when converting to a string, use convert with the format of your choosing: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Praveen Nambiar
  • 4,852
  • 1
  • 22
  • 31
Moho
  • 15,457
  • 1
  • 30
  • 31
1

Just use DATE datatype instead ofDATETIME

More on DATE

SQL FIDDLE DEMO

Praveen Nambiar
  • 4,852
  • 1
  • 22
  • 31
0

try this 

  DateTime dt = DateTime.Now;
  SqlCommand cmd = new SqlCommand("INSERT into myTable(dateColumn) Values (@pDate)", conn);
  cmd.Parameters.AddWithValue("@pDate", dt.Date.ToShortDateString());
XTGX
  • 114
  • 9