Conditional Formatting With DateDiff

I am attemting to write an expression that changes the background color if the difference between two dates is less than 60 days. I have this expression but it does not work. The color never changes.

=IIF (DateDiff("Day", Fields!pract_start_date.Value, Fields!project_start_date.Value) < 60,"Yellow","White")

Maybe you should wrap the DateDiff() with an ABS() in case the dates are in the wrong order in your function, resulting in a DateDiff of -60 rather than 60?

|||

That's exactly what was wrong. I had them in the wrong order. I thought I had checked for that but, evidently, did not. Thanks.

Conditional Formatting with a date

Hi all,
I have some dates in a recordset in english format dd/mm/yyyy
If the date returned is 1st Jan of any year then i just want the year to be
displayed.
i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
if the date is 1/3/2000 then return the whole date
Is this possible through conditional formatting?
--
Thanks in advance,
Dave HuntDave,
I don't think it will work via conditional formatting.
But you can use the following expresion for your value:
=IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
date.Value) with date.Value as your date return value.
Jan Pieter Posthuma
"DustpanDave" wrote:
> Hi all,
> I have some dates in a recordset in english format dd/mm/yyyy
> If the date returned is 1st Jan of any year then i just want the year to be
> displayed.
> i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> if the date is 1/3/2000 then return the whole date
> Is this possible through conditional formatting?
> --
> Thanks in advance,
> Dave Hunt|||hi jan,
=IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
1),'yyyy','MMMM yyyy')
If i do this code in the function of the properties of the field then i get
an error saying 'Expression expected'
would this code work if i put it in the value part of the field?
--
Thanks in advance,
Dave Hunt
"Jan Pieter Posthuma" wrote:
> Dave,
> I don't think it will work via conditional formatting.
> But you can use the following expresion for your value:
> =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> date.Value) with date.Value as your date return value.
> Jan Pieter Posthuma
> "DustpanDave" wrote:
> > Hi all,
> >
> > I have some dates in a recordset in english format dd/mm/yyyy
> >
> > If the date returned is 1st Jan of any year then i just want the year to be
> > displayed.
> >
> > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> >
> > if the date is 1/3/2000 then return the whole date
> >
> > Is this possible through conditional formatting?
> > --
> > Thanks in advance,
> > Dave Hunt|||Hi,
Something like this might work...?
=iif(datepart("d",Fields!StartDate.Value) &
datepart("m",Fields!StartDate.Value) =11,datepart("yyyy",Fields!StartDate.Value),Fields!StartDate.Value)
"DustpanDave" wrote:
> hi jan,
> =IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
> 1),'yyyy','MMMM yyyy')
> If i do this code in the function of the properties of the field then i get
> an error saying 'Expression expected'
> would this code work if i put it in the value part of the field?
> --
> Thanks in advance,
> Dave Hunt
>
> "Jan Pieter Posthuma" wrote:
> > Dave,
> >
> > I don't think it will work via conditional formatting.
> > But you can use the following expresion for your value:
> > =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> > date.Value) with date.Value as your date return value.
> >
> > Jan Pieter Posthuma
> >
> > "DustpanDave" wrote:
> >
> > > Hi all,
> > >
> > > I have some dates in a recordset in english format dd/mm/yyyy
> > >
> > > If the date returned is 1st Jan of any year then i just want the year to be
> > > displayed.
> > >
> > > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> > >
> > > if the date is 1/3/2000 then return the whole date
> > >
> > > Is this possible through conditional formatting?
> > > --
> > > Thanks in advance,
> > > Dave Hunt|||Dave,
You should use double quotes (") for the date format parts.
Jan Pieter Posthuma
"DustpanDave" wrote:
> hi jan,
> =IIF (Fields!StartDate.Value =New Date(Year(Fields!StartDate.Value), 1,
> 1),'yyyy','MMMM yyyy')
> If i do this code in the function of the properties of the field then i get
> an error saying 'Expression expected'
> would this code work if i put it in the value part of the field?
> --
> Thanks in advance,
> Dave Hunt
>
> "Jan Pieter Posthuma" wrote:
> > Dave,
> >
> > I don't think it will work via conditional formatting.
> > But you can use the following expresion for your value:
> > =IIF( date.Value = New Date(Year( date.Value), 1, 1), Year(date.Value),
> > date.Value) with date.Value as your date return value.
> >
> > Jan Pieter Posthuma
> >
> > "DustpanDave" wrote:
> >
> > > Hi all,
> > >
> > > I have some dates in a recordset in english format dd/mm/yyyy
> > >
> > > If the date returned is 1st Jan of any year then i just want the year to be
> > > displayed.
> > >
> > > i.e date returned = 1/1/2000 i just want to display the year so... (yyyy)
> > >
> > > if the date is 1/3/2000 then return the whole date
> > >
> > > Is this possible through conditional formatting?
> > > --
> > > Thanks in advance,
> > > Dave Hunt

Concatenating Numbers/Converting Dates to Integers

My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:

SET @.Macola = Cast(Datepart(yy,@.MacolaDate) as varchar) + Cast(Datepart(mm,@.MacolaDate) as varchar) + Cast(Datepart(dd,@.MacolaDate) as varchar)

However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.just to be sure
what is the type of your variable @.Macola

doeas it HAVE to be integer ?|||Well, I am working with a database design that I cannot modify and it stores the dates in an int column with the format 20040101 and any conversions to the 100,000 values in the table takes too long, so I want to convert my date to an integer.|||i'm not sure if this is what you want

but create this user define function

CREATE function dbo.Date(@.Date DateTime)
returns Varchar(10) as
begin
return(
Cast(Datepart(yy,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(mm,@.Date) as varchar)))+Cast(Datepart(mm,@.Date) as varchar) +
Replicate('0',2-len(Cast(Datepart(dd,@.Date) as varchar)))+Cast(Datepart(dd,@.Date) as varchar)
)
end

