SQL SERVER - Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}

June 21, 2007 by pinaldave

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 same results. If you see execution plan there is no performance difference. It is same for all the three select statement.

SELECT CURRENT_TIMESTAMP
GO
SELECT {fn NOW()}
GO
SELECT GETDATE()
GO

Performance:
There is absolutely no difference in using any of them. As they are absolutely same.

My Preference:
I like GETDATE(). Why? Why bother when they are same!!!

Reference : Pinal Dave (http://www.SQLAuthority.com), BOL (All Definitions)

Posted in Author Pinal, SQL, SQL Authority, SQL Coding Standards, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 84 Comments

84 Responses to “SQL SERVER - Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()}”

on June 25, 2007 at 6:42 am S. Ramkumar

Actually i worked in Oracle 10g. Now i got prj in Sql Server 2005. Here i feel little difficult in formatting Date.
Can u tell me how to format Date as ” DD\Mon\YYYY”.
Here in Sql Server 2005 gives both DateandTime.
on June 25, 2007 at 6:47 am pinaldave

Hi Ramkumar,

Refer my article SQL SERVER - Retrieve - Select Only Date Part From DateTime - Best Practice

Regards,
Pinal Dave (SQLAuthority.com)
on June 25, 2007 at 7:29 am S. Ramkumar

Hi,

Don’t feel that i’m appreciating Oracle. i’m New for Sql Server.

Just now i worked u r example.

Actually i need a Date in this format ( 25-Jun-2007).

In Oracle we use as follows,

Query:
select to_char(’25/06/2007′,’dd-Mon-YYYY’) from dual;

Result:
25-Jun-2007

In Sql-Server 2005, i tried following query.
Query:

select substring(convert(varchar(10),getdate(),111),len(convert(varchar(10),getdate(),111))-1,2) + ‘-’
+ substring(convert(varchar(20),getdate(),0),1,3)+’-'+substring(convert(varchar(10),getdate(),111),1,4)as Dat

Result:
25-Jun-2007

See the Length of the query, can u give me a short query to format Date in Sql-Server 2005

Regards
S. Ramkumar
on June 25, 2007 at 7:35 am pinaldave

Hi Ramkumar,

I prefer SQL Server over Oracle cause several reasons. One of them is simplicity.

First you should go over to post my previous comment.
Second see the reference of Book On Line.
which is BOL Convert. There are many examples of different date formats. Just use the one you need.

Regards,
Pinal Dave(SQLAuthority.com)
on June 25, 2007 at 7:47 am S. Ramkumar

Hi,

Thankyou Mr. Pinaldave. i saw that on-line book. There are lot of styles were given for Date conversion. I think style no: 105 and 106 will satisfy my requirement.

Thankyou again. Bye.

Regards
S. Ramkumar
on July 27, 2007 at 12:50 am Didier Peinke

Hi, am learning SQL SERVER 2005. How i do condition in Date like

SELECT…
FROM…
WHERE EndDate < …………….

I have try currentdate, SYSDATE but it error. Please provide a code of Current date thank.

Didier Peinke
on August 17, 2007 at 5:36 pm pinaldave

Hi,

Use GetDate() to retrieve or select current datetime.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on August 23, 2007 at 10:01 pm Jigna

Hello

I have a table in sql server 2005 which contain a column for date.
I want to put a validation that if user does not specify the date, it takes current date and time.
Is it possible by setting the ‘Default value’ for that column?? if yes how can i dothat.
Please help me.
Thanl you

Jigna
on August 23, 2007 at 10:37 pm Mani

how do get day of Date?
eg: monday, friday
on August 24, 2007 at 11:52 am pinaldave

@Jigna,

You can create default constraint on any column with default value as getdate()

@Mani,
http://blog.sqlauthority.com/2007/07/23/sql-server-udf-get-the-day-of-the-week-function/
on September 5, 2007 at 8:18 pm shiruderura

