There are three classic ways to retrieve the current datetime in SQL Server: CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}. There is also a fourth that arrived later and is the one I would reach for today.
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP is a nondeterministic function. Views and expressions that reference this column cannot be indexed. CURRENT_TIMESTAMP can be used to print the current date and time every time that the report is produced.
GETDATE()
GETDATE is a nondeterministic function. Views and expressions that reference this column cannot be indexed. GETDATE can be used to print the current date and time every time that the report is produced.
{fn Now()}
The {fn Now()} is an ODBC canonical function which can be used in T-SQL since the OLE DB provider for SQL Server supports them.
If you run the following script, all three give you the same answer, and the execution plan is the same for all three select statements.
SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GOPerformance: there is absolutely no difference in using any of them. They are the same.
My Preference: I like GETDATE(). Why? Why bother when they are the same!!!
The one this post was missing: SYSDATETIME
SQL Server 2008 added SYSDATETIME, and for anything written today it is the better choice. The reason is precision.
SELECT GETDATE() AS Getdate_datetime,
SYSDATETIME() AS Sysdatetime_datetime2;GETDATE returns a datetime, which stores time in steps of about three and a third milliseconds. It cannot hold anything finer, so the value you get back is rounded. Watch the last digit: a datetime can only ever end in 0, 3 or 7.
SYSDATETIME returns a datetime2, which holds up to a hundred nanoseconds. It is also one byte smaller than datetime at the default precision, so you are not paying for the accuracy.
For a report header, nobody cares. For a log table where you need to know which of two events happened first, that rounding will eventually merge two rows into the same timestamp and you will not be able to tell them apart.
The mistake that costs people a weekend
Every function above returns the time on the server, in whatever time zone the server happens to be in. It does not record what that time zone was.
So a row that says 14:00 is a row you can no longer place, once the server moves, the clocks go back, or a second server joins from another country. The data looks perfectly fine and is quietly ambiguous.
Store UTC, and convert when you show it to somebody.
SELECT GETUTCDATE() AS Utc_datetime,
SYSUTCDATETIME() AS Utc_datetime2,
SYSDATETIMEOFFSET() AS Local_with_offset;SYSDATETIMEOFFSET is the interesting one. It returns the time and the offset from UTC, so the value carries its own context and nothing is guessed later.
From SQL Server 2016 onwards you can convert properly by name, which handles daylight saving for you rather than making you remember it.
SELECT SYSUTCDATETIME() AT TIME ZONE 'UTC'
AT TIME ZONE 'India Standard Time' AS LocalTime;The first AT TIME ZONE says what the value already is, the second says what you want. You need both, and leaving the first one off is the usual mistake.
Just the date, without the time
A very common follow up, and there is a neat answer for it now.
SELECT CAST(GETDATE() AS DATE) AS Today;
You will still see the old trick in scripts, which is worth recognising when you meet it.
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) AS Today_OldWay;
Both work. The first one says what it means.
Which one should you actually use
SYSDATETIME for new code that records when something happened.
SYSUTCDATETIME for anything stored, and convert on the way out.
GETDATE when you are matching a column that is already a datetime, or writing something quick.
CURRENT_TIMESTAMP if you like standard SQL, since it is the one the standard defines and it moves to other database engines unchanged.
{fn NOW()} never, in new code. It is ODBC syntax from another era, and it survives only because nothing removed it.
Quick Video on the same subject about datetime
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
458 Comments. Leave new
This is a very helpful article…Thanks PinalDave
how to find currenttime in australia
Thanks yet again… PinalDave, I have used your teachings many times and well, never offered a thank you. So… Thank you. Anytime I do a query for SQL help, I check your links first! You have been most helpful so many times – so thanks for this one, and all of them!
hi, how to get yyyymmddhhmmss ??? without space and : between hour minutes and seconds
Thanx
This is formation issues that should be handled by your application. However here is the answer for sql
select convert(char(8),getdate(),112)+replace(convert(char(10),getdate(),108),’:’,”)
Hi Sir,
I m facing a problem that is:
I have two columns as CreateDate and ExpiryDate in ASPNETDB.MDF file, I want to put days between two date in DaysLeft Column.
I m using Visual Web developer 2005 express Edition. I m using the function
Datediff(Day,convert(datetime,CreateDate),Convert(Datetime,ExpiryDate)) in computed column specification
but failed. Please reply
I am creating an SSIS to export queried data to excel. I want to pull records from yesterdays date. Seems like the following should work but I get no return
SELECT * FROM TableName
WHERE dateTimeMix = dateadd(dd, -1, getdate())
It should be
SELECT * FROM TableName
WHERE dateTimeMix >= dateadd(day, datediff(day,-1, getdate())),0) and
dateTimeMix < dateadd(day, datediff(day,0, getdate())),0)
Hi Sir, can you please assist me I am trying to insert the current time in a timestamp field.
like
create table x ( a timestamp not null)
INSERT INTO x
VALUES CAST(current_timestamp AS TIMESTAMP)
This alone works
SELECT CAST(current_timestamp AS TIMESTAMP) but if I want to insert it into a table it does not work . I am aware that it converts it to binary on the table when it was successful
I am trying to convert to Month/Day/Year formant,
from Day/Month/Year format.
Will this statement work, or should it be 101 rather than 103 ???
SELECT WorkOrderID, Convert(varchar(12),ModifiedDate,103) FROM Production.Workorder
Hi All,
I wanted a query which will get records between current date and 20 days before also if i can group them to each day. Can any body help?
Regards,
Sachin
Hello, It is a very helpful page. Can anyone tell me how to retrieve only TIME part and DATE/TIME of Database of 2011 from database MS SQL Server 2000. If there is no field of TIME in the table, even then if someone is able to retrieve Time of data storing?
I want to
Create a table having fields are as follows
Create table emp
(
Empname varchar(50),
joining SystemDate,
sal int
)
Plz help me whats the actual Query to get systemdate in this table
Hello ,
i want to search txt file like as 13.03.2014_15.59. txt (current date and time ).
**********************************
bulk insert TmpStList
from ‘C:\PATH\ currentdate_time.txt ‘
with (fieldterminator = ‘,’, rowterminator = ‘\n’)
*************************************************** is what i want to do.
why i get this error:
“The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
The statement has been terminated.”
i want to insert datetime.now into sql where the type is also datetime
but somtimes its not working.. i get above error..
how can i insert datetime.now as datetime in sql… i want to check the datetime stored in sql with current datetime (it must be of the form dd/mm/yyyy hh:mm:ss am/pm or mm/dd/yyyy hh:mm:ss am/pm)
pls help me with the above problem…
You need to use unambiguous datetime format YYYYMMDD HH:MM:SS
The time is not server time but the time of the machine running the SQL (e.g. if you run the query on a SQL Management studio in one country and it connects to a server on another country – the time will be of the local machine – not the remote)
Hi! Congratulations for your blog is awsome!
One question:
if i want to setup the date of the database with a configuration file… how can i achieve this?
So for example if the configuration file says it is 12/12/12 getdate() retrieves that date…
thank youuu
Thanks for this!
Hello.. need your help.
i have SP like this:
Crearte PROC [dbo].[SP_CONVERT_DAY_TO_DATE_2] @DAY_CODE AS CHAR(2),@TARGET_DATE AS DATETIME OUTPUT
AS
SET DATEFIRST 1
DECLARE @TRX_DATE AS DATE
SET NOCOUNT ON
SELECT @TRX_DATE = TRX_DATE FROM MASTER_CALENDAR
WHERE D_CODE = @DAY_CODE
AND TRX_DATE BETWEEN GETDATE()-25 AND GETDATE()
SET @TARGET_DATE = ISNULL(@TRX_DATE,’01-01-1900′)
—-
PRINT @TARGET_DATE
but, the result always : ” Jan 01 1900 12:00AM”, whats wrong??
when i exec this SP like this: exec [SP_CONVERT_DAY_TO_DATE_2] 02,’2015-01-02′
the result always ” Jan 01 1900 12:00AM, but when 02 change to 31 the result is right, “Dec 31 2014 12:00AM”
may be MASTER_CALENDAR table doesn’t have entry for ’2015-01-02′
Thanks for the articel,actualyy it’s giving half and hour late to the current time
i see Select GetDate() as CurrentTime query running from app server to my database, and it is blocking other queries.
and it never released the lock unless i kill the session. could you please sugget why is that.
Never heard about such blocking. What is the wait resource?