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 Everyone
can any one find solution for this qury
I have one column which contains Date field. and I want date difference between two rows like (1st row- 2nd row),(2st row- 3nd row) 3-4,4-5 like that.
Thanks in Advance
VishwanathReddy
Hi
can we get only time in datetime(),if anyone knows plz update………
ex:
I need it to return 01:34:30
instead it returns 1901/01/01 01:34:30
Thanks
Kathir
Hi S. Ramkumar,
u can use convert function and u will get the value as u want
convert(datetime,getdate(),101)
Hi Kathir,
Can u please use this ?
convert(datetime,getdate(),108)
Hi esam,
can u plz use between clause?
Hi kpn,
can u please use datepart function and thn use ‘=’ condition.
datepart(yyyy,date)
can u tell me, how to dispay time like this “1994-11-05T08:15:30-05:00” corresponds to November 5, 1994, 8:15:30 am, US Eastern Standard Time.using sql server 2005
Thats the formation issue which should be addressed in your front end application
Hi,
can any one help me Please. i have a date column “DateTime” populated with 2008-05-01 11:45:58.380 with thousands of dates , when i run report /Excute i would like to get result ” DataTime” as 2008-05-01 only, not 2008-05-01 11:45:58.380. Is there any way to truncate the date field??
Have a look at Convert function in SQL Server help file. Also formation is the job of front end application
Hello,
I have entered this trying to get the current date into my table with the field named InvoiceSentDate. Is this the correct usage b/c the column keeps coming back null.
select @InvoiceSentDate = GETDATE()
Thanks,
Nathan
Can you post the full code you used?
This worked for me: –
SELECT dbo.AuditTrail.ATTime,
CONVERT(char(12), dbo.AuditTrail.ATTime, 3) ,
CONVERT(char(12), dbo.AuditTrail.ATTime, 8) ,
CONVERT(char(12), dbo.AuditTrail.ATTime, 10) FROM dbo.AuditTrail WHERE CONVERT(char(12), dbo.AuditTrail.ATTime, 3) = ’01/05/08′
WHERE dbo.AuditTrail.ATTime= �'
is the effecient method
My above reply should be
WHERE dbo.AuditTrail.ATTime= '20080501'
is the effecient method
Hello,
I would like to format the CURRENT_TIMESTAMP. Suppose CURRENT_TIMESTAMP return ‘2008-02-09 11:46:17.827′. But I want ’09-02-2008’. Is it possible.
With Regards.
Biplab
It is the job of presentation layer
If you dont have any other option, use format function
select replace(convert(varchar(10),CURRENT_TIMESTAMP,103) ,'/','-')
Dear Friends,
I want to find only the differnece in the time in minutes and hours can u suggest me how can I do it
Read about datediff function in SQL Server help file
Hi Kabir/Biplab,
Did you guys find any solution to the problem.
Thanks,
Sanjana
Hello, i have a problem witn a stored procedure which uses datetime variables. The procedure looks like :
BEGIN TRANSACTION
DELETE FROM dbo.station
WHERE stationId = @stationId
AND auditDate = @time_stamp
auditDate and @time_stamp are both DATETIME
When I try to execute the procedure, it gives the following error : “Error converting data type nvarchar to datetime”
If i try to use another date format , like mdy not dmy , there is no row updated. Any idees ?
Thanks
How did you assign value to the variable @time_stamp?
Post the full code you used
@tylo
Its not the problem of datetime format, the problem is with Nvarchar and datetime. Either auditdate or stationid must be nvarchar datatype and you are trying to compare a datetime with a nvarchar datatype… which is not possible.
Try using a convert function and then compare, it would be helpful if you post your complete query….
Thanks,
Imran.
i am struggling from one concept.let me any one help for me?
how to find difference b/w two dates with timings….
@ sathish,
select datediff ( dd, ‘01.23.1985’, ‘02.23.1985’)
Result: 31
Hope this helps.
Thanks,
Imran.
Note that you should always express dates in unambigious formats like YYYYMMDD. Othwerwise depends on the Server’s date format, you may get error
hi all
Can anybody help me out to calculate the age of a person…in
years-month-days like 23 years-9 months-10 days
we are having a column as DOB(date of birth) in one table….
I want to calculate using current date….
thanx in advance
@jitender Pal,
I would use some thing like this for your question,
declare @date varchar(100)
set @date = ‘2.23.2005’
select (convert (varchar(10), datediff (yy, convert (datetime , @date), getdate()))+ ‘ Years- ‘ + convert (varchar(10), datepart (mm, (getdate())- convert (datetime , @date) ))+ ‘ Months- ‘ + convert (varchar(10),datepart (dd, (getdate())- convert (datetime , @date) ))+’ Days’) AS DOB
You might want to change this code accordinly to your needs.
Hope this helps.
Thanks,
hi
I need to generate an expiry date baised on the date entered in one coloumn
can you help me out with this