Katmai - date time format - 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

Other SQL Server 2008 Topics

Katmai - date time format

Author

Topic

blackbox320
Starting Member

10 Posts

Posted - 2007-06-15 : 12:10:15

In Katmai, is date and time two different datatype or a single datatype as in previous versions?

vishwamukh

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-15 : 15:13:21

Checked katmai books online, same as in current versions.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-15 : 15:50:19

it's supposedly to be separated in RTM but i seriously doubt it.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 00:17:01

So, still we can expect "How do I show only date without Time?" questions

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-16 : 08:47:01

quote:
Originally posted by madhivanan

So, still we can expect "How do I show only date without Time?" questions

Madhivanan

Failing to plan is Planning to fail

you not so lucky for now..

Chirag

http://chirikworld.blogspot.com/

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-06-16 : 08:53:17

didnt tried out.. but google.. gave me this link..

http://msmvps.com/blogs/robfarley/archive/2007/06/11/date-and-time-data-types-in-sql-server-2008.aspx

Chirag

http://chirikworld.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-16 : 09:12:43

quote:
Originally posted by chiragkhabaria

didnt tried out.. but google.. gave me this link..

http://msmvps.com/blogs/robfarley/archive/2007/06/11/date-and-time-data-types-in-sql-server-2008.aspx

Chirag

http://chirikworld.blogspot.com/

That seems cool

Madhivanan

Failing to plan is Planning to fail

blackbox320
Starting Member

10 Posts

Posted - 2007-06-17 : 15:58:29

Thanks for your replies. May be someday it happens!

vishwamukh

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-06-18 : 03:04:56

well chirik as rob says in the end they're not available in June CTP of Katmai

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2007-07-03 : 05:36:55

Yeah, that's right.

There will still be good cause for taking care when casting datetime fields to date, as:

cast(myfield as date) = @thedate

is unlikely to be handled as nicely by indexes as:

myfield >= @thedate and myfield < dateadd(day,1,@thedate)

I'm pleased my post helped settle your argument though. Sorry for not noticing before now.

Rob

Rob Farley
President - Adelaide SQL Server User Group
http://msmvps.com/blogs/robfarley

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 19:47:39

July CTP test code

DECLARE	@a time
SET @a = current_timestamp

select @a

01:46:25.4500000

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 19:51:21

[code]SELECT
CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'
,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS
'smalldatetime'
,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS
'datetime2'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS
'datetimeoffset';[/code]
[code]
Data type Output
time 12:35:29. 1234567
date 2007-05-08
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:29.123
datetime2(7) 2007-05-08 12:35:29.1234567
datetimeoffset(7) 2007-05-08 12:35:29.1234567 +12:15[/code]

E 12°55'05.25"
N 56°04'39.16"

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2007-08-01 : 19:54:36

Yes, it's great to see that the July CTP includes these!

Rob

Rob Farley
President - Adelaide SQL Server User Group
http://msmvps.com/blogs/robfarley

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-01 : 20:15:17

[code]Data type Format Range Accuracy Storage size (bytes) User-defined fractional second precision Time zone offset
time hh:mm:ss[.nnnnnnn] 00:00:00.0000000 through 23:59:59.9999999 100 nanoseconds 3 to 5 Yes No
date YYYY-MM-DD 0001-01-01 through 9999-12-31 1 day 3 No No
smalldatetime YYYY-MM-DD hh:mm:ss 1900-01-01 through 2079-06-06 1 minute 4 No No
datetime YYYY-MM-DD hh:mm:ss[.nnn] 1753-01-01 through 9999-12-31 0.333 second 8 No No
datetime2 YYYY-MM-DD hh:mm:ss[.nnnnnnn] 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 nanoseconds 6 to 8 Yes No
datetimeoffset YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC) 100 nanoseconds 8 to 10 Yes Yes[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-08-02 : 03:46:25

"datetime
datetime2"

Do they just let the office junior pick these names?

So we have "small" for, errmmmmm .... "small" precision, and "2" for Big precision. Great!

A bit like the change of TEXT to varchar(MAX) in SQL 2005 ...

Kristen

rob_farley
Yak Posting Veteran

64 Posts

Posted - 2007-08-02 : 04:41:55

Well, Oracle have char and char2, compared to Microsoft's char and nchar. So I've joked that datetime2 should be ndatetime. I don't tend to explain the Oracle connection, but people who have used Oracle chuckle a little.

Rob Farley
President - Adelaide SQL Server User Group
http://msmvps.com/blogs/robfarley

Kristen
Test

22859 Posts

Posted - 2007-08-02 : 05:41:54

"ndatetime"

Priceless

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-02 : 07:00:56

tinydatetime or bigdatetime?

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-08-02 : 08:26:48