0

Im making a simple table in sql where i need to specify certain times. I use datatype time. I enter time like this: 5:38 It results in this: 05:38:00.0000000

How do I get rid of all the zeros? Do I have to change data-type?

juergen d
  • 201,996
  • 37
  • 293
  • 362
user2915962
  • 2,691
  • 8
  • 33
  • 60
  • Why do you care about how the time is formatted in the DB? – Yair Nevet Mar 02 '14 at 11:37
  • Which `DBMS` are you using? – Hamidreza Mar 02 '14 at 11:39
  • Well, I just thinks it looks pretty bad in my "timetable"..Im just trying to learn SQL som im probably missing something.. – user2915962 Mar 02 '14 at 11:39
  • Microsoft management studio – user2915962 Mar 02 '14 at 11:40
  • 1
    which sql? MS SQL or MySQL? Even then its the way database stores the time. Just format it when you make a selection. – ray Mar 02 '14 at 11:40
  • 2
    You can't change the way a data type is stored in the DB. You can only change the way it is represented to you in your select queries. – juergen d Mar 02 '14 at 11:42
  • 1
    http://stackoverflow.com/questions/7710449/how-to-get-time-from-datetime-format-in-sql – ray Mar 02 '14 at 11:46
  • As juergen's comment points to, you need to get used to the being a difference between the information stored and how it's represented. Internally, date/time/timestamps type are usually some sort of incrementing counter - this makes comparisons **really fast** (because they compare the internal value). Types like `Decimal` in C# are usually multiple integral variables, but are given a decimal point only on string output. You should be able to hide the representation in your db client for basic stuff. – Clockwork-Muse Mar 02 '14 at 12:40

1 Answers1

0

To summarize the comments from other users. Possible solutions are:

  1. Create a time(0) (see the sqlfiddle)
  2. Use a convert to convert the results to your desired representation (also in sqlfiddle).

Sample: 

select convert(char(5), t, 108)
from   tableName
Patrick Hofman
  • 153,850
  • 22
  • 249
  • 325