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 cf.Objective() 2014 (Bloomington, MN) with: Jeff McDowell and Joel Hill and Jonathan Rowny and Shawn Grigson and Jonathan Dowdle and Matt Vickers and Christian Ready and Asher Snyder and Clark Valberg and Oscar Arevalo and David Bainbridge

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 Deep thoughts 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.