how to retrieve “yyyy” from database
on September 5, 2007 at 11:05 pm veluthai

how to retrieve sql database server
on September 18, 2007 at 6:47 am Steve

When I use ISQL to execute “select getdate()” or “select current_timestamp” or “select {fn NOW()}” the timestamp returned is “yyyy-mm-dd hh:mm:ss.ttt”. When I run the same “select” in ADO the timestamp is returned as “dow-mmm-dd hh:mm:ss timezone yyyy”….what gives? I need the timestamp exactly as it appears in ISQL…

Thanks

Steve
on November 9, 2007 at 3:19 am ron

this query does not retrieve data?
select *
from Main_Table
where Todays_date <= convert(varchar,datepart(yyyy,getdate())) + ‘-’ + convert(varchar,datepart(mm,getdate())) + ‘-’ + convert(varchar,datepart(dd,getdate()))
on December 21, 2007 at 1:53 pm suri

hi

how to get previous date query.

thks
on January 16, 2008 at 2:08 am tran

I am trying to do something very simple, but can’t seem to get it to work. I am using a PHP include to update certain parts of my website. What I want to do is take the date in the start_date column add n number of days to it, then run the PHP script. Can anybody give me any help on this?

Thanks so much!!!!

Tran
on January 28, 2008 at 3:53 pm shahzahan

How i give current date from system in SQL.
on March 10, 2008 at 5:42 pm Ashlesh

Hi,
i was wondering if i can ask, if anyone knows how to reference the getdate() function in a table, in SQL express, from a vb.net form. My current query is

SELECT *
FROM table1
WHERE date = ‘textbox1.text ‘

this however does not work, but if i put the date in its binary date format in the textbox this works, i can select something, does anyone have any ideas about a conversion??…..

Thank you in advance.

ashlesh
on March 11, 2008 at 12:27 pm Lakshmi

hi, i need more queries about date and time.
i have a problem.
i) how to store only time with AM or PM notation in datetime type
ii) how display time with out milliseconds

i need immediate solution.

regards,

thanks.
on March 12, 2008 at 12:20 pm Vishwanath

Hi Lakshmi,

You may use the following :

SELECT LTRIM(RIGHT(CONVERT(CHAR(26),GETDATE(),109),14))

There might be a better way of doing it. Maybe Guru (Mr. Dave) can help us.

All the best.
on March 12, 2008 at 12:34 pm Vishwanath

Hi again Lakshmi,

You may also use the following :

SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),7)

Regards,
Vishwanath
on March 12, 2008 at 2:15 pm Vishwanath

Hi once again Lakshmi,

You may create a function to get the time part and can use the function wherever you want. Create a function as follows :

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)
RETURN @RetDate
END
GO

– Use the function like this :

SELECT [dbo].[udf_GetTime](GETDATE())
on March 17, 2008 at 5:26 pm Nitin Shetye

Hi Pinal,

create table employee
(
emp_id varchar(5) NOT NULL,
fname varchar(20)NOT NULL,
lname varchar(30)NOT NULL,
job_id smallint,
hire_date DATETIME DEFAULT GETDATE());

insert into employee values(’00003′,’Joe’,'Wys’,'0001′,”);

in above table, i need to get current datetime, but unfornately I am not getting it….follwoing is the result :–
00003 Joe Wys 1 1900-01-01 00:00:00.000

What may be the reason ?
on March 18, 2008 at 5:21 am Jason

Nitin -

You are overwriting your default value when you specify ” as your last value. You can not supply a value if you want the default to kick in. When you supply ”, the system puts the blank datetime in, which is 1900-01-01 00:00:00.000. The correct insert would be

INSERT INTO employee (emp_id, fname, lname, job_id) VALUES (’00003, ‘Joe’, ‘Wys’, ‘0001′);

This should keep the default value for the hire_date.
on March 21, 2008 at 3:44 pm Shakeer

hi,

