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
@Mirwais,
In order to just extract the year, use command
select DATEPART(year,getdate())
Example of how to substract value from it:
select 2014 – DATEPART(year,getdate())
i wan to retrive data from existing table but i can’t compare datefield’s date(18-APR-2012) to current month
vishal pandey
dear friend i am also best make sql softwere
hello,
I have tried
DECLARE @X int
SET @X= DAY(getdate())
and the same things for year and month
and I increase DAY and I want to make that values in datetime (yyyy-mm-dd)
how can I do that
Simply use like
select dateadd(day,2,getdate())
actually my problem is that I want to get date from my existing table and increase day then format new datetime
Hi sir i want to fetch date from system but the condition is that
I want to fetch yesterdays and current date before 5:30 pm and the date must be not in 2nd and Third saturday and other sundays
HI..
I working on a oracle db but my db creashed suddenly,how can i take backup of my db.plz help
Only way is to restore the dump you have already taken
Thanks …giving first page in Google search
Sachin Karche
How insert cuurect date time in MSSQL table ??
Anyone Plz help…
use GETDATE()
insert into table(col)
select getdate()
This syntax is used to display only date (2011-11-01 00:00:00 )
eg syntax:
convert(varchar(10),Date_Da,101) as Date_Da
This Date_Da that field is our table date field
sir;
i am facing a problem .i want to find current date and time of indian format .
when we host the application then time is not in good format as like pm to change in am
sir, plz help me
I’m wanting to find records that are older than 85 days from today. When I put the following code: Category_3 < dateadd(day, -85, getdate())
Once excuted I then get the following error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value. Error Code: 242
Any ideas on how to fix this?
I fixed my own issue. Should have had:
Category_3 < Convert(Varchar(10),dateadd(day, -85, getdate()))
However now would love to sort these results in DESC order. ORDER BY Category_3 DESC doesn't work (I assume because of the Varchar). Any ideas?
I want month and year-wise Reports so how to pass Month and Year Parameters in Same Stored Procedure..Please Help Me as soon as
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