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://blog.SQLAuthority.com) , BOL (All Definitions)

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 143 Comments

143 Responses

on June 25, 2007 at 6:42 am | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Mani

how do get day of Date?
eg: monday, friday
on August 24, 2007 at 11:52 am | Reply 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 | Reply shiruderura

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

how to retrieve sql database server
on September 18, 2007 at 6:47 am | Reply 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 | Reply 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 | Reply suri

hi

how to get previous date query.

thks
on January 16, 2008 at 2:08 am | Reply 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 | Reply shahzahan

How i give current date from system in SQL.
on March 10, 2008 at 5:42 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Sarathi

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

Hi,

how to calculate LOP in payslip using sql server?
on April 11, 2008 at 10:08 am | Reply 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 | Reply 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 | Reply 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 | Reply Kanika

I want extract only date and not time from getdate()
on May 1, 2008 at 10:25 am | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Darshan shah

Hi Kathir,

Can u please use this ?

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

Hi esam,

can u plz use between clause?
on June 9, 2008 at 5:44 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply Sanjana

Hi Kabir/Biplab,

Did you guys find any solution to the problem.

Thanks,
Sanjana
on July 22, 2008 at 2:17 pm | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply 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 | Reply anil

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

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

How to diplay the list of months in the current year ??
on October 1, 2008 at 11:24 am | Reply 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 | Reply farhana

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

urgent!!
help!
on October 10, 2008 at 3:08 pm | Reply 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 | Reply brijesh

thanks!
on October 24, 2008 at 5:30 am | Reply 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 | Reply 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
on November 21, 2008 at 11:40 pm | Reply Rosales

the Answer to the getdate issue is simply to put Default
i.e
Insert into tableName(v1,va2,datetime)
values(‘myself’,'yourself’,default)

done.
on December 3, 2008 at 5:51 pm | Reply ashish

i want select Second highest date from table.
on December 4, 2008 at 11:17 pm | Reply BKW

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?
on December 17, 2008 at 6:19 pm | Reply Dhana

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
on December 19, 2008 at 11:21 am | Reply Tejas Shah

Hi Dhana,

you can use this: DATEPART(s,GETDATE())

Let me know if you want it in other formats too..

Thanks,

Tejas
on January 14, 2009 at 2:13 pm | Reply badari

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
on January 19, 2009 at 4:08 pm | Reply mosa'ab

how make report take date from text (from date to date)
only date between two text. thanks
on January 20, 2009 at 10:20 am | Reply Arvind

Sir ,
I wnat only time in the Sqlserver2005
on January 20, 2009 at 11:56 am | Reply ShameerKhan

Sir,

Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005

Thanks
Shameer
on January 20, 2009 at 6:27 pm | Reply Tejas Shah

Hi Arvind,

If you need to get Only Time, you can use

CONVERT(VARCHAR(10), GetDATE(),108).

For more details: http://tejasnshah.wordpress.com/2008/12/22/sql-server-get-time-in-ampm-format/

Regards,

Tejas
on February 10, 2009 at 11:16 pm | Reply BluEiS

================================
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)
on February 11, 2009 at 5:55 pm | Reply manu

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
on February 13, 2009 at 7:51 am | Reply Naomi

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?
on February 18, 2009 at 5:00 am | Reply Sunnysak

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.
on February 24, 2009 at 5:39 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} [...]
on February 24, 2009 at 7:52 pm | Reply Avinash

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.
on February 25, 2009 at 11:09 pm | Reply SQL SERVER - Top Five Articles of Year 2008 Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Retrieve Current Date Time in SQL Server CURRENT_TIMESTAMP, GETDATE(), {fn NOW()} Easiest way to get date time in SQL Server [...]
on February 26, 2009 at 1:28 pm | Reply Tejpal

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
on March 10, 2009 at 3:50 pm | Reply Haj

Use this query

select * from result where
Convert(varchar(10),invitiondate,103) = Convert(varchar(10),getdate(),103)
on March 13, 2009 at 3:13 am | Reply Peter

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!
on March 13, 2009 at 11:06 am | Reply tejasnshah

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
on March 13, 2009 at 7:00 pm | Reply Brian Tkatch

