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()
GOPerformance:
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
[youtube=http://www.youtube.com/watch?v=BL5GO-jH3HA]Reference: Pinal Dave (https://blog.sqlauthority.com)
458 Comments. Leave new
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?
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.