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
Need help with getting the correct time due to daylight saving time. I need to get current time from table but factor in as follows:
If date is >= first Sunday in November and <= 2nd Sunday in March, then I need to subtract 1 hour from the date time I display on a report.
Ideas?
Hai Pinal dave,
I have an column with datetime, I want to get the seconds from that column.
For that what i could do
Thanks in advance
Hi Dhana,
you can use this: DATEPART(s,GETDATE())
Let me know if you want it in other formats too..
Thanks,
Tejas
hi ,
how to get the highest dates in the database
for example in my query i want to get the highest and compare to the input date
SELECT COUNT(*) AS Expr1
FROM Booking_Item
WHERE (‘2009-03-06’ ‘2009-03-06’)
This is my query
where your_date=(select max(date) from your_table)
how make report take date from text (from date to date)
only date between two text. thanks
Sir ,
I wnat only time in the Sqlserver2005
Sir,
Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005
Thanks
Shameer
Hi Arvind,
If you need to get Only Time, you can use
CONVERT(VARCHAR(10), GetDATE(),108).
For more details:
Regards,
Tejas
================================
Biplab
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
================================
You can do like this:
LEFT(CONVERT(varchar, CURRENT_TIMESTAMP, 103), 10)
Hello i have 4 fields in my table
1.Market Name,
2.Commodity Name
3.Price
4.SubmissionDate Date_Format=(11/02/2009 01:58:27 )
i wanna fetch a collection of records in manner of such format:
one market ,all commodity and one recent Price submitted by user .
i try my my side but i can’t do such thing.
please help me.
Thanks and Regards
Do you know is there a way in SSMS to set up precision for showing how long does the query took? The one I see at the bottom in yellow?
for Lakshmi’s Q. here is a better formated “TimeOnly” function (tweaked Vishwanath’s)
CREATE FUNCTION [dbo].[udf_GetTime] ( @myDateTime DATETIME)
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @RetDate VARCHAR(50)
SELECT @RetDate = RIGHT(CONVERT(VARCHAR(50), @myDateTime, 100),7)
SELECT @RetDate = REPLACE(@RetDate, ‘ ‘, ‘0’)
SELECT @RetDate = REPLACE(@RetDate, ‘AM’, ‘ AM’)
SELECT @RetDate = REPLACE(@RetDate, ‘PM’, ‘ PM’)
RETURN @RetDate
END
Displays your time in HH:SS AM/PM format.
Is there a way to get the day as (sunday or monday ….) in sql server as we use to_char function in Oracle to get the current day.
use datename function
select datename(weekday,getdate())
dear sir,
i am working in asp.net, i want to retrive Current Invitiondate of customere in result table.
so i use its query
select * from result where invitiondate = getdate()
But that query is not sucessfully result
plz send me this query
thanks
tejpal singh bajiya
select * from result
where
invitiondate >= dateadd(day,datediff(day,0,getdate()),0) and
invitiondate < dateadd(day,datediff(day,0,getdate())+1,0)
Use this query
select * from result where
Convert(varchar(10),invitiondate,103) = Convert(varchar(10),getdate(),103)
This will not make use of index
See my previous reply
Sir,
My current query:
select *
from workorder
order by timecreated
where timecreated >= DATEADD(day, -1, CURRENT_TIMESTAMP);
I want to retrieve all date with today’s date. I am not a SQL PRO, used to be but have not done SQL for over ten years.
“TIMECREATED” is the column where the work order’s creation date is stored in the format 1/14/2009 12:12:19PM
Can you please help me sort this out. THANKS!
Hi Peter,
you can use this:
select *
from workorder
where DATEADD(day, 0, DATEADD(dd,0, DATEDIFF(dd,0,timecreated))) >= DATEADD(day, 0, DATEADD(dd,0, DATEDIFF(dd,0,GETDATE())))
order by timecreated
Thanks,
Tejas
sir,
im currently doing a project on rfid which tracks attendance. and i am having difficulty in creating an sql query statement to be used in crystal reports.
i am using 2 database tables
1)holds the employee attendance details retrieved from the rfid
2) general employee details, more like a database of employee details
attendance details:table contents
employee id
card id
employee name
department
systemdate
time in
time out
lost
mark delete
the time in and out values are provided by teh rfid kit
while the other details are retrieved from the employee details table.
im supposed to be generating weekly, monthly reports based on the above values.
and im finding it very difficult to learn querying withing such a short span of time .
my weekly reports should contain the columns
employeeid employee name no of days late no of days early out
the start day and end dates are provided by the user at run time using a datepicker in vs2005.
the report is supposed to show a consolidated value for no of days late and no of days early out columns during the time span provided by the user at runtime.
any person entering teh gate after 8:45 is marked late and any person leaving before 4:10 is marked early out
hence for each day that the person has left early or come late i have to show days in whole numbers as 1,2 ,3 etc for the above columns(early out and late in).. can u help me generate a query for teh above
Sir
I want to retrieve all the audit logs between previous date and current date.
How can i write this ??
where
date_col>= dateadd(day,-1,getdate()) and
date_col<getdate())
DEAR SIR,
MY QUESTION IS HOW CAN I TAKE THE SERVER DATETIME, NOT THE STATTION DATETIME.
I CREATE A PROCEDURE WITH A SELECT current_timestamp
to pass the timer but return the station time not a server time.
how i do this ?
thanks and best regards
It returns server’s time only
How did you know it returns your local system’s time?