SQL SERVER – Tips from the SQL Joes 2 Pros Development Series – System and Time Data Types – Day 16 of 35

System and Time Data Types

Keeping track of date and time data points has always been a critical part of online transactional databases. For example, each sales invoice record needs a date-time stamp, as do systems which track quotes and customer contacts regarding sales opportunities.

Think of how many times during your workday that you rely on a date-time stamp as helpful metadata to sort or locate the latest information in a report or data source. Global organizations, in particular, have a need for their in-house communication, reporting, and collaboration tools to appropriately convey accurate date and time information in order to keep every part of the organization in sync.

Recap of DateTime Functions

GETDATE( ) and SELECT SYSDATETIME( ) both return the current date and time in your time zone. However, GETDATE( ) shows fractional seconds expressed in milliseconds (.333 second), and SYSDATETIME( ) shows fractional seconds expressed in nanoseconds (.3333333 second). SYSDATETIME( ) return similar results but their precisions differ.

What time is it right now in the UK? UTC is Coordinated Universal Time, formerly known as Greenwich Mean Time (GMT). (UTC is also known by the terms zulu time, world time, and universal time.)

SELECT GETUTCDATE( ) will show the current time expressed in terms of UTC. GETUTCDATE( ) is less precise than SYSUTCDATETIME ( ). However there is a UTC function that gets down to the nanoseconds, SYSUTCDATETIME( ). When we run all 4 of these functions together We see the two top times in my local time zone (in my case the Pacific time zone) and the two bottom times in UTC.

Question 16

Which one of the following functions will return the date and time in the current time zone to a precision of milliseconds?

GETDATE( )

SYSDATETIME( )

GETUTCDATE( )

SYSUTCDATETIME( )

Rules:

Please leave your answer in comment section below with correct option, explanation and your country of resident.
Every day one winner will be announced from United States.
Every day one winner will be announced from India.A valid answer must contain country of residence of answerer.
Please check my facebook page for winners name and correct answer.
Winner from United States will get Joes 2 Pros Volume 3.
Winner from India will get Joes 2 Pros Volume 3.
The contest is open till next blog post shows up at http://blog.sqlauthority.com which is next day GTM+2.5.

becuase option #1 GETDATE() and #2 SYSDATETIME() returns date and time value with respect to CURRENT time zone, however, option #1 returns in fractional seconds expressed in milliseconds,
option #2 returns in fractional seconds expressed in nanoseconds.

Option #3 GETUTCDATE() and #4 SYSUTCDATETIME() returns the date and time value in UTC format., so these are incorrect.

Correct answer is No. 1 because GETDATE( ) return the date and time in the current time zone to a precision of milliseconds. Option No. 2 does the same but with a precision of nanoseconds, so is incorrect. Option 3 and 4 are incorrect beccause they return the date and time expressed in terms of UTC.
Rene Castro
El Salvador

The answer is Getdate() since it displays the current time zone’s date and time to the milliseconds grain.

Option 1 : It gives the current time zone’s date and time to the precision of milliseconds.
Option 2 : It gives the current time zone’s date and time to the precision of 100 nano seconds.
Option 3 : It gives the UTC date and time to the precision of milliseconds.
Option 4 : It gives the UTC date and time to the precision of 100 nano seconds.

It displays the current time zone’s date and time with respect to the milliseconds format.

Option 1 GETDATE(): It returns the current date and time in your time zone. It shows fractional seconds expressed in milliseconds (.333 second).
Option 2 SYSDATETIME(): It returns the current date and time in your time zone. But it shows fractional seconds expressed in nanoseconds (.3333333 second).
Option 3 GETUTCDATE(): It will show the current time expressed in terms of UTC. GETUTCDATE( ) is less precise than SYSUTCDATETIME ( ).
Option 4 SYSUTCDATETIME(): It will show the current time expressed in terms of UTC and it gets down to the nanoseconds.

becuase option #1 GETDATE() and #2 SYSDATETIME() returns date and time value with respect to CURRENT time zone, however, option #1 returns in fractional seconds expressed in milliseconds,
option #2 returns in fractional seconds expressed in nanoseconds.

Option #3 GETUTCDATE() and #4 SYSUTCDATETIME() returns the date and time value in UTC format., so these are incorrect.

becuase option #1 GETDATE() and #2 SYSDATETIME() returns date and time value with respect to CURRENT time zone, however, option #1 returns in fractional seconds expressed in milliseconds,
option #2 returns in fractional seconds expressed in nanoseconds.

Option #3 GETUTCDATE() and #4 SYSUTCDATETIME() returns the date and time value in UTC format., so these are incorrect.

Asked : Function will return the date and time in the current time zone to a precision of milliseconds.

Explanation : Here we need to find out Current time zone in the above article it is very beautifully explained by pinal sir that GETDATE() will return current date and time in your time zone by default your time zine is said as default time zone though GETUTCDATE( ) returns the same in milliseconds but here our requirement is current time zone, i.e our zone.

Explanation: Option 3 and 4 provide date and time in Universal Time Coordinates (UTC) and option 2 provide date and time in current time zone but with precision of nanoseconds.
Option 1 provide date and time in current time zone to a precision of milliseconds.

Challenge:
Question 16
Which one of the following functions will return the date and time in the current time zone to a precision of milliseconds?
1. GETDATE( )
2. SYSDATETIME( )
3. GETUTCDATE( )
4. SYSUTCDATETIME( )

Correct Answer:
1. GETDATE( )

Explanation:
GetDate( ) will show return the current date and time in milliseconds. SysDateTime( ) will return the current date and time in nanoseconds. GetUTCDate( ) will return the Coordinated Universal Time in milliseconds.
SysUTCDateTime( ) will return the Coordinated Universal Time in nanoseconds.

Explanation:
Option 3 and 4 returns the UTC Date and Time, so they are not the correct options for this question. And Option 2 returns the date and time in local time zone but precision is in nanoseconds. So the correct answer is the option 1.

Explanation:
Option 3 and 4 returns the UTC Date and Time, so they are not the correct options for this question. And Option 2 returns the date and time in local time zone but precision is in nanoseconds. So the correct answer is the option 1.

Which one of the following functions will return the date and time in the current time zone to a precision of milliseconds?

GETDATE( )
SYSDATETIME( )
GETUTCDATE( )
SYSUTCDATETIME( )

Solution:

The right answer is Getdate().This function returns the current time zone or local date and time to the precision of milliseconds .

1 -> This function returns the current time zone date and time to the precision of milliseconds.
2 -> This function returns the current time zone date and time to the precision of 100 nano seconds or 10-7 seconds.
3 -> This function returns the Coordinated Universal Time (UTC) date and time to the precision of milliseconds.
4 -> This function returns the Coordinated Universal Time (UTC) date and time to the precision of 100 nano seconds or 10-7 seconds.

I think the correct answer is both No. 1 because GETDATE( ) return the date and time in the current time zone to a precision of milliseconds and No. 2 becauseSysDateTime does the same thing but with higher precision. Both are accurate to the millisecond.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.