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
Thanks for providing alternative.
I normally use GETDATE(). But never heard about SELECT {fn NOW()}.
I like your “My Preference:” section !!!
You can read about more such functions in this blog post
Refer this
Your article is very good
how to check user login in network using c# & sql.
how to find a day,hours and minutes from one query.
guys i want the OutPut Like this
as i’m selecting a query which have many crows i’m showing some sampl that i want
Days
————-
1 Days ago
8 hours ago
12 minutes ago
2 minutes ago
8 minutes ago
12 hours ago
please help me
select
case when datediff(day, MyDate, getdate())>=1
then cast(datediff(day, MyDate, getdate())as varchar )+’ day(s) ago’
else
case when datediff(hour, MyDate, getdate())>=1
then cast(datediff(hour, MyDate, getdate())as varchar )+’ hour(s) ago’
else
case when datediff(minute, MyDate, getdate())>=1
then cast(datediff(minute, MyDate, getdate())as varchar )+’ minute(s) ago’
else ”
end
end
end
from MyTable
You need something like this?
Dear All,
Hi i need help from u,i want to display a current date on my web application but it shows previous date upto 12.30 PM, after 12.30 PM as indian time its get change to current date .Please help me how to solve this problem.
Can you post some sample data with expected result?
Hi,
I have a table with 2 columns, one for date(only) and one for time(only).
I need to insert into those columns the current date and the current time.
Could you help me please?
I got it:
insert into MyTable
(MyDate, MyTime)
values
(dateadd(day, 0, datediff(day, 0, getdate())),
getdate()-dateadd(day, 2, datediff(day, 0, getdate())))
It seems that for some reason if the datetime is 12/30/1899 10:20:00, the date becomes invisible, only time is shown, like :
MyTime
=====
10:20:00
What is the datatype of the columns?
Hi,
datetype of the columns is datetime.
I am looking for function that returns the data between two times
for ex. mod_date_time between ’2010-07-21′ and ’2010-07-22′ will give me the data which has 21/07/2010 date.
But if I want to see the data between 06:30 am to 09:00am, How do i do this?
Please help me out, with this one!!!
Make sure to read this post and try to understand hot to use date and time values in the where clause
Hi Vikash,
You can use to write a query as
SELECT *
FROM Table
WHERE DATE BETWEEN ‘2010-07-21 06:30’ AND ‘2010-07-21 09:30’
This query will return only data between 06:30 AM and 09:30 for 2010-07-21
Thanks,
Tejas
You should always enter dates in unambiguous format. Refer this post for more informations
HI Pinal Dave,
how to have the query for data enter in one particular month and year like range 1st of every month to 5th of same month, 6th to 10th, 11th to 15th like wise
pls i have having serious problem with my application.whenever any transaction takes place,it records the date the transaction took place in this format,04/08/2010(mm,dd,yyyy).from the client end they are suppose to be able to view report for all transaction ,but the problem is that the rad date picker brings out the date in this format
4/8/2010 12:00am but i was able to use this code RadDatePicker1.SelectedDate.Value.ToShortDateString(); to get only the date.what i need is for the date value gotten from this rad date picker to be in the same format with the first one ,that is 04/08/2010 must be in the same format as 4/8/2010. i have tried using SELECT CONVERT(CHAR(10),4/8/2010,110) it didnt work.what sql code do i use.
Make sure to read this article that explains how to use proper date format and use it effectively in SQL Server
I have 3 columns in a table(CreationDate, LastModifiedDate, presentDate). when i am inserting data into record i want system datetime automatically updated to the table.
how can i do that
You need to use getdate() function
Hi Rakesh,
You need to alter those columns as:
ALTER TABLE
ALTER COLUMN DATETIME DEFAULT(GETDATE())
By this way, if you not specify value for this column then also this column will be populated with current time.
Thanks,
Tejas
SQLYoga.com
Hi all,
i have a ITEM_ID,start date & end date. For the ITEM_ID, if the current date falls under the start date & end date ,it should return yes or else no.
How to achieve this?
Thanks in Advance
Thanks & Regards,
Priya
select ITEM_ID, case when getdate() between start_date and end_date then ‘yes’ else ‘no’ end from table
Hi All,
I want to convert MM-YY (for ex: 04-99) to YYYY-MM-01 (always first day of the month ex: 1999-04-01). Could anyone please advise?
Thanks in advance,
Sathish.
declare @date char(5)
set @date=’04-99′
select convert(datetime,’01-‘+@date,5)
— =============================================================================
— Author : Venkata Narayana Thogati
— Create date : July 26 -2010
— Description : This SP for to get the day dates of perticular day in a month
— Parameters : @month, @year, @dayname
— Exicution : Exec [p_GetReqWeekDaysInAMonth] 01, 2010, ‘Monday’
— Result : It givies the all monday dates in jan month of 2010 year
— =============================================================================
ALTER PROCEDURE [dbo].[p_GetReqWeekDaysInAMonth]
— Add the parameters for the stored procedure here
@month int = 0,
@year int = 0,
@dayname varchar(50)
AS
BEGIN
— SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
SELECT
CAST(CAST(@year AS VARCHAR) + ‘-‘ + CAST(@Month AS VARCHAR) + ‘-01’ AS DATETIME) + Number as DaysDate,
DATENAME(dw,CAST(CAST(@year AS VARCHAR) + ‘-‘ + CAST(@Month AS VARCHAR) + ‘-01’ AS DATETIME) + Number) as DaysName
FROM master..spt_values
WHERE
TYPE = ‘P’
AND
(DATENAME(dw,CAST(CAST(@year AS VARCHAR) + ‘-‘ + CAST(@Month AS VARCHAR) + ‘-01’ AS DATETIME) + Number) = @dayname)
AND
(CAST(CAST(@year AS VARCHAR) + ‘-‘ + CAST(@Month AS VARCHAR) + ‘-01’ AS DATETIME) + Number )
<
DATEADD(mm,1,CAST(CAST(@year AS VARCHAR) + '-' + CAST(@Month AS VARCHAR) + '-01' AS DATETIME) )
SET NOCOUNT OFF;
END
select cDate from tablename where month(CDate)=MONTH(SYSDATETIME()) and day(CDate)=DAY(SYSDATETIME())
Hai,
I get current time using the following select query,
SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)
Result : 4:00PM
SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)
Result : 5:20PM
I want the time difference and get result like this 01:20 using select query, plz tell anyone what sql query?
Do you want top take this from a table column?
hi i am a sap b1 developer.i entered time in a field.when i am retrieving the time when 00:01 it is showing as “1” please tell me how to retrieve time value.
What is the datatype of the time column?
Hi All…
Ho can I convert sum of decimal to time
actually data in data base as fallows:
Col1 Col2 Col3
1.20 1.30 1.20
I tried to Sum(col1+col2+col3) = 3.70
but here 1.20 = 1hr 20min, 1.30 = 1hr 30min
I want result should be as 4.10, for this result what can I do?
please advice is there any userdefined function is there for this
Thanks,
Venkat919
Refer this post. The methods used will help you in solving this
Date conversions are big pain to me. I always confuse on data conversion using “convert()” function.