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.googlegroups.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,mydate
column2),0) as numberofdays
if mydatecolumn2 is null then select
isnull(datediff(day,mydatecolumn1,GETDAT
E()),0) as numberofdays2 )
sorry to be a PITA
mark|||As a batch that should be (two resultssets)
select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as
numberofdays
if mydatecolumn2 is null
select isnull(datediff(day,mydatecolumn1,GETDAT
E()),0) as
numberofdays2
or in one statement
select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as
numberofdays,
ISNULL(mydatecolumn2,isnull(datediff(day
,mydatecolumn1,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.googlegroups.com...
> As a batch that should be (two resultssets)
> select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as
> numberofdays
> if mydatecolumn2 is null
> select isnull(datediff(day,mydatecolumn1,GETDAT
E()),0) as
> numberofdays2
> or in one statement
> select isnull(datediff(day,mydatecolumn1,mydate
column2),0) as
> numberofdays,
> ISNULL(mydatecolumn2,isnull(datediff(day
,mydatecolumn1,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.googlegroups.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
>
>
>
Friday, February 17, 2012
datediff function
Labels:
calculate,
condition,
database,
datediff,
egdatediff,
function,
getdate,
microsoft,
mydatecolumn,
mysql,
numberofdaysbut,
oracle,
server,
sql
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment