Friday, February 17, 2012

datediff function

can i do a condition datediff eg
DATEDIFF(day, mydatecolumn, GETDATE()) as numberofdays
but i want to only calculate the days where mydatecolumn isn't null - is
this possible ?
Select DATEDIFF(day, mydatecolumn, GETDATE()) as numberofdays
>From SomeTable
where mydatecolumn IS NOT NULL
Or what do you want to to withthese rows not containing a date ?
Inserting 0 ?
Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays

>From SomeTable
where mydatecolumn IS NOT NULL
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||Sorry, corrected second one.
Inserting 0 ?
Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays

>From SomeTable
|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126605935.965506.214820@.g47g2000cwa.googlegr oups.com...
> Sorry, corrected second one.
> Inserting 0 ?
>
> Select ISNULL(DATEDIFF(day, mydatecolumn, GETDATE()),0) as numberofdays
>
thanks for the replies, did the job great, now how can i do this ?
(
select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as numberofdays
if mydatecolumn2 is null then select
isnull(datediff(day,mydatecolumn1,GETDATE()),0) as numberofdays2 )
sorry to be a PITA
mark
|||As a batch that should be (two resultssets)
select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as
numberofdays
if mydatecolumn2 is null
select isnull(datediff(day,mydatecolumn1,GETDATE()),0) as
numberofdays2
or in one statement
select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as
numberofdays,
ISNULL(mydatecolumn2,isnull(datediff(day,mydatecol umn1,GETDATE()),0),0)
as numberofdays2
Did I understand your requirement right?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1126609542.543399.165310@.g44g2000cwa.googlegr oups.com...
> As a batch that should be (two resultssets)
> select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as
> numberofdays
> if mydatecolumn2 is null
> select isnull(datediff(day,mydatecolumn1,GETDATE()),0) as
> numberofdays2
> or in one statement
> select isnull(datediff(day,mydatecolumn1,mydatecolumn2),0 ) as
> numberofdays,
> ISNULL(mydatecolumn2,isnull(datediff(day,mydatecol umn1,GETDATE()),0),0)
> as numberofdays2
> Did I understand your requirement right?
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
something like that
my query looks like this
select column1,column2,
datediff (day,column3,column4) as newcolumn1
then i need to do this :-
if column4 is null then select datediff(day,column3,getdate())
its almost there!, im cheating by pasting into excel !
thanks
mark
|||select column1,column2,
datediff (day,column3, ISNULL(column4, GetDate())) as newcolumn1
Nik Marshall-Blank MCSD/MCDBA
"mark" <mark@.remove.com> wrote in message
news:1126612753.14638.0@.ersa.uk.clara.net...
> "Jens" <Jens@.sqlserver2005.de> wrote in message
> news:1126609542.543399.165310@.g44g2000cwa.googlegr oups.com...
> something like that
> my query looks like this
> select column1,column2,
> datediff (day,column3,column4) as newcolumn1
> then i need to do this :-
> if column4 is null then select datediff(day,column3,getdate())
> its almost there!, im cheating by pasting into excel !
> thanks
> mark
>
>
>

No comments:

Post a Comment