Geek Answers Handbook

Remove time from sql

Please, can any of you suggest me how I can remove part of the time from sql dates. I already read 100 articles and most of them deleted the time, but left 00:00:00 and I don’t want, I also know that I can delete these ZEROs by converting to varchar, but unfortunately I can’t change date column type it should be a date type, not a string

Please inform

thanks

+4
source share
9 answers

The datetime column in the database will always contain both the date part and the time part. SQL Server 2008 introduces types that store only date or only time. But, as long as the column is of type datetime, you have to accept that there is a temporary part, and you can just ignore it in your application (using appropriate formatting).

You can add a validation constraint to the table to ensure that all entries in the column always have the same part of the time (for example, 00:00:00).

+4
source

SQL Server The DateTime type is the date and time in SQL 2005, you do not have just a date type field, you cannot remove the time component and still have a DateTime type field. Your options are the ones you have outlined: convert to (n) varchar and remove the time component, leave it as a DateTime and accept that it has a time component. Why do you want to remove the time component, what problem will be solved for you.

In addition to your comment below, the database is not where you should form the date rows that will be displayed in your GridView. You display layer objects at the display level, in this case in a gridview. You must use the format string in your data when binding data to a gridview. The following are examples.

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>
+2
source

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

Is this a gridview of ASP.NET? If so, then an example is here .

+2
source

try it

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

+1
source 
 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 remember to save 00:00:00 to your desk and delete some of the time in
  • Return the date and time in grid format or in your code.
+1
source

If you can upgrade to SQL2008, it has a separate time command.

I see, you say you want to save it as a datetime object, as I see, if you cannot update it until 2008, then you are stuck with zeros

0
source

You cannot remove the temporary part from the datetime datatype tag.

The only thing you can do is pass it to the date or varchar data type before exiting or set the temporary part to 00:00:00 before inserting / updating.

Example:

statement:

 select cast(getdate() as date)

returns only date

0
source

What are you trying to do?

Why don't you care if the database saves the exact time or 00:00:00?

When you request this field (inside or outside the database), you can ignore the time value and only show the date ...

Say again what you are trying to do ... I believe that it will be easier to help you.

0
source

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

This will update your table. Hope this is what you are looking for.

103: Date and time format as dd / mm / yyyy.

0
source

More articles:


All Articles