then you'll be able to get easily

set @.Macola=dbo.date('1/1/2004')
set @.Macola=dbo.date(@.MacolaDate)

the Replicate+Len functions adds the 0 if necessary|||Thank you!|||is it what you needed ('cause i'm going offline)|||Yes it is, I appreciate your help.|||Cross post?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32771

Anyway, what does your base data look like

Since you say you want it to be 20040101, are you assuming that that's the int value?

Where do you get 1/1/2004 from?

Some ddl might help

I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields,

That part confused me...|||Sorry for any confusion. Let me try this again.

I need to pass StartDate and EndDate parameters to the query I use as the record-source for several reports.

My database stores dates as integers in the format 20040101, but I don't want my end users to have to enter dates in that integer format, also, I figured it would be easier to convert the integer dates to datetimes in the query so that I wouldn't have to make this conversion on every report, it would already be done for me.

I wrote a T-SQL scalar function that converts from the integer format to a datetime format, and I just used that 'fnIntToDate(doc_date)' as the column returned in the header for the 3 date columns I have. This function looks like this, and works well:

Convert(Datetime,Left(Right(@.MacolaDate,4),2) + '/' + Right(@.MacolaDate,2) + '/' + Left(@.MacolaDate,4))

I do my development on a database with 1,000 rows, but our production database has over 100,000, and this date conversion (along with a few other things I have since fixed) caused the query to time-out. And according the the query analyzer, I can save 3-4 seconds PER COLUMN if I just leave the integers as integers and convert my date parameters to integers instead.

So I'm asking for help on a function that does the opposite of what my function does. I want to pass it a datetime value and have it return a date in the format 20040101. I had gotten all of it except the leading 0s, but I think the previous poster answered that for me, so i'm goign to go try that now.

Thank you.|||And what he told me worked great :). I just converted it to an integer and I'm good to go.