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

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. {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)

SQL DateTime, SQL Function, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Find Length of Text Field
Next Post
SQLAuthority.com News – iGoogle Gadget Published

Related Posts

458 Comments. Leave new

  • Hello,
    How can I specify a condtion to achieve the required date where
    “required date= current date -13months”

    Reply
    • RequiredDate = (sysdate – interval ’13’ month)

      Reply
      • Thank you for your time Garethpn. But I get an error. sysdate is not a recognized function. and if it is GETDATE() -interval’13’month, I have an error “incorrect syntax near month”

      • Sysdate only works in ORACLE. In SQL Server you need to use getdate()

        RequiredDate = dateadd(month,-13,getdate())

  • Praveen Acharya
    March 21, 2012 1:34 pm

    thanks pinal for ur help. can u pls help in some question….is SQL/PLI is similar to ODBC????also to JDBC?????

    Reply
  • hi

    i want to fetch the data according to particular time i select. suppose i want to fetch data between date 10 to 15 and for particular hr 16:00 then what is the suitable query for that. kindly help me

    Reply
  • hi I am Ram,

    I want to get last one year records …how can i get it..

    i used this following SP. to get it.

    alter procedure dev_GetAllWeekCharts_M
    @userID bigint,
    @StartDate datetime

    as

    BEGIN

    declare @EndDate datetime = dateadd(Year, -1, @StartDate)

    SELECT [weight],convert(varchar,Updateddate,101)[Updateddate] into #tempWeight from dev_UserAlerts_WeightTracking
    where userid = @Userid and UpdatedDate between @EndDate and @StartDate
    ;with aweek(day,weight) as
    ( select @StartDate as day,’0′ as weight
    union all
    select day – 1,’0′ as weight from aweek
    where day > @EndDate )

    select aweek.day as WeightDate ,case when #tempWeight.weight IS NULL then ‘0’ else #tempWeight.weight end as weight
    from aweek left outer join #tempWeight on #tempWeight.Updateddate = aweek.day order by WeightDate asc
    Drop table #tempWeight

    END

    exec dev_GetAllWeekCharts_M 4,’2012/04/02′

    But is show error message as
    (5 row(s) affected)
    Msg 530, Level 16, State 1, Procedure dev_EvolveSystems_GetAllWeekCharts_M, Line 14
    The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

    thanks.
    RAM

    Reply
  • Please tell me how get MIN Amount Date between 2011-12-31 and 2012-03-31 below table

    Date Amount
    2011-11-01 4,500.00
    2012-02-15 5,300.00
    2012-03-16 6,000.00

    Reply
  • hi can u help me plz i want to get all data from current date to last date till which schedule hv been created from schedule tabel that contain date column

    Reply
  • Mirwais Ahmadzai
    April 14, 2012 2:42 am

    dear frindes please, who can help me i want to get only year from the database and then subtract a value from it. plz looking for your kind help.

    Reply
  • @Mirwais,

    In order to just extract the year, use command

    select DATEPART(year,getdate())

    Example of how to substract value from it:

    select 2014 – DATEPART(year,getdate())

    Reply
  • i wan to retrive data from existing table but i can’t compare datefield’s date(18-APR-2012) to current month

    Reply
  • vishal pandey
    April 30, 2012 5:43 pm

    vishal pandey
    dear friend i am also best make sql softwere

    Reply
  • hello,
    I have tried
    DECLARE @X int
    SET @X= DAY(getdate())
    and the same things for year and month

    and I increase DAY and I want to make that values in datetime (yyyy-mm-dd)
    how can I do that

    Reply
  • actually my problem is that I want to get date from my existing table and increase day then format new datetime

    Reply
  • Hi sir i want to fetch date from system but the condition is that
    I want to fetch yesterdays and current date before 5:30 pm and the date must be not in 2nd and Third saturday and other sundays

    Reply
  • HI..
    I working on a oracle db but my db creashed suddenly,how can i take backup of my db.plz help

    Reply
  • Sachin Karche
    June 1, 2012 5:52 pm

    Thanks …giving first page in Google search

    Sachin Karche

    Reply
  • Dinesh Vishe
    June 15, 2012 6:15 pm

    How insert cuurect date time in MSSQL table ??
    Anyone Plz help…

    Reply
  • This syntax is used to display only date (2011-11-01 00:00:00 )
    eg syntax:

    convert(varchar(10),Date_Da,101) as Date_Da

    This Date_Da that field is our table date field

    Reply
  • Anand Upadhyay
    July 9, 2012 7:30 pm

    sir;
    i am facing a problem .i want to find current date and time of indian format .
    when we host the application then time is not in good format as like pm to change in am
    sir, plz help me

    Reply
  • I’m wanting to find records that are older than 85 days from today. When I put the following code: Category_3 < dateadd(day, -85, getdate())
    Once excuted I then get the following error:
    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Error Code: 242

    Any ideas on how to fix this?

    Reply
    • I fixed my own issue. Should have had:
      Category_3 < Convert(Varchar(10),dateadd(day, -85, getdate()))

      However now would love to sort these results in DESC order. ORDER BY Category_3 DESC doesn't work (I assume because of the Varchar). Any ideas?

      Reply
  • lashinipriya
    July 30, 2012 3:06 pm

    I want month and year-wise Reports so how to pass Month and Year Parameters in Same Stored Procedure..Please Help Me as soon as

    Reply

Leave a ReplyCancel reply

Exit mobile version