| Author |
Topic |
|
Sarah63777
Starting Member
8 Posts |
 Posted - 2007-01-25 : 10:04:45
|
| I need to store the time part of datetime in 24 hour format. when i do 'select * from table' i want it to show the time in 24 hour format. I do not want to have to convert it everytime i want it displayed. is there any way to make all times in the database/table to be in 24 hour? |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-25 : 10:11:13
|
| If this is specific to single table, why don't you wrap conversion inside a view?Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
Sarah63777
Starting Member
8 Posts |
Posted - 2007-01-25 : 10:12:46
|
| hmm thats actually a good idea if there is no way to actually store it....is there a way to store it? if no i will do that. you mean just make a view based on my main table, do the conversion there and then anytime i want the data, just get it from the view instead of the table right? |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-01-25 : 10:20:02
|
| Well, SQL Server stores datatime in its internal format and you can always convert it in the format you want. I don't see any point of storing the data just for the formatting sake. More better approach is to do all the formatting stuff in the front-end.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Sarah63777
Starting Member
8 Posts |
Posted - 2007-01-25 : 10:22:04
|
| I realize people say its better to format on the front end but I my question is how to format it in the actual table. It would make more sense to store it in the table in 24 hour time to avoid ambiguity. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-25 : 10:28:53
|
| If you store "just the time" in a DATETIME datatype, in SQL Server, it will be given a default "date" component of 01-Jan-1900.I think its a great shame that SQL 2005 didn't add DATE and TIME datatypes, but they didn't, so we are stuck with DATETIME only.Storing the data in a DATETIME datatype column allows you to sort it, compare it and manipulate it "chronologically".You can format it with CONVERT(varchar(12), MyDate, 114) - or using some other conversion style. But as you said, it is better to do that at the Front End so that the value retains its "datatype" so that the application can manipulate it without having to first parse it back to a Date/Time datatypeExamples:SELECT CONVERT(datetime, '01:02:03')SELECT CONVERT(varchar(12), CONVERT(datetime, '01:02:03'), 114)Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-25 : 10:33:05
|
quote:
Originally posted by Sarah63777
I realize people say its better to format on the front end but I my question is how to format it in the actual table. It would make more sense to store it in the table in 24 hour time to avoid ambiguity.
Datetime is stored internally in a format that is unambiguous, and it cannot be changed. It is only the way that you are displaying data that is ambiguous. The CONVERT function can be used to convert it to many different formats, so you should read about that in SQL Server Books Online.You can find links to more info about datetime here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762CODO ERGO SUM |
|
|
|
Sarah63777
Starting Member
8 Posts |
Posted - 2007-01-25 : 10:44:07
|
| ok thanks, i guess ill do that. I was told that in oracle there is a setting to make it appear as 24 hour format in the table so i assume there was a way with sql server. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-25 : 11:11:13
|
| "i assume there was a way with sql server"Can't speak for Oracle, but in SQL Server the data is stored internally in an unambiguous format, as MVJ described, and to view it in a specific format you have to apply some sort of conversion to a string (and as mentioned above this is better done client-side normally)Kristen |
|
|
|
Sarah63777
Starting Member
8 Posts |
Posted - 2007-01-25 : 11:41:29
|
| ok Thanks! I'm using a view to convert it. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-01-25 : 15:19:59
|
| Add a calculated column like thisALTER TABLE <YourTableNameHere> ADD TimeOnly AS CONVERT(VARCHAR, <YourDateTimeColumnNameHere>, 108)Peter LarssonHelsingborg, Sweden |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2007-01-26 : 09:15:41
|
| that some clever thinking Peso using a calculated column brilliant solution ? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-26 : 09:25:58
|
quote:
Originally posted by Jim77
that some clever thinking Peso using a calculated column brilliant solution ?
Calculated columns are not an entirely painless solution.Your code runs into a number of issues with the settings of ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, and NUMERIC_ROUNDABORT. There are also issues with reindexing in maintenance plans. I find them generally more trouble than they are worth.I would use a view.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-26 : 11:53:16
|
| "I find them generally more trouble than they are worth."Me too. To add to MVJ's list: I've also had problems with calculated columns with Views onto the underlying table which I needed to INSERT/UPDATE through, even with Instead of Triggers in place ...Now I wouldn't normally do that, of course, but we implemented a "change of heart" on column names, created a VIEW for backward compatibility, and the one table that had a Calculated Column was a nightmare ...Kristen |
|
|
|
|
Time format