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