Ben Nadel
On User Experience (UX) Design, JavaScript, ColdFusion, Node.js, Life, and Love.
I am the chief technical officer at InVision App, Inc - a prototyping and collaboration platform for designers, built by designers. I also rock out in JavaScript and ColdFusion 24x7.
Meanwhile on Twitter
Loading latest tweet...
Ben Nadel at RIA Unleashed (Nov. 2009) with:

Adding Numbers To Date/Time Values

By Ben Nadel on
Tags: SQL

Ok ok, last SQL date/time post I swear. But, while testing my last entry idea, I realized you can add numbers to your date/time values. Wicked cool:

  • SELECT
  • -- Query for yesterday's date.
  • (
  • getDate() - 1
  • ) AS yesterday,
  •  
  • -- Query for tomorrow's date.
  • (
  • getDate() + 1
  • ) AS tomorrow

Just note that this leaves in the TIME value as-is.

Tweet This Titillating read by @BenNadel - Adding Numbers To Date/Time Values Thanks my man — you rock the party that rocks the body!


Reader Comments

Hi,
I need to know how to Subtract "3 days 4 hours and 22 minutes" from the current date and print the date.

Reply to this Comment

@Santhosh,

Have you tried looking at the Date/Time functions built into SQL server? They must have something for it.

Reply to this Comment

Hi,
I have tried using date functions. But couldn't find exact.

The thing is if I type the date as such 24-08-2012 the output shd be 21-08-2012.
It shd be applicable for all the dates !!

Note : 3 days 4 hours and 22 minutes shd be included

I need the query for the scenario as its imp for one application.

Reply to this Comment

@santosh you can use dateAdd function to add or subtarct date time
<cfset date=dateAdd("d",-3,now() )>

Reply to this Comment

@keshav,
Thank u keshav. N also ive asked fr "3 days 4 hours and 22 minutes" in the format to be included !

Reply to this Comment

Thank u keshav. N also ive asked fr "3 days 4 hours and 22 minutes" in the format to be included !
Pl suggest !

Reply to this Comment

hello ben,
I need to create one exercise by using the Date function. Please help me.
Jus needed a sample query so tat i can wrk it out

Reply to this Comment

Hi,
This was the question:

"If the day is (Mon/Wed/Fri) print 'Date is Mon/Wed/Fri' "

THE ANSWER:
create procedure exe
@da_Yl date
as
begin
select day=case DATEPART(DW,@da_Yl)
when '1'then 'na'
when '2'then 'monday'
when '3'then 'na'
when '4'then 'wednes'
when '5'then 'na'
when '6'then 'fri'
when '7'then 'na'
end
end

Reply to this Comment

Post A Comment

You — Get Out Of My Dreams, Get Into My Comments
Live in the Now
Oops!
Comment Etiquette: Please do not post spam. Please keep the comments on-topic. Please do not post unrelated questions or large chunks of code. And, above all, please be nice to each other - we're trying to have a good conversation here.