Getting Minute from DATEPART Function

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 2000 Forums

Transact-SQL (2000)

Getting Minute from DATEPART Function

Author

Topic

michaelsync
Starting Member

2 Posts

Posted - 2007-03-21 : 08:56:09

When I run the following T-SQL in Query analyzer, I got "23", "1" and "0".

"


PRINT  DATEPART(HH,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT  DATEPART(MM,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT  DATEPART(SS,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))

"23" and "0" are correct but "1" is incorrect since actual minute in my statment is 00.

Could anyone explain me why I get "1" instead of "0" for minute?
Thanks in advance.

Thanks,
Michael Sync
Blog: http://michaelsync.net

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 08:58:22

Yes.

quote:
Datepart Abbreviations
year
yy, yyyy

quarter
qq, q

month
mm, m

dayofyear
dy, y

day
dd, d

week
wk, ww

weekday
dw

hour
hh

minute
mi, n

second
ss, s

millisecond
ms

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 09:02:54

PRINT DATEPART(HOUR,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT DATEPART(MINUTE,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT DATEPART(SECOND,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))

PRINT DATEPART(HH,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT DATEPART(MI,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT DATEPART(S,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))

PRINT DATEPART(HOUR,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT DATEPART(N,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT DATEPART(SS,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-03-21 : 09:08:32

"Could anyone explain me why I get "1" instead of "0" for minute?"

As Peso has pointed out "MM" is Month, not Minute. We long since stopped using teh shorthand codes, they are too ambiguous:

ms millisecond
mi minute
mm month
m month

y dayofyear
yyyy year
yy year

dy dayofyear
dw weekday
dd day
d day

and we now use the full-length parameter labels:


PRINT  DATEPART(Hour,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT  DATEPART(Minute,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))
PRINT  DATEPART(Second,CONVERT(CHAR(8),CONVERT(DATETIME,'1900-01-01 23:00:00'),8))

Kristen

michaelsync
Starting Member

2 Posts

Posted - 2007-03-21 : 11:37:21

Oh.. I got the point..
Thank you so much for your help..

Thanks,
Michael Sync
Blog: http://michaelsync.net

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources