time datatype sql server 2008 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

time datatype sql server 2008

Author

Topic

daytona steve
Starting Member

5 Posts

Posted - 2011-04-17 : 15:52:33

My first post, hope I've put this in the right place.

I have a small table with several fields, two of which are rawtime and handicap. I've selected the time datatype as these are elasped time hours:minutes:seconds.[nnnnnnn]. Using management studio, I changed the number from the default time(7) to time(2) in the hopes of reducing the number of digits to the right of the . [nnnnnnn] from 7 to 2 .[nn]. However, regardless of the number I choose, the table displays all 7, adding zeros to the entry.

My select statements are in asp.net and when I select the fields, it returns the full number hours:minutes:seconds.[nnnnnnn]. Since asp.net doesn't support the time datatype, normal datetime format rules do not apply so I've not been able to get rid of the extra digits in asp.net.

Any advice as to how to render this at 2 digits to the right of the . would be appreciated. Please answer at the third grade level, my experience in sql server is limited.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-17 : 17:44:16

SQL server is doing the right things - if you specify precision as 2, it will return the data with only 2 digits of precision for the milli-second part. It is .Net that is doing the default conversion with 7 digits for the milli-second part.

The equivalent .Net data type for SQL type Time is TimeSpan according to http://msdn.microsoft.com/en-us/library/bb675168.aspx. So you could do something like this in your .Net code (I tested this)


   TimeSpan ts = (TimeSpan)rdr[0]; // rdr is my SqlDataReader.
   DateTime d = new DateTime(ts.Ticks);
   Console.WriteLine(d.ToString("hh:mm:ss.ff"));

Alternatively you could format the TimeSpan object itself. There are examples here:http://msdn.microsoft.com/en-us/library/1ecy8h51.aspx (You will need to do each piece - hour, minute, second, milli second separately).

The other alternative is to format the time as a varchar in your SQL query - which many people on this forum who are experts would discourage you from doing, for a variety of very valid reasons.

daytona steve
Starting Member

5 Posts

Posted - 2011-04-17 : 20:04:06

I may not have expained this correctly or I may not understand your answer.

Using management studio, I have the time datatype set to Time(2). Once again, using management studio, despite the (2) setting, there are 7 digits to the right of the . 10:23:45.1234567. Regardless of the setting I get 7 digits looking at the column in management studio.

This is all prior to asp.net.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-17 : 20:17:28

I just did an experiment and it does not seem to show the behavior you are describing. This is the test that I ran:

DECLARE @t7 TIME, @t2 TIME(2), @t0 TIME(0), @dt datetime2;

SET @dt = '2011-04-17T20:10:19.5172345';

set @t7 = @dt;
SET @t2 = @dt;
SET @t0 = @dt;

SELECT @dt,@t7,@t2, @t0;

Here I am taking a datetime2 variable (@dt) and assigning it to three different time variables each with a different precision. In each case, the results I get is as I would expect - see below:


@dt                             @t7                     @t2             @t0
2011-04-17 20:10:19.5172345	20:10:19.5172345	20:10:19.52	20:10:20

Can you post code that demonstrates the incorrect behavior?

Thanks,
Sunita

daytona steve
Starting Member

5 Posts

Posted - 2011-04-17 : 22:58:31

I'm sorry I don't know how to post code from sql server. I'm working in management studio. There is a screen that has the fields, first column is the field name, second column is the data type. I have two field with data type Time. The defualt was Time(7). I changed the 7 to a 2. When I look at the table in the "edit top 200 rows" screen, the values in each of the two fields at 00:14:23.1230000.

Sorry to be such an idiot about this.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 07:15:45

You are using the graphical UI features of the SQL Server Management Studio (SSMS) to do this - which is perfectly fine. I was assuming that you were using T-SQL scripts.

I repeated what you described using the graphical tools, and I still can't reproduce the problem you are experiencing. Here are the steps that I did (starting with a brand new table).

1. In SSMS, in object explorer right-clicked on "Tables" under YourServerName->Databases->YourDatabaseName and selected New Table.

2. IN the new table edit window, typed in MyTime for Column Name and time(7) for the Data Type.

3. Under file menu selected "Save Table1". In the Choose Name window, gave it a name - MyTestTable.

4. Right clicked on Tables in object explorer and clicked refresh.

5. Found the "MyTestTable" under Tables in object explorer and selected edit Top 200 rows.

6. In the edit window typed in 7:30:20.1234567

7. Closed the edit window and then went back to edit top 200 rows to verify that the time is there exactly as I typed it.

8. Right clicked on MyTestTable in object explorer and selected "Design" to bring up the design window.

9. Under Data Type changed "time(7)" to "time(2)"

10. From the menu bar, clicked File->Save MyTestTable.

11. Right-clicked on MyTestTable in object Explorer and selected edit top 200 rows.

Now the edit window that comes up shows 07:30:20.1200000

So the time was rounded off from 7 digits to 2 digits.

Can you repeat this procedure and see if you get the same results? If you do, compare that with what you are doing. Perhaps you are not saving the change from time(7) to time(2)?

daytona steve
Starting Member

5 Posts

Posted - 2011-04-18 : 09:23:17

You've captured the issue perfectly and it looks like it is my misunderstanding as to how this works. My incorrect expectation was that if the datatype was set to (2) the results of 7:30:20.1234567 would be 7:30:20.12 and what it actually does is rounding to 7:30:20.1200000 .... so it is working correctly. Where I need to go now is to reduce the displayed data on the web page to 7:30:20.12 either by some fuctionality in sql server or by some fucntionality in asp.net which doesn't support the time datatype and as such the dataformat string options are useless.

Thank you for the education on sql server.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 10:01:07

My sense is that you are better of doing that formatting at the client side. My earlier post (at Posted - 04/17/2011 : 17:44:16) was describing how you would do it if you are using C# and .Net 4.0

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-04-18 : 10:20:23

What you're seeing is an artefact of the row editor for SSMS, which is broken in many ways.

Rather write and run queries, then you will see exactly what SQL is returning, not what mangling SSMS is then doing.

--
Gail Shaw
SQL Server MVP

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 10:36:54

quote:
Originally posted by GilaMonster

What you're seeing is an artefact of the row editor for SSMS, which is broken in many ways.

Rather write and run queries, then you will see exactly what SQL is returning, not what mangling SSMS is then doing.

--
Gail Shaw
SQL Server MVP

If you write a T-SQL query the results do show up with the correct precision, with no trailing zeros.

However, if you send exactly the same query to the server via ADO.Net, the object returned into .Net has the trailing zeros. That is the same behavior that you see in the SSMS graphical row editor.

daytona steve
Starting Member

5 Posts

Posted - 2011-04-18 : 10:42:59

To sunitabeck:

Thank you for the code. I'll give it a try, I'm at 3.5 C# but can easlily upgrade it to 4.0.

To Gilamonster... I've typically done that but I was looking for a short cut to the answer since asp.net doesn't support the time datatype and would require some fancy coding as supplied above rather than just using a normal asp.net format string like {0:t}.

You'd think since sql server is Microsoft and asp.net is Microsoft they could get more in sync. Really thought it would be a feature in asp.net 4.0. Seems a little like the Nissan wheel plant making wheels that won't fit a Nissan without an adapter.

Thank you for the comments.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-18 : 11:13:03

You may not need .Net 4.0; If you have .Net 3.5 SP1 or later, that would be enough. http://msdn.microsoft.com/en-us/library/bb675168(v=VS.90).aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources