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
Great article glad I found it when I did I would have been lost without it. Backlinks
A computer program called Antivi took over my computer. How do I get rid of it?
I will check the databas field date time format compare the current date time checking how to integrate plese help me
FWIW, I was connecting to a non-SQL DB (PROGRESS still lives!) while creating a DTS package through ODBC and it does make a difference regarding which one you use.
GETDATE() was giving me an error until I tried {fn NOW()} which worked like a charm.
Thanks to this page, I was able to find a solution though.
I have one datetime variable. It contains the value like {10/10/2011 2:33:33 PM}. How can i compare this value with the other datetime variable’s value which is like {2011-10-10 14:33:33.990} ?
This both are the same time but still i can’t compare it. Help me how to do it?
Thanks in advance,
Yash Thakkar
Make sure to read this post
Hi,
How Can I Insert / modify current date time in TimeStamp Column.
when I try to update timestamp column, its giving msg
Msg 272, Level 16, State 1, Line 1
Cannot update a timestamp column.
when i try to insert timestamp column, its giving msg
Msg 273, Level 16, State 1, Line 1
Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.
Thanks in advance,
Vijay Vegi
You cannot add/update value for Timestamp datatype
hi,i want to know about the date datatype on sql server2005, that didnt acquire the date datatype.
How can create the date datatype in sql query.
greantly give me your suggesstion.
thanks,
Gomathi
hi,i want to know about the date datatype on sql server2005, that didnt acquire the date datatype.
How can create the date datatype in sql query.
greantly give me your suggesstion.
thanks,
Gomathi
Note that DATE datatype is available from version 2008 onwards. You can use datetime datatype instead
i want query which retrive date from close to curret date…example
5-12-2011
20-12-2011
take 20-12-2011
Do the data available in a table?
select max(date) from table
where date<=getdate()
How to insert date using date time in UK style date ?
Forget about regional datesettings. Follow this post
Hi Pinal,
I need to calculate the number of days by doing this calculation
MTRY_DT – (YR_NUM+MO_NUM+Last day of the month) ,Where MTRY_DT = Maturity Date . So,how can I do this for all the rows that I have.
I have 6446 rows.
Thanks in Advance,
Samyuktha
sir, i want to auto generated in date and month of the database in sql server 2005 . please help for me
You need to post some sample data and expected result to help you
please help me,I want to retrieve one column of my Database as per current date in asp.net ,SqlServer2005
Hello,
How can I specify a condtion to achieve the required date where
“required date= current date -13months”
RequiredDate = (sysdate – interval ’13’ month)
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())
thanks pinal for ur help. can u pls help in some question….is SQL/PLI is similar to ODBC????also to JDBC?????
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
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
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
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
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.