datetime issue - 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

General SQL Server Forums

New to SQL Server Programming

datetime issue

Author

Topic

1821
Starting Member

18 Posts

Posted - 2008-07-16 : 07:43:29

Hi guys, I was just wondering how to display just the time part of a datetime data type?

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 07:45:12

That is a frontend decision.
Let your application format the datetime value.
It is faster and easier.

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

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-16 : 08:54:02

To get just the time part of a DateTime data type, the CONVERT function can be used. The CONVERT function explicitly converts an expression of one data type to another. The syntax of the CONVERT function is as follows:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

use this

SELECT CONVERT(VARCHAR(10), GETDATE(), 108) AS [CurrentTime]

If you want to include the millisecond (HH:MI:SS.MMM), the style to use is 114:

SELECT CONVERT(VARCHAR(13), GETDATE(), 114) AS [CurrentTime]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-16 : 09:00:04

or use SQL2008

raky
Aged Yak Warrior

767 Posts

Posted - 2008-07-16 : 09:10:25

quote:
Originally posted by elancaster

or use SQL2008

Em

I think there is no datetime datatype in sql server 2008

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-16 : 09:11:31

quote:
Originally posted by raky

quote:
Originally posted by elancaster

or use SQL2008

Em

I think there is no datetime datatype in sql server 2008

eh? we now have seperate date and time datatypes as well as datetime

Em

1821
Starting Member

18 Posts

Posted - 2008-07-16 : 09:43:55

Thanks, is there another style like 114 that just has HH:MM:SS?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 09:49:45

112, as Raky demonstrated earlier.

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

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-16 : 09:52:06

108 surely?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-16 : 09:55:50

Yes, 108 of course. 112 is another number in the States.

DATETIME is still available in SQL Server 2008. Also the new DATETIME2 together with DATE datatype and TIME datatype.
TIME datatype has seven settings. TIME(1), TIME(2) and so on depending on which detail you need.

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-07-16 : 09:58:58

Just remember that converting a DateTime to a VARCHAR for formatting means it no longer is a DateTime value; it will no longer sort or compare as a DateTime, and you cannot apply any further formatting or datetime "math" anywhere on the value. You should never convert all of your data to varchars just for formatting purposes, you should always format your output at the proper place. Your code is shorter and it is easier and much more flexible to simply specify a format string like "hh:mm:ss" at your client. You are binding your database code to your output when trying to format at the database layer, which is almost always a bad idea.

http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspx

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources