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
@Kanika,
you can do that by using Datetime conversion functions available in SQL..
like
select convert(varchar(10),getdate(),105)
Can we select any of them as field’s Default Value if field type is DateTime?
Or
Is there any solution for that?
I need a column if it doesn’t specified on insert it’s default value should be current date time!
Create that column to have default value of getdate()
Sir,
Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005 & ms-access
Sir,
Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005 & in Ms-Access.
Formation matters only if you want to show dates in front end application. If you use front end application, use format function there. Otherwise read abot CONVERT function in SQL Server help file
Converting UTC datetime to a desired format like 2009-01-08 18:26:54.923 instead of 2008-06-18T13:51:46.7-05:00
Thanks for your post.
BTW the only one of the three that works within a DTS package against a legacy KB-SQL ODBC datasource is {fn NOW()}.
I’m a newbie, and am guessing it is because the others aren’t understood by the KB-SQL’s flavour of SQL?
Anyway, very glad that I remembered your post (which I’d already referred to for straight MSSQL queries) and found that one out of three worked in the context of what I was doing.
So, while they’re all the same performance-wise, there is a crucial difference in breadth of application.
Thanks again.
hi
i have problem in inserting the record depending on the current date. if current date change then record has to be inserted automatically.plz tel me how to write trigger or jobs in sql server2000
Actually i’m finding that
CURRENT_TIMESTAMP is correct, it returns the full date and time :
2009-10-26 04:58:24
GEDATE returns always return a time of 0 ( the date is correct though )
example : 2009-10-26 00:00:00
Si in my case i prefered to use CURRENT_TIMESTAMP because i sort my news by time, and i might have several news an hour.
Pat.
<>
How is it possible?
GETDATE() will always display date along with time
Hi,
I need help on writing a query for both these scenarios :
1. The current date is equal to or greater than 92 days past a semester start date.
2. The current date is not greater than 92 days past a semester start date.
Semester start date is a column..
Thank you..
Clue
datediff(day,col,getdate())>=92
Hi,
I am new in sql server,
i have some problem ,
like when i write this code
select * from [some table] where
convert (datetime,’1/5/2009′,103′) >= getDate()
when return the record is lose some of them .
i hope help me .
thanks
The correct way od foing it is
select * from [some table]
where
date_col>= dateadd(day,datediff(day,0,getDate()),0) and
date_col< dateadd(day,datediff(day,0,getDate())+1,0)
Hi all,
I have table in which hiredate is column and i want to calculate, Using HireDate of an employee the time they have worked. Basically i want to calculate the difference between hire date and current date of my system.
Thanks a lot in advance.
Jay
print datediff(day, ‘2009-10-01’, getdate())
will give you 27, because I used ‘day’ as the first parameter.
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017
Select Replace(Convert(Varchar,GetDate(),106),’ ‘,’-‘)
how to find last 15 days, last 2 month date
how to find day on end of month
for my report I specify the following:
DECLARE
@StartDate datetime,
@EndDate datetime
SET @StartDate = ‘2009-12-10 21:00′
SET @EndDate = ‘2009-12-11 07:00′
SELECT ……
WHERE Datecolumn >= @StartDate
and Datecolumn < @EndDate.
For the report, instead of specifying date and time ('2009-12-10 21:00'), I want the users to only specify the time (21:00). Which is the best way to do this ?
Hello Clive,
If you are using SQL Server 2008 then you can convert the date variables to TIME data type.
In earlier version use the following syntax:
WHERE Datecolumn >= CONVERT (VARCHAR(5), @StartDate,108)
and Datecolumn < CONVERT (VARCHAR(5), @EndDate,108)
Regards,
Pinal Dave
é o seguinte precisava de uma ajuda vossa , quero visuallizar registos somente da semana passada , ou entao de 8 dias atraz mas nao queria q aparecesse o dia actual
tenho usado estes commandos :
DATEPART(week, dvl.DATa) = DATEPART(week, GETDATE()) AND
DATEPART(year, dvl.DATa) = DATEPART(year, GETDATE())
e este :
dvl.data > = getdate () -8
below query returns no rows
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
convert(varchar(10),hmp_effectivefrom,101)<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
group by hmp_EntityId
i am using convert(varchar(10),getutcdate(),101) format
below query returns the rows what is the diff between both
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
convert(varchar(10),hmp_effectivefrom,102)<=convert(varchar(10),getutcdate(),102) and hmp_Discontinued=0
group by hmp_EntityId
i am using convert(varchar(10),getutcdate(),102) format
Have you tried this?
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock)
where
hmp_effectivefrom<=dateadd(day,datediff(day,0,getutcdate()),0) and hmp_Discontinued=0
group by hmp_EntityId
pls reply me as soon as possible