I want to some project on SQL please provide me information about it.

Thank you,
Shakeer Basha
on April 1, 2008 at 4:31 pm Sarathi

Hi, please tell me how to get last month of current year in SQL Server?
on April 3, 2008 at 11:10 am Deepa

Hi,

how to calculate LOP in payslip using sql server?
on April 11, 2008 at 10:08 am puneet

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..
on April 11, 2008 at 12:27 pm puneet

can u correct this query , bcoz it’s nt working

select datediff(hh,select hire_date from employee,getdate())
on April 19, 2008 at 5:45 pm Amar

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.
on April 24, 2008 at 2:32 pm Kanika

I want extract only date and not time from getdate()
on May 1, 2008 at 10:25 am suketu

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
on May 1, 2008 at 8:07 pm Jill JOhnson

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
on May 3, 2008 at 10:05 am jayachandran

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.
on May 5, 2008 at 11:25 pm Nanthu

Hi,

I want the day of a given date. Like Monday, Tuesday. There is a similar question, but no answer. Pls help

Nanthu
on May 6, 2008 at 9:29 pm Kevin

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
on May 6, 2008 at 10:27 pm Alice

Hi, i need de getdate from my local computer..when i use the getdate() bring me the date from the server…

Tks…
on May 7, 2008 at 6:40 pm Teo

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!!!!
on May 7, 2008 at 7:11 pm Teo

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
on May 7, 2008 at 7:29 pm Alber

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)
on May 12, 2008 at 10:58 am Maha

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 …
on May 21, 2008 at 12:33 am kpn

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!
on May 24, 2008 at 4:26 pm vicknesh

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′})
on May 30, 2008 at 7:41 am esam

how I can select all record in last week
table action
{
Actiondate datrtime
………
primary key(Actiondate)
}
on June 2, 2008 at 7:33 pm VishwanathReddy

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.
on June 2, 2008 at 7:34 pm VishwanathReddy

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
on June 9, 2008 at 2:15 pm Kathir

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
on June 9, 2008 at 5:30 pm Darshan shah

Hi S. Ramkumar,
u can use convert function and u will get the value as u want

convert(datetime,getdate(),101)
on June 9, 2008 at 5:39 pm Darshan shah

Hi Kathir,

Can u please use this ?

convert(datetime,getdate(),108)
on June 9, 2008 at 5:41 pm Darshan shah

Hi esam,

can u plz use between clause?
on June 9, 2008 at 5:44 pm Darshan shah

Hi kpn,

can u please use datepart function and thn use ‘=’ condition.

datepart(yyyy,date)
on June 17, 2008 at 11:58 am krishna

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
on June 19, 2008 at 11:13 am Kabir

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??
on June 24, 2008 at 9:03 pm Nathan

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
on June 26, 2008 at 4:02 am kabir

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′
on June 26, 2008 at 11:21 am 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
on June 29, 2008 at 4:13 pm Jayesh

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
on July 2, 2008 at 4:10 pm Sanjana

Hi Kabir/Biplab,

Did you guys find any solution to the problem.

Thanks,
Sanjana
on July 22, 2008 at 2:17 pm tylo

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
on July 23, 2008 at 5:58 am Imran Mohammed

@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.
on July 28, 2008 at 5:13 pm sathish

i am struggling from one concept.let me any one help for me?

how to find difference b/w two dates with timings….
on July 29, 2008 at 7:57 am Imran Mohammed

@ sathish,

select datediff ( dd, ‘01.23.1985′, ‘02.23.1985′)

Result: 31

Hope this helps.

Thanks,
Imran.
on July 29, 2008 at 8:39 pm jitender Pal

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
on July 30, 2008 at 1:04 am Imran Mohammed

@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,
on July 30, 2008 at 6:36 pm jain

hi
I need to generate an expiry date baised on the date entered in one coloumn
can you help me out with this
on July 31, 2008 at 7:00 am SQL SERVER - 2008 - Find Current System Date Time and Time Offset Journey to SQL Authority with Pinal Dave

[...] SQL SERVER - Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} [...]
on August 9, 2008 at 9:28 pm premanand

hi,

I am new to sqlserver 2005, so pls help me

i have @date=10:10:10 AM (hh:mm:ss am/pm format) as varchar datatype.

I want to get the current sqlserver time as say @current_time and find the difference between @date and @current_time.

Thanks,
Prem.
on August 22, 2008 at 7:28 am jen

how am i able to change the sql server current date… by adding 1 day to it?… pls help… thanks a lot… God Bless!!!
on August 22, 2008 at 6:51 pm Archana

Hi
Can u please help
Iam having DateTime in database I need to compare only Time in the database with the current system time i.e comparing two times………………………
on August 30, 2008 at 5:16 pm DennisQuek.com

I was going thru my sch project and this is a stupid course of mine~. Lectures don’t cover all these in detail

Thanks for the GetDate function with a () , I guess it helped me to search for more resource and info on the net too !!!
on September 3, 2008 at 11:29 am bhavi

I want asp script which can help me to get a date after six month from date which is retrive from database.
on September 3, 2008 at 3:28 pm kim_sql

How can i display the detail which is after the day 4th march 1963
i tried this one but shows no rows selected.
where as there are rows after this date

select * from agents
where to_char(activation_date,’FMMonth DD, YY’) > ‘march 4, 1920′
;
please help
on September 12, 2008 at 5:45 pm Vijay

Jen,

U can add one day by code below.

select dateadd(dd, 1, getdate()) - add one day

select dateadd(mm, 1, getdate()) - add one month

select dateadd(yy, 1, getdate()) - add one year

Same way you can subtract day, month or year. Just give negative value.

select dateadd(dd, -1, getdate()) - subtract one day
on September 12, 2008 at 5:55 pm Vijay

hi Kim,

Please see below:

select * from agents
where replace(convert(varchar, activation_date, 102), ‘.’, ”) > 19200403
on September 16, 2008 at 11:22 am sanjay

how to give like % convert(varchar(10),dateadd(day,datediff(day,0,GetDate())- 10,0),103) % in sql query
on September 17, 2008 at 7:15 pm anil

can you help me to to get current date sql server 2005
on September 18, 2008 at 12:37 am satish

use “select getdate()” to get current date
on September 22, 2008 at 2:50 pm Raja

How to diplay the list of months in the current year ??
on October 1, 2008 at 11:24 am Sudhir Goswami

I want to know server date() in sql server 2005?
if i am not connect to server directly but i alreadt know server name
then how can is it possible???
on October 6, 2008 at 12:18 pm farhana

how to auto generate email after one day or 24 hours?

urgent!!
help!
on October 10, 2008 at 3:08 pm yagnesh

Hi Farhana,

you can maintain column in database which indicate status of email address generated or not.
For 24hr solution maintain one column which insert current date when data inserted in table.
you can query database and get last day records. After that say u have unique user name in your system or firstname or something that u maintain unique for all users. Take that field value and concatenate it with domain you want to auto generate like “hello@example.com” and send this details to user or update the data whatever task you want.

Hope this help you out.

Thanks
on October 11, 2008 at 3:59 pm brijesh

thanks!
on October 24, 2008 at 5:30 am vidhya

Hai,

for example the manager when check the report , that time one hour before records only to display and it store automatcally in path.

What is the query for that one?plz tell me step by step
on November 6, 2008 at 3:11 pm noha

SELECT @DateTime AS Expr1, LastModifiedDate
FROM dbo.tblProjectHistory
WHERE (LastModifiedDate = @DateTime) AND (RecordId = 1)

LastModifiedDate fields type date time
LastModifiedDate=23/10/2008 2:18:51 PM
@DateTime=23/10/2008 2:18:51 PM
when run Query result null