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
Hello M.Kamala,
Here you are converting two date values as strings. And strings are compared from left to right character by character. To explain I am taking two values as below:
hmp_effectivefrom = 10/20/2009
GetUTCDate() = 01/4/2010
Now when we compare these values using convert(varchar(10),getutcdate(),101) format, they are compared as:
10/20/2009 <= 01/04/2010
This is false and will return record.
When we convert these two values using convert(varchar(10),getutcdate(),102) format, they are compared as:
2009/10/20 <= 2010/01/04
This is true and record will be returned.
Regards,
Pinal Dave
dear pinal,
which can i use in my project? any other solution for the same.
currently i using convert(varchar(10),getutcdate(),101) it’s working 2009 records now 2010 then doesn’t return any record.,
Pls suggest me if any good idea for date filter
See my previous reply
please suggest, can i use below format query?
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
group by hmp_EntityId
otherwise using below l
select max(hmp_effectivefrom) from mos_HandlingMarkupPercentage with(nolock) where
hmp_effectivefrom<=convert(varchar(10),getutcdate(),101) and hmp_Discontinued=0
group by hmp_EntityId
No need to convert any value. Just compare without conversion as below:
hmp_effectivefrom<=getutcdate()
Regards,
Pinal Dave
thks pinal.
one query.
hmp_effectivefrom stored in future value also.
Ex:
date stored in table already
‘2010-01-05 04:35:09.680’ like this but i run the report before 4:33 then report didn’t pick up the this date. this is my problem.
‘2010-01-05 04:33:01.197’ (sysdate)
Ex:
hmp_effectivefrom<=getutcdate()
'2010-01-05 04:35:09.680'<='2010-01-05 04:33:01.197'
then this record is not displayed. but i want today all records.
for that i am using like below is it correct or not. have look
cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101)
Please help as soon as possible.
if ok means any difference between below
cast(convert(varchar(10),hmp_effectivefrom,102) as datetime)<=convert(varchar(10),getutcdate(),102)
cast(convert(varchar(10),hmp_effectivefrom,112) as datetime)<=convert(varchar(10),getutcdate(),112)
thks pinal.
one query.
hmp_effectivefrom stored in future value also.
Ex:
date stored in table already
‘2010-01-05 04:35:09.680′ like this but i run the report before 4:33 then report didn’t pick up the this date. this is my problem.
‘2010-01-05 04:33:01.197′ (sysdate)
Ex:
hmp_effectivefrom<=getutcdate()
'2010-01-05 04:35:09.680'<='2010-01-05 04:33:01.197'
then this record is not displayed. but i want today all records.
for that i am using like below is it correct or not. have look
cast(convert(varchar(10),hmp_effectivefrom,101) as datetime)<=convert(varchar(10),getutcdate(),101)
i expecting your solution.
Hi M.kamala,
Use the below expression:
hmp_effectivefrom < convert(varchar(10),getutcdate()+1,101)
Regards,
Pinal Dave
I would like to get the following comparison:
example 1: I would like to receive data 2 years back from current date.
How can I arange that?
Thanx!
Rob (The Netherlands)
where
date_col>=dateadd(year,-2,getdate())
How can I give this name to my .bak file?
Or if it is not possible can I provide time only like
RemoteDatabase_11:29AM.bak
Hi Jayan,
We can not use special characters for file/folder name. “:” is one of special character, so we can not give file name that you want.
What alternate is give file name like:
Test_20100112.bak
where 2010=year, 01=month, 12=date.
Thanks,
Tejas
SQLYoga.com
Hello friends
How I can have TIME format
(for example) 125623
in sql server 2000
Hi Pinal,
I am just a beginner to SQL server 2008 and learning sql commands, i am unable to get logic behand store procedures
could you please help me out and can you post me the syntax for getting last and fist day of the month if we pass current date using store procedures.
thank you.
Select
dateadd(month,datediff(month,0,getdate()),0) as first_day,
dateadd(month,datediff(month,0,getdate())+1,-1) as last_day
Madhivanan
More functions
Madhivanan
Hi Pinal,
I am new in SQL Server 2008.
I want to execute some stored procedure on particular dates(all column entries as dates).
How shd I do tht?
Please help me out.
Thanks.
Chetan
Hello Chetan,
What I understand is that you have table containing dates and you want to execute some stored procedure on that dates. If that is what you want to do then create a SQL Server Agent job to execute once daily. In the job check for the GETDDATE() in dates stored in table, if found then execute else skip it.
Regards,
Pinal Dave
hi pinal,
im having trouble inserting and retrieving dates. im using visual web developer 2008 and sql server 2005.
i would like to insert current date while in retrieving, i want to retrieve date(s) only.
can you help me out by showing me the format in query?
thanks!
When you show dates in .NET use fromat function there
Hi Pinal,
Yes, I was looking for that only.
My issue has been solved.
Thank you very much for your help.
Regards,
Chetan
Hi All,
I have the column called created date , i want to make the report, for every three month ,like”jan,feb,march”,….etc..,
when i use between operators i am not able to get , similarly ,
can u please help me in code ,
Regards
sateesh
Post the code you used
Your requirement is not veryclear
Hi
when i tried to find record which is greater than current date it show me all date record because it compare date with time thats why it give wrong record but i tried below which give me right record
string currentdate = DateTime.Parse(DateTime.Now.ToString()).ToShortDateString();
select * from tbltest where TrainingDate>='” + currentdate + “‘”
Hi,
I want difference between two date days, months and year all required.