@Peter

select *
from workorder
order by timecreated
where timecreated >= CONVERT(DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1);

That’s if none are in the future. If there are future entries, BETWEEN is required:

select *
from workorder
order by timecreated
where timecreated BETWEEN DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1) AND DATEADD(d, 1, CONVERT(DATETIME, CONVERT(VARCHAR(8), CURRENT_TIMESTAMP, 1), 1))

@tejasnshah

Pinal’s best practices rule #5 is “Avoid functions on columns.” http://blog.sqlauthority.com/2008/04/25/sql-server-optimization-rules-of-thumb-best-practices/

our solution puts a FUNCTION on the COLUMN itself.
- on March 16, 2009 at 10:41 pm pinaldave
  
  Brian Tkatch,
  
  I like very much your comment. Please continue to help community like this.
  
  Kind Regards,
  Pinal
on March 15, 2009 at 12:11 am | Reply harsha

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
on March 17, 2009 at 9:50 am | Reply Zubaria

Sir

I want to retrieve all the audit logs between previous date and current date.

How can i write this ??
on April 14, 2009 at 5:18 am | Reply VICENCIO

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
on April 22, 2009 at 1:00 pm | Reply somalia

I am using (right(CONVERT([char](8),getdate(),(108)),(10))) to get only the time part,however it is not showing the AM,PM part.How can I get the AM,PM part.
Please suggest.

Thank You
on April 22, 2009 at 6:14 pm | Reply Brian Tkatch

Somalia, look at the documentation on CONVERT to see what formats support am/pm. I see 100, 109, 130, and 131.
on May 21, 2009 at 11:44 am | Reply Roshani

hi,

I want to fetch todays date from the whole database , not from any particular Table and that is already in database. please help me…!!!
on May 25, 2009 at 3:51 pm | Reply shmartaf

I think GETDATE() works more quckly than {fn Now()}
on May 30, 2009 at 11:02 am | Reply Shashank

hi,
I want to find the difference between the current date and the date stored in the database(I used datetime datatype to store the dates in the database).
I m new to the sql server pleeeeeeeeeeeeese help!!!!
on June 11, 2009 at 11:18 am | Reply Jodie

Hi!

I need a bit of unique help.
Is there any way to retrieve the last updated records (for the last week say) if you DO NOT have the date stored anywhere in your table?

I know this is a dumb question but then again it’s kind of a silly mistake to make when creating the table! N what the hell… it’s worth a shot rite?

Could someone please tell me if they think this is at all remotely possible?
on June 14, 2009 at 12:30 pm | Reply smd

am pretty new to sql queries…

in the below query
‘———————————–
SELECT USERS.USERS_NAME, COMPANY.COMPANY_NAME, LOGS.LOGS_DATETIME, LOGS.LOGS_ACTION
FROM LOGS INNER JOIN
COMPANY ON LOGS.LOGS_COMPANY_ID = COMPANY.COMPANY_ID INNER JOIN
USERS ON LOGS.LOGS_USERS_ID = USERS.USERS_ID
WHERE (LOGS.LOGS_COMPANY_ID = 11) AND (LOGS.LOGS_ACTION LIKE ‘uploaded%’) AND (LOGS.LOGS_DATETIME = GETDATE()) OR
(LOGS.LOGS_ACTION LIKE ‘downloaded%’)
ORDER BY LOGS.LOGS_DATETIME DESC
‘———————————–
—i need to filter records from the log table which as uploaded and downloaded in logs_action field.

this query should be run on the current date and teh cutoff time is 5.30pm for e.g. the query should run for today date and any file uploaded or downloaded before 5.30pm today should be shown.
thanks a ton
on July 6, 2009 at 7:57 pm | Reply Maegen Wilson

Hello,

I am trying to get the number of days between a date span.
If I have a span of three months I would like it to appear like the example down below. I would have the parameter as 01/01/2009 – 03/31/2009, but want the results split out. Can anyone help?

Jan xxhours
Feb xxhours
March xxhours
on July 6, 2009 at 10:37 pm | Reply Brian Tkatch

@Maegen

Look at DATEDIFF() to find the intervals. If you have difficulty, post what you have tried and perhaps we can help.
on July 13, 2009 at 10:17 am | Reply vishnu vardhan

Hi,
I need to get details from attandence table
date,timein,timeout by chid id.

In this table stroring one day n times timein and time out. But i need day first time in and day last time out
plz send the result for this if any one know this is my mail id
on July 23, 2009 at 1:15 pm | Reply Dexter V. dela Paz

Hello to everyone

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS SystemDate, LEFT(RIGHT(CONVERT(varchar, GETDATE(), 0), 8), 6) + ‘ ‘ + RIGHT(CONVERT(varchar, GETDATE(), 0), 2) AS SystemTime

Just copy the code above to obtain the Date and Time separate in each column the time format is HH:MM AM/PM that will vary in your system time and the date format is MM/DD/YYYY.

Sample Output:

SystemDate SystemTime
07/23/2009 3:59 PM

I hope you find it useful.

God Bless to all :)
on July 23, 2009 at 1:19 pm | Reply Dexter V. dela Paz

Hello to everyone

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [SystemDate], LEFT(RIGHT(CONVERT(varchar, GETDATE(), 0), 8), 6) + ‘ ‘ + RIGHT(CONVERT(varchar, GETDATE(), 0), 2) AS [SystemTime]

Just copy the code above to SQL Query or View to obtain the Date and Time separate in each column the time format is HH:MM AM/PM that will vary in your system time and the date format is MM/DD/YYYY. There’s so many person want to know how will you format time in HH:MM AM/PM now here’s the answer. luckily i find it out. :)

Sample Output:

SystemDate SystemTime
07/23/2009 3:59 PM

I hope you find it useful.

God Bless to all :)
on July 24, 2009 at 4:45 pm | Reply Gareth

If you want UTC date time use:

SELECT GETUTCDATE();

Gareth.
on August 5, 2009 at 2:11 pm | Reply Mr. Jame

Dear all
i would like query from my table but we have many data record the same infor but i need query only on colum
example depent only one type
in my table have

Row —mobile name—-type—–creae date—–install date
1 mobile1 A 03/05/2009 03/04/2009
1 mobile1 A 07/06/2009 07/06/2009
1 mobile1 A 05/07/2009 05/07/2009
1 mobile1 A 12/08/2009 12/08/2009

for user normall i use
mobile1 A 12/08/2009 12/08/2009

Pleae help me because we have more information
on August 6, 2009 at 1:55 pm | Reply lutfi

dear all

I want to make query for the same minute of transactions table, does any one help me

thanks
on August 10, 2009 at 11:45 am | Reply Vineeta

Hi,

I want to create database backup file with current date and time.
For example : database name is : RemoteDatabase
Current Date Time is : 10 August 2009 11:29 AM

How can I give this name to my .bak file?

Or if it is not possible can I provide time only like
RemoteDatabase_11:29AM.bak

Thanks
Vineeta
on August 10, 2009 at 5:32 pm | Reply Lucas

Is it possible to put GetDate() or CURRENT_TIMESTAMP as a field’s default value? If yes, when I update a row, will this field update itself with the the timestamp of that update moment?
on August 11, 2009 at 8:22 am | Reply Imran Mohammed

@Lucas

1. First question, Can Getdate() be used as default value to a column ?
Ans: Yes, GetDate() can be used as a default value for a column.

2. Second Question, when I update a row, will this field update itself ?
Ans: No. It will not update by itself, you will have to update this manually.

Consider below script as example.

create table #Example1 ( Eid Int, Ename varchar(40), Date_Time datetime default (Getdate()))

Insert into #Example1 (Eid, Ename) values ( 1, ‘SQL’ )
Insert into #Example1 (Eid, Ename) values ( 2, ‘SQQ’ )

Select * from #Example1

– Now update statment.

Update #Example1
Set Ename = ‘SQL’
, Date_Time = Getdate() — updating manually.
Where Eid = 2

Select * From #Example1.

Drop table #Example1.

How ever, this could be done automatically as well,

You can have a FOR UPDATE or AFTER UPDATE Trigger created on the table.

This trigger will fire when there is any update on the table, and inside this trigger, you can update your datetime column with the current datetime.

You can also use, if update statement inside trigger, to be specific, mentioning if there is any update for these specific columns only then update date_time column.

I dont suggest using trigger though, if you see many updates on the table. This could decrease your application performance significantly.

~ IM.
on September 8, 2009 at 12:33 pm | Reply samsi

HI all,

can i retrieve the values for time between 5:00 pm and 8:00 pm from apptime column in database, does any one help me
on September 14, 2009 at 2:22 pm | Reply Mazhar

Hi All,

Can anyone tell me how to find the difference of (year/month/day) birth date to till date.

i see the imran code but it is not perfect. I have to count actual year,month and date.

thanks,
Mazhar
on September 16, 2009 at 3:30 pm | Reply Dharmendra Dixit

@Kanika,

you can do that by using Datetime conversion functions available in SQL..
like
select convert(varchar(10),getdate(),105)
on October 6, 2009 at 12:54 pm | Reply Turker Tunali

Can we select any of them as field’s Default Value if field type is DateTime?

Or

Is there any solution for that?

I need a column if it doesn’t specified on insert it’s default value should be current date time!
on October 7, 2009 at 11:59 am | Reply shubhangi

Sir,

Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005 & ms-access
on October 7, 2009 at 12:00 pm | Reply shubhangi

Sir,

Will you please tell me how to format Date as “mmm-dd-yyyy hh:mm” in SQL server 2005 & in Ms-Access.
on October 13, 2009 at 7:56 pm | Reply Sultan Ibrahim

Converting UTC datetime to a desired format like 2009-01-08 18:26:54.923 instead of 2008-06-18T13:51:46.7-05:00
on October 20, 2009 at 9:34 pm | Reply si

Thanks for your post.

BTW the only one of the three that works within a DTS package against a legacy KB-SQL ODBC datasource is {fn NOW()}.

I’m a newbie, and am guessing it is because the others aren’t understood by the KB-SQL’s flavour of SQL?

Anyway, very glad that I remembered your post (which I’d already referred to for straight MSSQL queries) and found that one out of three worked in the context of what I was doing.

So, while they’re all the same performance-wise, there is a crucial difference in breadth of application.

Thanks again.
on October 22, 2009 at 1:11 pm | Reply vandana

hi
i have problem in inserting the record depending on the current date. if current date change then record has to be inserted automatically.plz tel me how to write trigger or jobs in sql server2000
on October 26, 2009 at 2:33 pm | Reply pat

Actually i’m finding that

CURRENT_TIMESTAMP is correct, it returns the full date and time :
2009-10-26 04:58:24

GEDATE returns always return a time of 0 ( the date is correct though )

example : 2009-10-26 00:00:00

Si in my case i prefered to use CURRENT_TIMESTAMP because i sort my news by time, and i might have several news an hour.

Pat.
on October 27, 2009 at 1:14 am | Reply SA

Hi,

I need help on writing a query for both these scenarios :

1. The current date is equal to or greater than 92 days past a semester start date.
2. The current date is not greater than 92 days past a semester start date.

Semester start date is a column..
Thank you..
on October 28, 2009 at 1:37 am | Reply moath

Hi,
I am new in sql server,
i have some problem ,
like when i write this code

select * from [some table] where
convert (datetime,’1/5/2009′,103′) >= getDate()

when return the record is lose some of them .

i hope help me .

thanks
on October 28, 2009 at 4:52 am | Reply Jay

Hi all,

I have table in which hiredate is column and i want to calculate, Using HireDate of an employee the time they have worked. Basically i want to calculate the difference between hire date and current date of my system.

Thanks a lot in advance.

Jay
on October 28, 2009 at 7:16 pm | Reply Sultan Ibrahim

print datediff(day, ‘2009-10-01′, getdate())

will give you 27, because I used ‘day’ as the first parameter.

http://technet.microsoft.com/en-us/library/ms189794.aspx
on November 2, 2009 at 11:04 pm | Reply karthick

Select Replace(Convert(Varchar,GetDate(),106),’ ‘,’-')