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, please tell me how to get last month of current year in SQL Server?
select dateadd(month,datediff(month,0,gedate())-1,0)
select Month(Getdate())-1
This is incorrect. See what is the question about.Yours will return 0 for January month
Hi,
how to calculate LOP in payslip using sql server?
You need to give more informations to help you
Create this function and find the days in month and calculete LOP=DaysinMonth-LWP
Create FUNCTION [dbo].[fun_GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@pDate) % 4 = 0 AND
YEAR(@pDate) % 100 != 0) OR
(YEAR(@pDate) % 400 = 0)
THEN 29
ELSE 28
END
END
END
Create this function and find the days in month and calculete LOP=DaysinMonth-LWP
Create FUNCTION [dbo].[fun_GetDaysInMonth] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN CASE WHEN MONTH(@pDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@pDate) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@pDate) % 4 = 0 AND
YEAR(@pDate) % 100 != 0) OR
(YEAR(@pDate) % 400 = 0)
THEN 29
ELSE 28
END
END
END
hello sir, i am facing a problem in sql , i want to know the command which is used to know the no. of days where two dates were given.
for ex.
28/10/2008
10/11/2008
then what is the no. of days between these dates.
thanking you..
Use datediff function
select datediff(hour,date1,date2)
select Datediff(Day,convert(datetime,’28/10/2008′,103),Convert(Datetime,’10/11/2008′,103))
can u correct this query , bcoz it’s nt working
select datediff(hh,select hire_date from employee,getdate())
It should be
select datediff(hh,hire_date ,getdate()) from employee
sorry it should be
select datediff(dd,hire_date ,getdate()) from employee
hi
can you tell me how to retrive data from any table by using current date we will not give date in where condition but the table have date colum
eg
select * from abc where date=todaysdate.
system should take the todays date.
Use this code
where
date_col>=dateadd(day,datediff(day,0,getdate()),0) and
date_col<dateadd(day,datediff(day,0,getdate())+1,0)
I want extract only date and not time from getdate()
Try this code
select dateadd(day,datediff(day,0,getdate()),0)
how to add validity numbers of years in start date (datetimepiker1) and after add years output last date in (datetimepiker2)
Please very important in my projects
reply me
I upsized an Access application (with data) to SQL 2005.
Application works fine with the ‘date’ data that was uploaded, but not with new dates that are added.
The dates LOOK like they are in the same format, but there must be a difference.
If I run
select * from census
where censusdate= ‘4/10/2008’
I get all the records returned.
If I run
select * form census
where censusdate= ‘5/1/2008’
nothing gets returned, even though in the table there are 89 records with the date 5/1/2008.
These records were added with the following
UPDATE vth_census SET vth_census.censusdate = getDate()
WHERE (((vth_census.censusdate) Is Null));
(I’ve tried different date formats……05/01/2008, etc. All the possibilties work with the 4/10/2008 date, not with the 5/1/2008 date.)
I’ve narrowed down the problem to the date issue, but I can’t figure out the solution.
Any suggestions?
Thanks,
Jill
Becuase you enter dates in ambigious format
Express the dates in YYYYMMDD format and it will work perfectly
hi,
i have one query,
i want to find the difference between the time saved in data base and current time
and the condition is
if the difference is > 30 the respected rows only should display.
Example
select columns from table
where datediff(hour,date_col,getdate())>30
Hi,
I want the day of a given date. Like Monday, Tuesday. There is a similar question, but no answer. Pls help
Nanthu
Use this code
select datename(weekday,getdate())
Hi There, when asking for the date/time difference between 2 dates, is it possible to get it formated in hh:mm:ss but the hours incur +24 per day.
ex.
2008/5/6 04:56:55 PM – 2008/5/5 03:22:25 PM
I need it to return 25:34:30
instead it returns 1901/01/01 01:34:30
Hi, i need de getdate from my local computer..when i use the getdate() bring me the date from the server…
Tks…
It is not possible to get local system’s date
Hello,
I need to format the date in that way:
20080506
it is yyyymmdd and with the sentence that I have tested:
DECLARE @day as DATETIME
SET @day = convert(varchar,datepart(yyyy,getdate())) + ‘’ + convert(varchar,datepart(mm,getdate())) + ‘’ + convert(varchar,datepart(dd,getdate()))
I get this: 200856, without zeros.
Even defining each varchar length (varchar(2)) I get the same.
What should I do?
Thanks!!!!
I have found the solution to my question. I didn’t know that there was a list of formating stiles.
To get 20080507 the code number is 112.
SET @data = convert(varchar,getdate(),112)
Thank you anyway
Read about Convert function in SQL Server help file
You will know lot of formatting styles
here you go Teo
SELECT CAST(DATEPART(YEAR,GETDATE()) AS VARCHAR(4)) + RIGHT(‘0’ + CAST(DATEPART(MONTH,GETDATE()) AS VARCHAR(2)),2) + ‘0’ + RIGHT(‘0’ + CAST(DATEPART(DAY,GETDATE()) AS VARCHAR(2)),2)
Hi,
I need to find the current year for the previous year
For ex,
Select yr_mnth,…
from …
where yr_mnth in (200603,200602,200601)
consider the year 2006 is the previous year…and I want the results for 2007(i.e,for current year)
result
200703 …
200702 …
200701 …
Did you mean this?
select dateadd(year,-1,getdate())
Hi..
I am new to this one… plz body can help me..
i have one query in sql like.. using the current year , i have to get the data previous year as well as current year…
what should i do?
Thanks!
select columns from table
where date_col>=dateadd(year,datediff(year,0,getdate())-1,0)
hi im vicknesh here , im havinga difficulty when exract the date according date , where i need to extract the member details which includes member expiry dates on 31stmay 2008 .
can i put as follows … WHERE (vip.vip_expirydate => {d ‘2008-05-31’})
What happened when you tried?
If dates included time too, use
WHERE
vip.vip_expirydate >= '‘20080531' and
vip.vip_expirydate < '‘20080601'
Note that YYYYMMDD format is unambigious
how I can select all record in last week
table action
{
Actiondate datrtime
………
primary key(Actiondate)
}
where actiondate>=dateadd(week,datediff(week,0,getdate()),0)
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.