Checking Time

Question

Checking Time

Steve Jones - SSC Editor

SSC Guru

Points: 736173
More actions
November 21, 2022 at 12:00 am

#4107003

Comments posted to this topic are about the item Checking Time

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

The correct answer is... none of the above because because the method is unreliable. After 25 days, 20 hours, 31 minutes, and 23.649 seconds (-2147483649 ms), the DATEADD() will fail due to an INT overflow condition.

NEVER use the method posted.

As a bit of a sidebar, you have to wonder what MS was thinking when they made a DATEDIFF_BIG() but didn't make a DATEADD_BIG(), even for 2022! 🙁 As a wise man once said, "ZOMGWTFBBQ!!!11!11!shiftone!!!!!"

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

david.gugg SSCertifiable Points: 6096 More actions · Answer 2

lol, funny one @jeff Moden

Be still, and know that I am God - Psalm 46:10

chgn01 SSCarpal Tunnel Points: 4354 More actions · Answer 3

SELECT DATEADD(SECOND, -sample_ms/1000, GETDATE()), *

FROM sys.dm_io_virtual_file_stats ( 1,1)

or

SELECT DATEADD(mi, -sample_ms/1000000, GETDATE()), *

FROM sys.dm_io_virtual_file_stats ( 1,1)

This reply was modified 2 years ago by chgn01.

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

chgn01 wrote:

SELECT DATEADD(SECOND, -sample_ms/1000, GETDATE()), * FROM sys.dm_io_virtual_file_stats ( 1,1)
or
SELECT DATEADD(mi, -sample_ms/1000000, GETDATE()), * FROM sys.dm_io_virtual_file_stats ( 1,1)

Heh... instead of picking through the manure trying to figure out what the horse was thinking, let's walk around to the front of the horse and ask the horse! 😀

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Another way to do it (and guaranteed to be instance specific) is...

SELECT create_date FROM sys.databases WHERE name = 'tempdb';

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

chgn01 SSCarpal Tunnel Points: 4354 More actions · Answer 5

this system table may give diff. date time.

--------------------------------------
;-)“Everything has beauty, but not everyone sees it.” ― Confucius