How Do I Remove The Seconds From Time Format Table

Question

1.00/5 (1 vote)

See more:

Hello,

I would like to know how I can remove the second from a return value in database. So instead of HH:MM:SS, I would like HH:MM. how do I achieve this when querying the table using SELECT?

Thanks a lot.

Posted 25-Jun-15 3:44am

Awoldeselassie

Add a Solution

3 solutions

Solution 1

https://msdn.microsoft.com/en-us/library/ms187928.aspx[^]

This is what you need to know....

Posted 25-Jun-15 3:55am

TenmanS14

Comments

Awoldeselassie 25-Jun-15 10:08am

That has all the formats except the answer to my question. Thanks for suggesting anyways.

Solution 3

You should return the original field to your code and have the code dictate the relevant format. So if you're using .net then use ToString("XYZ") on your datetime variable where XYZ is the format of your choice. You should leave matters of display and formatting to your presentation layer, the database shouldn't be making these decisions.

Posted 25-Jun-15 4:25am

F-ES Sitecore

Comments

Awoldeselassie 25-Jun-15 10:32am

using C#, im executing a stored procedure. so I needed to change it on the procedure rather than code behind.

F-ES Sitecore 25-Jun-15 10:43am

Data types should be respected, and when you start to deviate from that you get complications that often occur in bugs etc. You have a date field that becomes a string when it leaves your database, so any attempt to treat that data as its native type (date) becomes incredibly error prone.

CHill60 25-Jun-15 11:07am

Actually F-ES Sitecore is spot on. Your question was not tagged as using C# so I gave you a solution based on the display being within SQL. Formatting of results - especially if it takes the entity away from its original datatype - should only be done at the point of display

Awoldeselassie 25-Jun-15 11:09am

I see,.. it is only for viewing purpose, and the data is not going to be used once it leaves the table. also, when you have to send gridview as confirmation email, you want the data to take as little space as possible. so HH:MM:SS:mm is not good for viewing or space. I wont be using the data in the code behind again, so shouldn't be a problem if format changes to whatever, because that data is not coming back.

Awoldeselassie 25-Jun-15 11:17am

Also, query is complicated time dependent query and pulls data out of four tables, I see you point, but I cant see an easier way of implementing this in the code behind rather than stored procedure. You answer worked for me CHILL60.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2015-06-25T04:10:00

Solution 2

Further to solution 1 - unfortunately there is no out-of-the box format that will do that for you, you will also need substring[^]

e.g.

SQL

-- Data is a DateTime column
select SUBSTRING(CONVERT(varchar(30), GETDATE(), 121), 1, 16)

or

SQL

-- Data is a Time column
DECLARE @T AS TIME = '10:57:43'
select SUBSTRING(CONVERT(varchar(30), @T, 121),1,5)

Posted 25-Jun-15 4:10am

CHill60

Comments

Awoldeselassie 25-Jun-15 10:31am

Spot on, although I didn't need to declare, Thanks

CHill60 25-Jun-15 11:05am

That was just so I had something to test it against - I should have left it out of the solution