SQL SERVER – Retrieve Current DateTime in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

There are three ways to retrieve the current DateTime in SQL SERVER. CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

SQL SERVER - Retrieve Current DateTime in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} currentdatetime

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.

Solarwinds

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. {fn Now()} can be used to print the current date and time every time that the report is produced.

If you run following script in Query Analyzer. I will give you the same results. If you see the execution plan there is no performance difference. It is the same for all the three select statements.

SELECT CURRENT_TIMESTAMP 
GO 
SELECT {fn NOW()} 
GO 
SELECT GETDATE() 
GO

Performance:
There is absolutely no difference in using any of them. As they are absolutely the same.

My Preference:
I like GETDATE(). Why? Why bother when they are the same!!!

Quick Video on the same subject about datetime

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Find Length of Text Field
Next Post
SQLAuthority.com News – iGoogle Gadget Published

Related Posts

458 Comments. Leave new

  • This is a very helpful article…Thanks PinalDave

    Reply
  • how to find currenttime in australia

    Reply
  • 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!

    Reply
  • hi, how to get yyyymmddhhmmss ??? without space and : between hour minutes and seconds
    Thanx

    Reply
    • 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),’:’,”)

      Reply
  • 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

    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())

    Reply
    • It should be

      SELECT * FROM TableName
      WHERE dateTimeMix >= dateadd(day, datediff(day,-1, getdate())),0) and
      dateTimeMix < dateadd(day, datediff(day,0, getdate())),0)

      Reply
  • 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

    Reply
  • Michael Pomeroy
    May 27, 2013 9:24 am

    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

    Reply
  • 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

    Reply
  • Dr. R. K. Kamboj
    September 26, 2013 12:37 am

    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?

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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…

    Reply
  • 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)

    Reply
  • 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

    Reply
  • Thanks for this!

    Reply
  • 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??

    Reply
  • 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”

    Reply
  • Thanks for the articel,actualyy it’s giving half and hour late to the current time

    Reply
  • Nikhil Kumar
    June 26, 2019 1:00 am

    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.

    Reply

Leave a Reply

Menu