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
hi,
I am new to sqlserver 2005, so pls help me
i have @date=10:10:10 AM (hh:mm:ss am/pm format) as varchar datatype.
I want to get the current sqlserver time as say @current_time and find the difference between @date and @current_time.
Thanks,
Prem.
select datediff(hour,@date,getdate())
how am i able to change the sql server current date… by adding 1 day to it?… pls help… thanks a lot… God Bless!!!
Did you try this?
select dateadd(day,1,getdate())
Hi
Can u please help
Iam having DateTime in database I need to compare only Time in the database with the current system time i.e comparing two times………………………
select datediff(hour,your_date,getdate())
I was going thru my sch project and this is a stupid course of mine~. Lectures don’t cover all these in detail
Thanks for the GetDate function with a () , I guess it helped me to search for more resource and info on the net too !!!
I want asp script which can help me to get a date after six month from date which is retrive from database.
where date_col>=dateadd(month,6,getdate())
How can i display the detail which is after the day 4th march 1963
i tried this one but shows no rows selected.
where as there are rows after this date
select * from agents
where to_char(activation_date,’FMMonth DD, YY’) > ‘march 4, 1920’
;
please help
Jen,
U can add one day by code below.
select dateadd(dd, 1, getdate()) – add one day
select dateadd(mm, 1, getdate()) – add one month
select dateadd(yy, 1, getdate()) – add one year
Same way you can subtract day, month or year. Just give negative value.
select dateadd(dd, -1, getdate()) – subtract one day
hi Kim,
Please see below:
select * from agents
where replace(convert(varchar, activation_date, 102), ‘.’, ”) > 19200403
how to give like % convert(varchar(10),dateadd(day,datediff(day,0,GetDate())- 10,0),103) % in sql query
can you help me to to get current date sql server 2005
use “select getdate()” to get current date
How to diplay the list of months in the current year ??
I want to know server date() in sql server 2005?
if i am not connect to server directly but i alreadt know server name
then how can is it possible???
No. You need to connect to it and query
select getdate()
how to auto generate email after one day or 24 hours?
urgent!!
help!
Hi Farhana,
you can maintain column in database which indicate status of email address generated or not.
For 24hr solution maintain one column which insert current date when data inserted in table.
you can query database and get last day records. After that say u have unique user name in your system or firstname or something that u maintain unique for all users. Take that field value and concatenate it with domain you want to auto generate like “hello@example.com” and send this details to user or update the data whatever task you want.
Hope this help you out.
Thanks
thanks!
Hai,
for example the manager when check the report , that time one hour before records only to display and it store automatcally in path.
What is the query for that one?plz tell me step by step
SELECT @DateTime AS Expr1, LastModifiedDate
FROM dbo.tblProjectHistory
WHERE (LastModifiedDate = @DateTime) AND (RecordId = 1)
LastModifiedDate fields type date time
LastModifiedDate=23/10/2008 2:18:51 PM
@DateTime=23/10/2008 2:18:51 PM
when run Query result null
the Answer to the getdate issue is simply to put Default
i.e
Insert into tableName(v1,va2,datetime)
values(‘myself’,’yourself’,default)
done.
i want select Second highest date from table.
Refer this
Apply the logic