A common question – I often get from Oracle/MySQL Professionals:
“What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?”
Here is a common question I often get from SQL Server Professionals:
“What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?”
Very simple question but have showed up so frequently that I feel like to write about it.
Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine.
You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server (Reference Link). CURRENT_TIMESTAMP is an ANSI SQL function, whereas GETDATE is T-SQL implementation of the same function. Both of them derive value from the operating system of the computer on which SQL Server instance is running.
Above discussion prompts another question – in this case, what should one use GETDATE or CURRENT_TIMESTAMP?
Well, this is indeed tricky and interesting question. I think I am very comfortable using the GETDATE () so I will go to use it but a matter of the fact there is no right or wrong answer. If you want to follow ancient saying “When in Rome, do as the Romans do”, I suggest using the GETDATE (), or continue using CURRENT_TIMESTAMP.
With that said, there is one very important property we all need to keep in mind. If you use CURRENT_TIMESTAMP while creating an object, they are automatically converted to GETDATE() and stored internally. To illustrate what I am suggesting here is the example –
Create a table using the following script
CREATE TABLE [dbo].[TestTable](
[Cold2] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD DEFAULT (CURRENT_TIMESTAMP) FOR [Cold2]
GO
Now go to SSMS and generate the script for the table and you will notice following syntax.
CREATE TABLE [dbo].[TestTable](
[Cold2] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TestTable] ADD DEFAULT (GETDATE()) FOR [Cold2]
GO
You can notice that SQL Server have automatically converted CURRENT_TIMESTAMP to GETDATE(). I guess this gives us an idea how they behave. Now go ahead and make your choice! Do let me know which one will you use CURRENT_TIMESTAMP or GETDATE () in the comments area.
Reference: Pinal Dave (https://blog.sqlauthority.com)
26 Comments. Leave new
GETDATE() – using it since I learned about GETDATE()
No need to use Current_Timestamp as per your suggestion.
Well, after a very good Discussion I felt GETDATE() is Better for me since i am much familiar with that… Any How as in Discussion CURRENT_TIMESTAMP automatically converted to GETDATE().
When Both Mean the Same …. Then What’s The Purpose of Current_Timestamp when getdate() is already there..
Hi Current_Timestamp is the Anci SQL function, this one is converted to GetDate() using SQL engine.
GetDate() is the one I will live me throughout my life
when ever possible we should have to follow ANSI standard,
because Its easy to migrate database from sql server to oracle or others.
I was using (+) for join in oracle but one big database have taken my too much time because of these type of non ANSI code.
so right now i am trying to use ANSI standard when ever possible
like,
coalesce instead of isnull()/nvl()
inner/outer join instead of (+)/(*) etc…
now I got one another point current_timestamp instead of getdate()
:)
I have always used getdate(), ever since I learned you could use that to define a field that defaults to the current date/time. It’s also easier to type (being all on the left hand, on the top two rows, and lowercase). It’s also slightly clearer to me what it will do (though I know they do the same thing).
Hi Pinal Dave..
How can i know Sqlserver is ready or not…
i.e while i am creating connection to sql server if sql server is not ready we will get exception… so first i want to know is sql server is ready to accept request or not… how to know it….
Good example, i will go with ur suggesion
A good SQL progammer will always write ANSI/ISO Standard code. The Standard also includes CURRENT_DATE while T-SQL needs CAST (CURRENT_TIMESTAMP AS DATE) instead.
That rewrite of code from Standard to dialect needs to be corrected by Microsoft as soon as possible. Everytime I see getdate() I have flashbacks to the 1970’s and UNIX on a 16-bit minicomputer.
SQL Server doesn’t recognize CURRENT_DATE
Interesting; so there’s no performance advantage then (I suppose outside of typing getdate() being shorter in typing length)?
I would prefer going the ANSI standard way, i.e. with CURENT_TIMESTAMP.
Personally I switched from using CONVERT to CAST although MS SQL Server internally transforms a CAST into a CONVERT ).
The reason I go with ANSI standard is because the standard is “constant”, meaning the code written in the standard is ensured to work as expected & designed for untill a newer version of the standard is released,
Please help me to find a solution for this problem
I have a database that stores login/logout time for a particular employee.
Create table EmpMaster
(
empid int,
checktime datetime,
chektype int // 1-checkin 2-checkout
)
Insert into EmpMaster values(2,’2013-01-03 9:30:00.00′,1)
Insert into EmpMaster values(2,’2013-01-03 10:30:00.00′,2)
Insert into EmpMaster values(2,’2013-01-03 11:30:00.00′,1)
Insert into EmpMaster values(2,’2013-01-03 12:00:00.00′,2)
Insert into EmpMaster values(2,’2013-01-03 14:30:00.00′,1)
Insert into EmpMaster values(2,’2013-01-03 16:00:00.00′,2)
i want to find out how many hours a particular employee was checked in
eg
empid hours
2 7
Hi Thejus,
Script for your query is as below –
————————————————————————————————
SELECT A.empid, DATEDIFF(hh,A.checktime, B.checktime) WorkHrs
FROM
(
SELECT empid, MIN(checktime) checktime FROM dbo.EmpMaster
WHERE chektype = 1
GROUP BY empid
)A
INNER JOIN
(
SELECT empid, MAX(checktime) checktime FROM dbo.EmpMaster
WHERE chektype = 2
GROUP BY empid
)B ON A.empid = B.empid
————————————————————————————————
Please check and revert!
SELECT A.empid, sum(datediff(mi,a.checktime,b.checktime))/60
FROM
(
SELECT empid, checktime, ROW_NUMBER() over (partition by empid order by checktime) as row_cnt
FROM dbo.EmpMaster
WHERE chektype = 1
)A
INNER JOIN
(
SELECT empid, checktime, ROW_NUMBER() over (partition by empid order by checktime) as row_cnt
FROM dbo.EmpMaster
WHERE chektype = 2
)B ON A.empid = B.empid and a.row_cnt = b.row_cnt
group by a.empid
begin
declare @start datetime ,@ends datetime
set @start=(select MIN(checktime) from EmpMaster where chektype=1)
set @ends=(select Max(checktime) from EmpMaster where chektype=2)
select @start,@ends
select DATEDIFF(hh,@start,@ends)
end
select empid, count(chektype)
from EmpMaster
where chektype = 1
group by empid;
Hi Thejus,
Script for your query is as below –
————————————————————————————————
SELECT A.empid, DATEDIFF(hh,A.checktime, B.checktime) WorkHrs
FROM
(
SELECT empid, MIN(checktime) checktime FROM dbo.EmpMaster
WHERE chektype = 1
GROUP BY empid
)A
INNER JOIN
(
SELECT empid, MAX(checktime) checktime FROM dbo.EmpMaster
WHERE chektype = 2
GROUP BY empid
)B ON A.empid = B.empid
————————————————————————————————
Please check and revert!
Current_timestamp is ANSI standard
and Getdate() is T-SQL implementation
is only the difference between these two.
CURRENT_TIMESTAMP, yes , I am using always it
I love you, Man! When I ask google a SQL question, click, then see your face, I know I’m in the right place. Keep up the good work!
I’ve always used GETDATE() and had to read up on CURRENT_TIMESTAMP because a C# Developer used it when writing some SQL.
My Vote is for GETDATE()
I have always used GetDate().
Good analysis with right example