remove time from sql

Question

Hi Guys,

Please can any ove suggest me how i can remove time part from sql dates. I have already read 100s of articles and most of them remove the time but leaves 00:00:00 behine which i don't want also i know that i can remove these ZERO's by doing a convert to varchar but unfortunately i cannot change the type of the date column it has to be date type instead of string

Please advise

Thanks

Answer 1

Try this

select cast(convert(char(11), getdate(), 113) as datetime)

Answer 2

SELECT convert(varchar, getdate(), 101)

OR

Declare @datetime datetime
set @datetime = getdate()
SELECT convert(varchar, @datetime, 101)

DateTime Formats

EDIT:

• SQL server 2008 has this functionality, if you can upgrade.
• Just make sure to save 00:00:00 in your table and remove time part in
• Return datetime and format it in your grid or in your code.

Answer 3

If you could upgrade to SQL2008, that has a separate time command.

I see you say you want to keep it as a datetime object, so as far as I can see if you can't upgrade to 2008 then you are stuck with the zeros

Answer 4

A datetime column in the database will always contain both a date portion and a time portion. SQL Server 2008 introduces types that store only date or only time. But, so long as the column is of type datetime, you'll have to accept that the time portion exists, and you can just ignore it in your application (by applying suitable formatting).

You can add a check constraint to the table to ensure that all entries in the column always have the same time portion (e.g. 00:00:00).

Answer 5

A SQL server DateTime type is a date and a time in SQL 2005 there is no date only field type, you cannot remove the time component and still have a field that is a DateTime type. Your options are the ones you have outlined: Convert to (n)varchar and remove the time componenet, leave it as a DateTime and accept that it has a time component. Why do you wish to remove the time component, what problem would this solve for you.

Further to your comment below, the database is not where you should be formating you Date strings to display in your GridView. You do display layer things in the display layer, in this case in the gridview. You need to use a format string in your data when data binding to the gridview. Yee examples below.

BoundField:

<columns>
  <asp:BoundField headertext="CreationDate" dataformatstring="{0:dd-MM-yyyy}" //rearrange these letters as necessary
       datafield="CreationDate"  />
</columns>

TemplateField

<itemtemplate>
    <asp:Label id="Label1" runat="server" Text='<%# Bind("CreationDate", "{0:M-dd-yyyy}") %>'>
    </asp:Label>
</itemtemplate>

Answer 6

Hi,

You can't remove time part from thr datetime datatype field.

Only thing you can do is to cast it to the date or varchar datatype before output or to set time part to 00:00:00 before insert/update.

Example:

the statement:

select cast(getdate() as date) 

returns only date

Answer 7

What are you trying to do?

Why do you care if the database is saving the exact time or 00:00:00?

When you query this field(inside or outside the DB) you can ignore the time value and show only the date...

Again, say what you are trying to do... I believe it will be easier to help you.

Answer 8

You need to apply a format string to your gridview column to only display the date part. An example of such a string would be dd MMMM yyyy

Is this an ASP.NET gridview? If so there is example code here.

Answer 9

Update mytable set mydatetime=convert(datetime,convert(char(10),mydatetime,103),103)

This will update your table. Hope that is what your looking for.

103: Format datetime as dd/mm/yyyy.