Daylite saving time problem

Hello
I am using SQL Server 2000, SP4
I am calculating number of hours passed between two dates. Both dates have
time set to 00:00:00. I use datediff function it works ok unless the time
interval I pass includes date when time is changed due to Daylite Saving Tim
e
(DST) issue. Instead of one hour more or one hour less datediff keeps
returning constant number of hours.
Does SQL Server 2000 internally support DST depending on a regional settings
in OS?
Thanks in advance.we don't that feature in SQL Server to my knowledge. You can write a UDF to
do the conversion.
Check out this link
http://www.planet-source-code.com/U...cripts/ShowCode!asp/txtCodeId!9
11/lngWid!5/anyname.htm|||Thanks a lot|||Some ideas here maybe:
http://www.aspfaq.com/2218
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||Oh, and also the calendar table.
http://www.aspfaq.com/2519
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in
message news:98FFD320-5011-4E65-A718-B6AAA1560AA8@.microsoft.com...
> Hello
> I am using SQL Server 2000, SP4
> I am calculating number of hours passed between two dates. Both dates have
> time set to 00:00:00. I use datediff function it works ok unless the time
> interval I pass includes date when time is changed due to Daylite Saving
> Time
> (DST) issue. Instead of one hour more or one hour less datediff keeps
> returning constant number of hours.
> Does SQL Server 2000 internally support DST depending on a regional
> settings
> in OS?
> Thanks in advance.|||or how about rather than using getdate() to get the two dates in the
first place, use getutcdate() function?
GETUTCDATE
Returns the datetime value representing the current UTC time (Universal
Time Coordinate or Greenwich Mean Time). The current UTC time is
derived from the current local time and the time zone setting in the
operating system of the computer on which SQL Server is running.
Mel|||> or how about rather than using getdate() to get the two dates in the
> first place, use getutcdate() function?
> GETUTCDATE
> Returns the datetime value representing the current UTC time (Universal
> Time Coordinate or Greenwich Mean Time). The current UTC time is
> derived from the current local time and the time zone setting in the
> operating system of the computer on which SQL Server is running.
Well, if you're comparing two datetime values:
2005-12-31
2006-06-01
If you're in a timezone that observes daylight savings time, your
calculation is going to be an hour off (which way depends on what is
currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
an hour off in the other direction the next time the daylight savings time
goes on or off.
The calendar table can help solve this problem by giving you the offset on
each of the dates in question, allowing you to adjust each date accordingly.|||You also have to take into account that different areas change their clocks
on different dates, so you may need to create a second table with each time
zone and the date/time that they change their clocks.
That, and some areas (Arizona for example) do not use daylight savings time
at all.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OES60M7YGHA.4144@.TK2MSFTNGP04.phx.gbl...
> Well, if you're comparing two datetime values:
> 2005-12-31
> 2006-06-01
> If you're in a timezone that observes daylight savings time, your
> calculation is going to be an hour off (which way depends on what is
> currently yielded from DATEDIFF(HOUR, GETDATE(), GETUTCDATE()) and will be
> an hour off in the other direction the next time the daylight savings time
> goes on or off.
> The calendar table can help solve this problem by giving you the offset on
> each of the dates in question, allowing you to adjust each date
accordingly.
>|||> You also have to take into account that different areas change their
> clocks
> on different dates, so you may need to create a second table with each
> time
> zone and the date/time that they change their clocks.
Or an extra column for each timezone (reproduce the tinyints instead of the
wider date values).

> That, and some areas (Arizona for example) do not use daylight savings
> time
> at all.
Right, Indiana just changed. Next year, the formula for determining the
dates changed in the US, so I think a lot of people who hav used an inline
calculation for this are either already working on fixing it or have plenty
of work to do over the winter. Since we used a calendar table in all of our
implementations, we don't have to worry about it... a simple update
statement corrects all future data until they waffle again.|||a column for each timezone seems much more complex than a single table with
one row each.
However, the benefit to doing it with columns is that you don't run into
problems when the timezone rules change. In the case of Indiana, you would
update the Indiana column in the calendar table for those date ranges. With
a separate table you would need to store the date that the rules changed and
always make sure you are joining to the correct row. I think I like your
idea of multiple columns better.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O%23NxvE8YGHA.4652@.TK2MSFTNGP04.phx.gbl...
> Or an extra column for each timezone (reproduce the tinyints instead of
the
> wider date values).
>
> Right, Indiana just changed. Next year, the formula for determining the
> dates changed in the US, so I think a lot of people who hav used an inline
> calculation for this are either already working on fixing it or have
plenty
> of work to do over the winter. Since we used a calendar table in all of
our
> implementations, we don't have to worry about it... a simple update
> statement corrects all future data until they waffle again.
>

DateTime using DateDiff

Hello everyone.

Im currently using the DateDiff function to filter my DateTime columns but am finding it somewhat troublesome. Currently I am having to write the same select statement 3 times if I want to filter by month, year or all (ignoring dates).

To find @.PurchaseTotal for the year, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
 DateDiff(yyyy, DateOf, @.IntervalDate) = @.Interval

To find @.PurchaseTotal for a month, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses
WHERE
 DateDiff(mm, DateOf, @.IntervalDate) = @.Interval

To find @.PurchaseTotal for all the records, I have to write the following:

SELECT
 @.PurchaseTotal = Sum(PurchaseTotal)
FROM
_Expenses

I've tried the following code but I get an error.

DateDiff(@.DateParameter, DateOf, @.IntervalDate) = @.Interval

Error says something like "incorrect parameter 1 for DateDiff."

It seems you have to write a different select statement for month, day and year. Also If you want a total from all the records you have to write yet another select statement.

Does anyone know of a DateTime function that allows parameters to specify for month, day and Year? Also does anyone know of a DateTime function that works like the COALESCE function to where you can send it a NULL value and give you all the records?

Thank you ahead for any direction you can give.

AlecGratulations for choosingthe worst possible approach.

::WHERE DateDiff(mm, DateOf, @.IntervalDate) = @.Interval

Means, in SQL Server language: DO NOT USE AN INDEX.

Why do you not go the easy way?

::WHERE DateOf BETWEEN @.StartDate and @.EndDate

which is WAY less processing for SQL Server, allows it to use an index and in general is faster?

DateTime comparison with some exceptions

I have StratDateTime and EndDateTime fields in the table. I need to compare this two datetime fields and find seconds. I can use DateDiff but there are the following exceptions:

1. Exclude seconds coming from the date which are Saturday and Sunday

2. Exclude seconds coming from time range between 7:01pm and 6:59am

3. Exclude seconds coming from Jan 1^st and Jul 4^th.

So do you want to make the difference between the two columns in seconds a column in the query results or do you want to compare them to each other or some other values in the WHERE clause of the query? I'd suggest that you post the query you have written and then one of us can help you with the query, as it is you have not really posted enough information for us to help you.|||

Ok. Thank you very much for your response.

SELECT Datediff(ss,StartDateTime,EndDateTime) AS mySeconds

FROM MyTable

This will return seconds. However this does not hold all the exceptions I listed above. Let’s say I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

|||This is the same question asked by you before

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1022431&SiteID=1

I will again suggest you to use the calender-table. This will make life easy as you are not able to know 12/16/2006, is Saturday or working day.
With calender-table you can easily find that, more over you can create the holiday list too, find the difference between any date & more functionality can be add according to your own requirements.

Gurpreet S. Gill|||

Thank you very much for your help. That does not work for me since it is considering the day, not the time. My business day should be between 7:00am and 7:00pm in the weekdays. I do not see how getting number of business days would really help.

I would ask the same question, let’s say I have a calendar table, how would I get calendar table return me 2 hours for the following example. I have StartDateTime=12/15/2006 7:00pm and EndDateTime=12/18/2006 9:00am, then the difference should be 2 hours because between 12/15/2006 7:00pm and 12/18/2006 7:00am is not a business period, the rest is 2 business hours.

Thanks you very much for your help.

|||

What you need to do is create a user defined function to calculate your desired value. It will look like this, I haven't put in all the conditional code for you, that will take a while, but you get the idea.

CREATE FUNCTION BusinessSeconds(@.StartTime datetime, @.EndTime datetime)
RETURNS int
AS
BEGIN
DECLARE @.retVal int
SET @.retVal = datediff(ss, @.StartTime, @.EndTime)
--Conditional code here to subtract your non-business periods
--eg. IF ... SET @.retVal = @.retVal - 86400
RETURN @.retVal
END

And you'll use it like this

SELECT dbo.BusinessSeconds(StartDateTime, EndDateTime) AS MySeconds
FROM MyTable

datetime

i'm using datediff to get the elapsed time b/t a timestamp and the current time. at this point i'm putting the answer in minutes, but i would like to format it to be similar to HH:MM.
how do i do this in sql server??
thanks in advance
e3wittselect cast(datediff(mi,'05/28/2004',getdate())/60 as varchar)+':'
+cast(datediff(mi,'05/28/2004',getdate())-(datediff(mi,'05/28/2004',getdate())/60)*60 as varchar)|||SET ANSI_NULLS OFF
SET NOCOUNT ON
GO

if object_id(N'dbo.fn_ElapsedTime') is not null begin
drop function dbo.fn_ElapsedTime
print 'Function dbo.fn_ElapsedTime dropped'
end
go

CREATE function fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null)
returns varchar(40)
as
begin
declare @.d int, @.h int, @.m int, @.s int, @.ms int, @.dif1 int, @.ret varchar(40)
select @.d = 0, @.h = 0, @.m = 0, @.s = 0, @.ms = 0

set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + ':' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end
go

if object_id(N'dbo.fn_ElapsedTime') is not null begin
print 'Function dbo.fn_ElapsedTime created'
end
go|||ok... now it's working just the way i was wanting.

thank you.|||Don't forget about the modulo operator (%). It's hand for converting time values:

select cast(datediff(mi, [TimeStamp], getdate())/60 as int) + ':' + (datediff(mi, [TimeStamp], getdate()) % 60)

DATEPART and DATEDIFF using VARCHAR(24) Date Format

My counterdatetime field format is varchar(24) listed below.
This format cannot be changed because it's output from perfmon. How can I
change the sql query to recognize DATEPART and DATEDIFF with my
counterdatetime field in varchar(24) format.
Please help me resolve the problem.
Thank You,
select a.counterdatetime, t.countername, avg (a.countervalue)
from counterdata a (NOLOCK),
counterdetails t (NOLOCK)
where a.counterdatetime > '2005-01-20'
AND a.CounterID = t.CounterID
AND t.countername like 'Data File(s) Size (KB)'
AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
group by a.counterdatetime, t.countername
order by a.counterdatetime
Error:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
a.counterdatetime
2005-01-20 00:00:35.316
2005-01-20 00:01:35.316Joe,
You should have stored the data in the table as a datetime datatype iand not
a character. In any case try setting the dateformat and see if that helps:
SET DATEFORMAT YMD
Andrew J. Kelly SQL MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:55ABEE1A-2D7B-470E-9D93-AFDE09F135A6@.microsoft.com...
> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>|||try this
convert(datetime,@.counterdatetime, 101)
Thanks,
RK
"Joe K." wrote:

> My counterdatetime field format is varchar(24) listed below.
> This format cannot be changed because it's output from perfmon. How can I
> change the sql query to recognize DATEPART and DATEDIFF with my
> counterdatetime field in varchar(24) format.
> Please help me resolve the problem.
> Thank You,
> select a.counterdatetime, t.countername, avg (a.countervalue)
> from counterdata a (NOLOCK),
> counterdetails t (NOLOCK)
> where a.counterdatetime > '2005-01-20'
> AND a.CounterID = t.CounterID
> AND t.countername like 'Data File(s) Size (KB)'
> AND DATEPART(hh,a.counterdatetime) BETWEEN 8 AND 17
> AND DATEPART(wday,a.counterdatetime) BETWEEN 2 AND 6
> group by a.counterdatetime, t.countername
> order by a.counterdatetime
> Error:
> Server: Msg 241, Level 16, State 1, Line 1
> Syntax error converting datetime from character string.
> a.counterdatetime
> 2005-01-20 00:00:35.316
> 2005-01-20 00:01:35.316
>
>

DateDiff: Calculating working days

I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?

Yes, this is possible; however, normally you must account for holidays and when all is said and done I would suggest that you give a look to this article:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

This discusses establishing a calendar table which ought to help with computing "working days".

( Adolf: what does "NB" stand for? )

|||NB this article doesn't discuss different sets of holidays

e.g. england, wales, scotland, ireland

have different public holidays despite being in the UK

you may wish to add extra columns to cater for this|||thank you.. I've decided to pinch the calendar table off another db and will try to follow the examples.

DateDiff: Calculating working days

I am trying to use the DateDiff function to calculate the difference between two dates in working days only... Is this possible in SSRS 2005, or can anyone suggest an alternate solution?

Yes, this is possible; however, normally you must account for holidays and when all is said and done I would suggest that you give a look to this article:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

This discusses establishing a calendar table which ought to help with computing "working days".

( Adolf: what does "NB" stand for? )

|||NB this article doesn't discuss different sets of holidays

e.g. england, wales, scotland, ireland

have different public holidays despite being in the UK

you may wish to add extra columns to cater for this|||thank you.. I've decided to pinch the calendar table off another db and will try to follow the examples.

DATEDIFF() function in SQL server

i know this is specifically more an SQL server question than an ADO.NET question, but if anybody can clear this up for me that'd be great.

if i have a filter like the following:

WHERE DATEDIFF(day, H.HitDate, getdate()) = 1

is this going to work over the new year? what i mean is, if i run the query on january 1st, will it get all the rows from december 31st the previous year (and day)?

any help is greatly appreciatedYes, it will.

Prove it to yourself by running this code:

DECLARE @.myThisYearDate datetime
DECLARE @.myNextYearDate datetime
SET @.myThisYearDate = '12/31/2003'
SET @.myNextYearDate = '01/01/2004'
PRINT DATEDIFF(day,@.myThisYearDate,@.myNextYearDate)

Terri|||k, that seems to be working great.

thanks

datediff() alters other values in nested iif

i have a nested IIF statement, see below, that evaluates all possible field
values of a particular field, and outputs appropriate text. The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"
I convert the last possible value type to date by concatenation:
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
The report works fine like this , but once I introduce the DATEDIFF(),
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
generating a invalid date function. In other words, instead of the values
"FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
suitable values that can be converted to a date format, they too are
subjected to CDATE(). You can clearly see this in the error below as "BASIC"
is converted to "01/IC/BA"
complete IIF expression:
=IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))= "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,"OLD","FASHION")))
error generated when DATEDIFF() is introduced:
Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textbox
â'textbox25â' contains an error: Conversion from string "01/IC/BA" to type
'Date' is not valid.
thanks for you help in advance.
anthonyinstead of using cdate, do you think datevalue may work better?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>i have a nested IIF statement, see below, that evaluates all possible field
> values of a particular field, and outputs appropriate text. The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"
> I convert the last possible value type to date by concatenation:
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> The report works fine like this , but once I introduce the DATEDIFF(),
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> generating a invalid date function. In other words, instead of the values
> "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> suitable values that can be converted to a date format, they too are
> subjected to CDATE(). You can clearly see this in the error below as
> "BASIC"
> is converted to "01/IC/BA"
>
> complete IIF expression:
> =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,"OLD","FASHION")))
>
> error generated when DATEDIFF() is introduced:
> Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> textbox
> 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> 'Date' is not valid.
> thanks for you help in advance.
> anthony
>
>|||thanks for the quick reply...cdate is not the issue it's the introduction of
datediff that appears to be called prior to the earlier conditions in the
nested iif being evaluated. as a result, the date expression is evaluated on
data that should have been accounted for before...see the error message
posted "01/IC/BA" will never be recognized as a date no matter what function
i call.
"Ben Watts" wrote:
> instead of using cdate, do you think datevalue may work better?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >i have a nested IIF statement, see below, that evaluates all possible field
> > values of a particular field, and outputs appropriate text. The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"
> >
> > I convert the last possible value type to date by concatenation:
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > The report works fine like this , but once I introduce the DATEDIFF(),
> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> > generating a invalid date function. In other words, instead of the values
> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> > suitable values that can be converted to a date format, they too are
> > subjected to CDATE(). You can clearly see this in the error below as
> > "BASIC"
> > is converted to "01/IC/BA"
> >
> >
> > complete IIF expression:
> >
> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> > < -2,"OLD","FASHION")))
> >
> >
> > error generated when DATEDIFF() is introduced:
> >
> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> > textbox
> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> > 'Date' is not valid.
> >
> > thanks for you help in advance.
> > anthony
> >
> >
> >
> >
>
>|||What are the values of styleseason, that you are trying to convert? So far
I know there is flash and basic, but what are the others?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> thanks for the quick reply...cdate is not the issue it's the introduction
> of
> datediff that appears to be called prior to the earlier conditions in the
> nested iif being evaluated. as a result, the date expression is evaluated
> on
> data that should have been accounted for before...see the error message
> posted "01/IC/BA" will never be recognized as a date no matter what
> function
> i call.
> "Ben Watts" wrote:
>> instead of using cdate, do you think datevalue may work better?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >i have a nested IIF statement, see below, that evaluates all possible
>> >field
>> > values of a particular field, and outputs appropriate text. The
>> > possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"
>> >
>> > I convert the last possible value type to date by concatenation:
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > The report works fine like this , but once I introduce the DATEDIFF(),
>> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> > generating a invalid date function. In other words, instead of the
>> > values
>> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> > only
>> > suitable values that can be converted to a date format, they too are
>> > subjected to CDATE(). You can clearly see this in the error below as
>> > "BASIC"
>> > is converted to "01/IC/BA"
>> >
>> >
>> > complete IIF expression:
>> >
>> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> > < -2,"OLD","FASHION")))
>> >
>> >
>> > error generated when DATEDIFF() is introduced:
>> >
>> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> > textbox
>> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> > type
>> > 'Date' is not valid.
>> >
>> > thanks for you help in advance.
>> > anthony
>> >
>> >
>> >
>> >
>>|||from my original post:
-- The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"--
I am not trying to convert all values only the ones that are not flash or
basic. The other values,which are in yymm format I am doing some string
manipulation and concatenation to get it into a mm/dd/yy format.
"01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
According to my IIF statement I am evaluating conditions for flash and basic
first wich should leave only yymm to convert to mm/dd/yy values. At this
point the report works as it should
BASIC to BASIC
FLASH to OLD
yymm to mm/dd/yy
Once I introduce any date function cdate,dateval or datediff into a sinlge
IIF in the nested IIF statements all styleseason values are evaluated by the
date function and obviously gives an error for the flash and basic values. I
end up with values like
"01/IC/BA" trying to be evaluated, which is BASIC run through the
concatenation. Please see full IIF statement.
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
"Ben Watts" wrote:
> What are the values of styleseason, that you are trying to convert? So far
> I know there is flash and basic, but what are the others?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> > thanks for the quick reply...cdate is not the issue it's the introduction
> > of
> > datediff that appears to be called prior to the earlier conditions in the
> > nested iif being evaluated. as a result, the date expression is evaluated
> > on
> > data that should have been accounted for before...see the error message
> > posted "01/IC/BA" will never be recognized as a date no matter what
> > function
> > i call.
> >
> > "Ben Watts" wrote:
> >
> >> instead of using cdate, do you think datevalue may work better?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >field
> >> > values of a particular field, and outputs appropriate text. The
> >> > possible
> >> > field values for Fields!STYLESEASON, are
> >> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> >> > e.g.
> >> > "0604"
> >> >
> >> > I convert the last possible value type to date by concatenation:
> >> >
> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > The report works fine like this , but once I introduce the DATEDIFF(),
> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> > generating a invalid date function. In other words, instead of the
> >> > values
> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> > only
> >> > suitable values that can be converted to a date format, they too are
> >> > subjected to CDATE(). You can clearly see this in the error below as
> >> > "BASIC"
> >> > is converted to "01/IC/BA"
> >> >
> >> >
> >> > complete IIF expression:
> >> >
> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> > < -2,"OLD","FASHION")))
> >> >
> >> >
> >> > error generated when DATEDIFF() is introduced:
> >> >
> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> > textbox
> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> > type
> >> > 'Date' is not valid.
> >> >
> >> > thanks for you help in advance.
> >> > anthony
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||I see a wayof trying it. You could either write some sort of CASE statment
in your select statement setting the value to a field. Like:
CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
Then write your nested if, something like this.
iif(Fields!Date.Value = 'neither' and
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,
'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
I think you see where I am taking this. Basically the case statment will be
handled first then the rest of it will also be handled in turn. I hope this
helps.
Then enter that instead of your nested if and that should work.
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> from my original post:
> -- The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"--
> I am not trying to convert all values only the ones that are not flash or
> basic. The other values,which are in yymm format I am doing some string
> manipulation and concatenation to get it into a mm/dd/yy format.
> "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> According to my IIF statement I am evaluating conditions for flash and
> basic
> first wich should leave only yymm to convert to mm/dd/yy values. At this
> point the report works as it should
> BASIC to BASIC
> FLASH to OLD
> yymm to mm/dd/yy
> Once I introduce any date function cdate,dateval or datediff into a
> sinlge
> IIF in the nested IIF statements all styleseason values are evaluated by
> the
> date function and obviously gives an error for the flash and basic values.
> I
> end up with values like
> "01/IC/BA" trying to be evaluated, which is BASIC run through the
> concatenation. Please see full IIF statement.
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>
>
>
> "Ben Watts" wrote:
>> What are the values of styleseason, that you are trying to convert? So
>> far
>> I know there is flash and basic, but what are the others?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> > thanks for the quick reply...cdate is not the issue it's the
>> > introduction
>> > of
>> > datediff that appears to be called prior to the earlier conditions in
>> > the
>> > nested iif being evaluated. as a result, the date expression is
>> > evaluated
>> > on
>> > data that should have been accounted for before...see the error message
>> > posted "01/IC/BA" will never be recognized as a date no matter what
>> > function
>> > i call.
>> >
>> > "Ben Watts" wrote:
>> >
>> >> instead of using cdate, do you think datevalue may work better?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >i have a nested IIF statement, see below, that evaluates all possible
>> >> >field
>> >> > values of a particular field, and outputs appropriate text. The
>> >> > possible
>> >> > field values for Fields!STYLESEASON, are
>> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> > format.
>> >> > e.g.
>> >> > "0604"
>> >> >
>> >> > I convert the last possible value type to date by concatenation:
>> >> >
>> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > The report works fine like this , but once I introduce the
>> >> > DATEDIFF(),
>> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> >> > generating a invalid date function. In other words, instead of the
>> >> > values
>> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> >> > only
>> >> > suitable values that can be converted to a date format, they too are
>> >> > subjected to CDATE(). You can clearly see this in the error below
>> >> > as
>> >> > "BASIC"
>> >> > is converted to "01/IC/BA"
>> >> >
>> >> >
>> >> > complete IIF expression:
>> >> >
>> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> > < -2,"OLD","FASHION")))
>> >> >
>> >> >
>> >> > error generated when DATEDIFF() is introduced:
>> >> >
>> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> >> > textbox
>> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> >> > type
>> >> > 'Date' is not valid.
>> >> >
>> >> > thanks for you help in advance.
>> >> > anthony
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||i was actually trying to avoid doing any of the cases in sql, but it looks
like ill have to do it that way. don't you think it is a bug of some sort as
to why calling the function in the iif takes precedence over the previous
conditional statements. in any regards, thank you for your time and help.
"Ben Watts" wrote:
> I see a wayof trying it. You could either write some sort of CASE statment
> in your select statement setting the value to a field. Like:
> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
> Then write your nested if, something like this.
> iif(Fields!Date.Value = 'neither' and
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,
> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
> I think you see where I am taking this. Basically the case statment will be
> handled first then the rest of it will also be handled in turn. I hope this
> helps.
>
> Then enter that instead of your nested if and that should work.
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> > from my original post:
> > -- The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"--
> >
> > I am not trying to convert all values only the ones that are not flash or
> > basic. The other values,which are in yymm format I am doing some string
> > manipulation and concatenation to get it into a mm/dd/yy format.
> >
> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> >
> > According to my IIF statement I am evaluating conditions for flash and
> > basic
> > first wich should leave only yymm to convert to mm/dd/yy values. At this
> > point the report works as it should
> >
> > BASIC to BASIC
> > FLASH to OLD
> > yymm to mm/dd/yy
> >
> > Once I introduce any date function cdate,dateval or datediff into a
> > sinlge
> > IIF in the nested IIF statements all styleseason values are evaluated by
> > the
> > date function and obviously gives an error for the flash and basic values.
> > I
> > end up with values like
> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
> > concatenation. Please see full IIF statement.
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> >
> >
> >
> >
> >
> > "Ben Watts" wrote:
> >
> >> What are the values of styleseason, that you are trying to convert? So
> >> far
> >> I know there is flash and basic, but what are the others?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> >> > thanks for the quick reply...cdate is not the issue it's the
> >> > introduction
> >> > of
> >> > datediff that appears to be called prior to the earlier conditions in
> >> > the
> >> > nested iif being evaluated. as a result, the date expression is
> >> > evaluated
> >> > on
> >> > data that should have been accounted for before...see the error message
> >> > posted "01/IC/BA" will never be recognized as a date no matter what
> >> > function
> >> > i call.
> >> >
> >> > "Ben Watts" wrote:
> >> >
> >> >> instead of using cdate, do you think datevalue may work better?
> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >> >field
> >> >> > values of a particular field, and outputs appropriate text. The
> >> >> > possible
> >> >> > field values for Fields!STYLESEASON, are
> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
> >> >> > format.
> >> >> > e.g.
> >> >> > "0604"
> >> >> >
> >> >> > I convert the last possible value type to date by concatenation:
> >> >> >
> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > The report works fine like this , but once I introduce the
> >> >> > DATEDIFF(),
> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> >> > generating a invalid date function. In other words, instead of the
> >> >> > values
> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> >> > only
> >> >> > suitable values that can be converted to a date format, they too are
> >> >> > subjected to CDATE(). You can clearly see this in the error below
> >> >> > as
> >> >> > "BASIC"
> >> >> > is converted to "01/IC/BA"
> >> >> >
> >> >> >
> >> >> > complete IIF expression:
> >> >> >
> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> > < -2,"OLD","FASHION")))
> >> >> >
> >> >> >
> >> >> > error generated when DATEDIFF() is introduced:
> >> >> >
> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> >> > textbox
> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> >> > type
> >> >> > 'Date' is not valid.
> >> >> >
> >> >> > thanks for you help in advance.
> >> >> > anthony
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||It really does seem like a bug, you could always put that portion of the if
statement first and see if it works that way. I have had weirder things
happen with if statements. Good luck
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:A9BF9E34-15F4-43C5-980E-A6AF59605C16@.microsoft.com...
>i was actually trying to avoid doing any of the cases in sql, but it looks
> like ill have to do it that way. don't you think it is a bug of some sort
> as
> to why calling the function in the iif takes precedence over the previous
> conditional statements. in any regards, thank you for your time and help.
> "Ben Watts" wrote:
>> I see a wayof trying it. You could either write some sort of CASE
>> statment
>> in your select statement setting the value to a field. Like:
>> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
>> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as
>> Date
>> Then write your nested if, something like this.
>> iif(Fields!Date.Value = 'neither' and
>> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> < -2,
>> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
>> I think you see where I am taking this. Basically the case statment will
>> be
>> handled first then the rest of it will also be handled in turn. I hope
>> this
>> helps.
>>
>> Then enter that instead of your nested if and that should work.
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
>> > from my original post:
>> > -- The possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"--
>> >
>> > I am not trying to convert all values only the ones that are not flash
>> > or
>> > basic. The other values,which are in yymm format I am doing some
>> > string
>> > manipulation and concatenation to get it into a mm/dd/yy format.
>> >
>> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
>> >
>> > According to my IIF statement I am evaluating conditions for flash and
>> > basic
>> > first wich should leave only yymm to convert to mm/dd/yy values. At
>> > this
>> > point the report works as it should
>> >
>> > BASIC to BASIC
>> > FLASH to OLD
>> > yymm to mm/dd/yy
>> >
>> > Once I introduce any date function cdate,dateval or datediff into a
>> > sinlge
>> > IIF in the nested IIF statements all styleseason values are evaluated
>> > by
>> > the
>> > date function and obviously gives an error for the flash and basic
>> > values.
>> > I
>> > end up with values like
>> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
>> > concatenation. Please see full IIF statement.
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Ben Watts" wrote:
>> >
>> >> What are the values of styleseason, that you are trying to convert?
>> >> So
>> >> far
>> >> I know there is flash and basic, but what are the others?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> >> > thanks for the quick reply...cdate is not the issue it's the
>> >> > introduction
>> >> > of
>> >> > datediff that appears to be called prior to the earlier conditions
>> >> > in
>> >> > the
>> >> > nested iif being evaluated. as a result, the date expression is
>> >> > evaluated
>> >> > on
>> >> > data that should have been accounted for before...see the error
>> >> > message
>> >> > posted "01/IC/BA" will never be recognized as a date no matter what
>> >> > function
>> >> > i call.
>> >> >
>> >> > "Ben Watts" wrote:
>> >> >
>> >> >> instead of using cdate, do you think datevalue may work better?
>> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >> >i have a nested IIF statement, see below, that evaluates all
>> >> >> >possible
>> >> >> >field
>> >> >> > values of a particular field, and outputs appropriate text. The
>> >> >> > possible
>> >> >> > field values for Fields!STYLESEASON, are
>> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> >> > format.
>> >> >> > e.g.
>> >> >> > "0604"
>> >> >> >
>> >> >> > I convert the last possible value type to date by concatenation:
>> >> >> >
>> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > The report works fine like this , but once I introduce the
>> >> >> > DATEDIFF(),
>> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by
>> >> >> > CDATE()
>> >> >> > generating a invalid date function. In other words, instead of
>> >> >> > the
>> >> >> > values
>> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions
>> >> >> > leaving
>> >> >> > only
>> >> >> > suitable values that can be converted to a date format, they too
>> >> >> > are
>> >> >> > subjected to CDATE(). You can clearly see this in the error
>> >> >> > below
>> >> >> > as
>> >> >> > "BASIC"
>> >> >> > is converted to "01/IC/BA"
>> >> >> >
>> >> >> >
>> >> >> > complete IIF expression:
>> >> >> >
>> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> > < -2,"OLD","FASHION")))
>> >> >> >
>> >> >> >
>> >> >> > error generated when DATEDIFF() is introduced:
>> >> >> >
>> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for
>> >> >> > the
>> >> >> > textbox
>> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA"
>> >> >> > to
>> >> >> > type
>> >> >> > 'Date' is not valid.
>> >> >> >
>> >> >> > thanks for you help in advance.
>> >> >> > anthony
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

DateDiff years as a float

Hello,
I would like to calculate the diferrence between two dates and express the
result as a float of the number of years - such as 3.75 or 5.33333. I am
trying...
CONVERT(float, DATEDIFF(d, MyStartDate, getdate() ) ) /365 as YearsOld
(I realize that dividing by 365 is inaccurate, but it is close enough for my
purposes here)
This line truncates the result to 3.0 or 5.0. What do I need to change in
the syntax?
Thanks in advanceHi Mark,
I get the corretn result when I do this
select CONVERT(float, DATEDIFF(d, convert(datetime,'1 jan 2000'),
getdate() ) ) /365 as YearsOld
but to make sure what you can do is this
select CONVERT(float, DATEDIFF(d, convert(datetime,'1 jan 2000'),
getdate() ) ) / CONVERT(float,365) as YearsOld
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Mark Hoffy" <mark@.here.com> wrote in message
news:0nuIe.290$Zo3.52@.fe03.lga...
> Hello,
> I would like to calculate the diferrence between two dates and express the
> result as a float of the number of years - such as 3.75 or 5.33333. I am
> trying...
> CONVERT(float, DATEDIFF(d, MyStartDate, getdate() ) ) /365 as YearsOld
> (I realize that dividing by 365 is inaccurate, but it is close enough for
> my
> purposes here)
> This line truncates the result to 3.0 or 5.0. What do I need to change in
> the syntax?
> Thanks in advance
>
>

Datediff -Year and Rounding

Hi, I have a statement similar to this...
SELECT
DATEDIFF(year, acc.acct_anuit_birth_dt, GETDATE()) AS 'age'
FROM rpsacct_t acc
The acc.acct_anuit_birth_dt field is a date field. The statement returns
whole numbers based like 64 etc... What I would like it to do is to return
the age to two decimal places eg... if someone is 64 years and 6 months, I'd
like to get back 64.50 Any idea how I might do this ?
ThanksTry this:
SELECT
DATEDIFF(month, acc.acct_anuit_birth_dt, GETDATE())/12. AS age
FROM rpsacct_t acc
If this is not what you want, post more sample data and expected
results.
Razvan

datediff without weekends

How would I use the datediff function without counting wends?
declare @.StartDate SmallDateTime
declare @.EndDate SmallDateTime
set @.StartDate = '10/01/05'
set @.EndDate = getdate()
select datediff(d,@.StartDate,@.EndDate)
--except for the wends
This value returns 18, but I want it to not count wends so it should only
return 13.
--
TIA,
ChrisRChris,
Take a look at:
[url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_threa
d/thread/fc6d9c7aa9b2580d/af9e9a9f851db285?lnk=st&q=Date+Difference+without+wen
ds&rnum=1&hl=en#af9e9a9f851db285[/url]
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:0217993F-F2D8-4B05-ADE9-06DC7D2957F6@.microsoft.com...
> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should
> only
> return 13.
> --
> TIA,
> ChrisR|||> set @.StartDate = '10/01/05'
Egads, this is a very ambiguous date format. I highly recommend using a
more standard format, e.g. YYYYMMDD, that cannot suddenly break if you
change your regional settings or run your code in a database with a
different language or dateformat setting...

> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
Use a calendar table, then you can incorporate holidays and other
non-working days too.
http://www.aspfaq.com/2519|||Look up how to design a Calendar table, which is a more general tool
for all of the temporal queries you will do.|||This link may help with Joe's suggestion.
http://www.aspfaq.com/show.asp?id=2519
HTH
Jerry
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1129763725.019473.177630@.o13g2000cwo.googlegroups.com...
> Look up how to design a Calendar table, which is a more general tool
> for all of the temporal queries you will do.
>|||Hey, Jerry, make the lazy bum work for it !! Google is a good habit
instead of the "do my homework for me" mentality that floods the
Newsgroups.|||Chris,
If you only want to exclude wends, here's one way to achieve this
(assuming Monday as the first day of the w):
SELECT
days/7*5 + days%7
- CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
- CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
FROM (SELECT
DATEDIFF(day, @.StartDate, @.EndDate) + 1 AS days,
DATEPART(wday, @.StartDate + @.@.DATEFIRST - 1) AS wd
) AS D;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw/default.mspx[/url]
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:0217993F-F2D8-4B05-ADE9-06DC7D2957F6@.microsoft.com...
> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should
> only
> return 13.
> --
> TIA,
> ChrisR|||Thanks to all...
--
TIA,
ChrisR
"ChrisR" wrote:

> How would I use the datediff function without counting wends?
> declare @.StartDate SmallDateTime
> declare @.EndDate SmallDateTime
> set @.StartDate = '10/01/05'
> set @.EndDate = getdate()
> select datediff(d,@.StartDate,@.EndDate)
> --except for the wends
>
> This value returns 18, but I want it to not count wends so it should on
ly
> return 13.
> --
> TIA,
> ChrisR

datediff with parameter

Hi
I am trying to run the following query but want to use a parameter instaed
of the -7
BETWEEN GETDATE() AND DATEADD(d, -7, GETDATE())
ive tried BETWEEN GETDATE() AND DATEADD(d, @.date, GETDATE()) with the
parameter set is an int & text but to no avaial.
Thanks in advanceTry BETWEEN GETDATE() AND DATEADD("d",CONVERT(INT,@.date),GETDATE())
I got it to work by converting the param to INT. It appears SSRS
defaults params to the nvarchar datatype.
Hang in there
toolman
Tango wrote:
> Hi
> I am trying to run the following query but want to use a parameter instaed
> of the -7
> BETWEEN GETDATE() AND DATEADD(d, -7, GETDATE())
> ive tried BETWEEN GETDATE() AND DATEADD(d, @.date, GETDATE()) with the
> parameter set is an int & text but to no avaial.
> Thanks in advance

DATEDIFF Weirdness!

Does not compute!!
Can someone explain why DATEDIFF function adds 3 hours to the result?!
I constantly had false results and I then noticed that the time values
are incorrect. Then I made this query to check it:
select DATEDIFF(second, 'jan 1 1970', '2003-08-14')
It returns: 1060819200
Then I made a nice perl script to prove my point:
#!c:\perl\bin\perl.exe -w
print "TIME IS: " . localtime(1060819200);
Result is: Thu Aug 14 03:00:00 2003 (!?)
My Server and Client-machines are both set to same time zone, same date,
same time... So where does this extra 3 hours come from? Does it have
sth to do with UTC-time as GETUTCDATE() returns a time that is 3 hours
behind the time of my machines (Finnish time...).
But anyway, how can I get the DATEDIFF-function to work properly. I
really don't need the extra 3 hours... ;)
Oh, BTW... it's SQL Server 2000 I'm talking about and Windows 2000
Server (SQL Server) & Professional (SQL Server client).
-N-The problem is not with SQL Server it is with the way you are calling
localtime.
This function converts the value returned by time to a nine-element list
with the time corrected for the local time zone.
http://www.ib-perl.org/class/localtime.html
"Niko" <niko.ratto@.noSPAMkia.fi> wrote in message
news:TcN_a.9951$g4.193781@.news1.nokia.com...
> Does not compute!!
> Can someone explain why DATEDIFF function adds 3 hours to the result?!
> I constantly had false results and I then noticed that the time values
> are incorrect. Then I made this query to check it:
> select DATEDIFF(second, 'jan 1 1970', '2003-08-14')
> It returns: 1060819200
> Then I made a nice perl script to prove my point:
> #!c:\perl\bin\perl.exe -w
> print "TIME IS: " . localtime(1060819200);
> Result is: Thu Aug 14 03:00:00 2003 (!?)
>
> My Server and Client-machines are both set to same time zone, same date,
> same time... So where does this extra 3 hours come from? Does it have
> sth to do with UTC-time as GETUTCDATE() returns a time that is 3 hours
> behind the time of my machines (Finnish time...).
> But anyway, how can I get the DATEDIFF-function to work properly. I
> really don't need the extra 3 hours... ;)
> Oh, BTW... it's SQL Server 2000 I'm talking about and Windows 2000
> Server (SQL Server) & Professional (SQL Server client).
> -N-
>

DateDiff Trivial Problem

Hy Friends,
I'm having a new annoying problem wiht T-SQL.
I need to remove 10 Seconds from a Datetime Value.
Somethin like This:
This date
07/24/2003 14:25:02
I want it like this
07/24/2003 14:24:52
Suggestions?select DATEADD(ss, -10, date_column) from table

DateDiff That Only Counts Working Days

Hi! I'm trying to create a query to calculate the number of days between two dates, but I only want to include working days. Is there a way to do this?Most DBAs work seven days a week, weekends and holidays included ;). The provided DateDiff works fine for us!

On a (very slightly) more serious note, what constitutes a "working day" by your definition?

-PatP|||LOL, I definitely realize what days DBAs work... :)

Working days are just the typical Monday through Friday.|||Vacations? Holidays? Snow days? Office closed due to threat of terrorist attack? Those all affect "working days". Or did you mean to ask "I'm trying to create a query to calculate the number of weekdays between two dates, excluding weekends."?|||Would you include Holidays as work days?

IF not I would suggest you have a table calendar with field workday
values 1 , 0

sum the workday field to get your answer.|||Yes, this is what I'm trying to say:

"I'm trying to create a query to calculate the number of weekdays between
two dates, excluding weekends."|||http://www.aspfaq.com/show.asp?id=2453

datediff subtracting time help needed

I have a report which I am trying to get the difference of time between 2 dates. The problem is I have a date field and a separate time field for the appointment. I have a 3rd field which has both the date and time in it for the completeion time (datetimesent) I am tring to subrtact the time differences from the appointment time and time sent to get the hours it took to complete. can some one help me out.

=Datediff(Fields!APPOINTMENT_DATE.Value+''+Fields!APPOINTMENT_TIME.Value - Fields!DateTimeSent.Value)%1440

Hi CPowers,

try the following

=Datediff( CDATE(Fields!APPOINTMENT_DATE.Value + ' ' + Fields!APPOINTMENT_TIME.Value) - CDATE(Fields!DateTimeSent.Value) ) % 1440

all the best

|||

I tried what you sent me but I get and error

Operator '-' is not defined for types "Objects" and "Date" I also tried the second post and get the same results.

|||

I tried what you sent me but got the error below do you have any suggestions

Operator '-' is not defined for types "Object" and "Date"

|||

=Datediff("d", CDATE(Fields!APPOINTMENT_DATE.Value + ' ' + Fields!APPOINTMENT_TIME.Value) , CDATE(Fields!DateTimeSent.Value) )

this will give the no of days between the specified dates.

refer this

http://msconline.maconstate.edu/tutorials/VBNET/VBNET01/vbnet01-07.aspx

|||

Please refer this URL http://msdn2.microsoft.com/en-us/library/aa337153.aspx. You could make use of both the DateAdd and DateDiff functions.

DateDiff rewrite

Given this query, how can I rewrite it to make it more indexable:
SELECT tableUID FROM MyTable
WHERE DateDiff(D, mydate, GETDAT()) BETWEEN 0 AND 29
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200608/1Try this
SELECT tableUID FROM MyTable
WHERE mydate BETWEEN DATEADD(d,-29,GETDATE()) and GETDATE()
Denis the SQL Menace
http://sqlservercode.blogspot.com/
cbrichards via droptable.com wrote:
> Given this query, how can I rewrite it to make it more indexable:
> SELECT tableUID FROM MyTable
> WHERE DateDiff(D, mydate, GETDAT()) BETWEEN 0 AND 29
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200608/1

DateDiff rewrite

Given this query, how can I rewrite it to make it more indexable:
SELECT tableUID FROM MyTable
WHERE DateDiff(D, mydate, GETDAT()) BETWEEN 0 AND 29
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1Try this
SELECT tableUID FROM MyTable
WHERE mydate BETWEEN DATEADD(d,-29,GETDATE()) and GETDATE()
Denis the SQL Menace
http://sqlservercode.blogspot.com/
cbrichards via SQLMonster.com wrote:
> Given this query, how can I rewrite it to make it more indexable:
> SELECT tableUID FROM MyTable
> WHERE DateDiff(D, mydate, GETDAT()) BETWEEN 0 AND 29
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200608/1

DateDiff returns odd result

I have a table of addresses for residents in a housing authority, and these
addresses may change so I need to find the first move in date and subtract it
from today's date to get the length of residency. Here is my query:
select distinct a1.HouseholdNbr,
(SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
a1.householdNbr = a2.householdNbr) as MoveInDate,
DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
from tblResidentAddresses a1
where a1.householdnbr < 99000
order by HouseholdNbr
The length of years residency is correct except for years that are 2004 or
2005. Here is a sample result:
MoveinDate Residency
01/13/20014
08/08/200412
02/21/19978
02/19/198817
07/12/200410
01/17/19978
06/22/199411
06/27/19978
05/24/20046
01/20/20014
07/12/20046
06/01/200424
02/20/200413
01/01/200512
11/18/19978
04/19/20014
08/25/19996
03/22/200429
12/01/199411
Any suggestions why 2004 and 2005 produce wrong results? Thanks
Hi
Posting ddl and example data would help see
http://www.aspfaq.com/etiquette.asp?id=5006. I believe the problem is related
to your function dbo.dateString, try adding it to the datediff MIN()
statement as using datetimes is fine:
CREATE TABLE tblResidentAddresses ( householdnbr int not null
identity(1,1), MoveinDate datetime not null)
INSERT INTO tblResidentAddresses ( MoveinDate )
SELECT '20010113'
UNION ALL SELECT '20040808'
UNION ALL SELECT '19970221'
UNION ALL SELECT '19880219'
UNION ALL SELECT '20040712'
UNION ALL SELECT '19970117'
UNION ALL SELECT '19940622'
UNION ALL SELECT '19970527'
UNION ALL SELECT '20040524'
UNION ALL SELECT '20010120'
UNION ALL SELECT '20040712'
UNION ALL SELECT '20040601'
UNION ALL SELECT '20040220'
UNION ALL SELECT '20050101'
UNION ALL SELECT '19971118'
UNION ALL SELECT '20010419'
UNION ALL SELECT '19990825'
UNION ALL SELECT '20040322'
UNION ALL SELECT '19941201'
SELECT DISTINCT a1.householdnbr,
CONVERT(char(10),(SELECT MIN(MoveInDate) FROM tblResidentAddresses a2 WHERE
a1.householdnbr = a2.householdnbr),110) AS MoveInDate,
DATEDIFF(yy,(SELECT MIN(MoveInDate) FROM tblResidentAddresses a3 WHERE
a1.householdnbr = a3.householdnbr), GETDATE()) AS Residency
FROM tblResidentAddresses a1
WHERE a1.householdnbr < 99000
ORDER BY a1.householdnbr
Seems fine:
householdnbr MoveInDate Residency
-- -- --
1 01-13-2001 4
2 08-08-2004 1
3 02-21-1997 8
4 02-19-1988 17
5 07-12-2004 1
6 01-17-1997 8
7 06-22-1994 11
8 05-27-1997 8
9 05-24-2004 1
10 01-20-2001 4
11 07-12-2004 1
12 06-01-2004 1
13 02-20-2004 1
14 01-01-2005 0
15 11-18-1997 8
16 04-19-2001 4
17 08-25-1999 6
18 03-22-2004 1
19 12-01-1994 11
(19 row(s) affected)
John
"DLS" wrote:

> I have a table of addresses for residents in a housing authority, and these
> addresses may change so I need to find the first move in date and subtract it
> from today's date to get the length of residency. Here is my query:
> select distinct a1.HouseholdNbr,
> (SELECT MIN(dbo.dateString(MoveInDate)) from tblResidentAddresses a2 where
> a1.householdNbr = a2.householdNbr) as MoveInDate,
> DateDiff(yy,(SELECT MIN(MoveInDate) from tblResidentAddresses a3 where
> a1.householdNbr = a3.householdNbr), GetDAte()) as Residency
> from tblResidentAddresses a1
> where a1.householdnbr < 99000
> order by HouseholdNbr
> The length of years residency is correct except for years that are 2004 or
> 2005. Here is a sample result:
> MoveinDate Residency
> 01/13/20014
> 08/08/200412
> 02/21/19978
> 02/19/198817
> 07/12/200410
> 01/17/19978
> 06/22/199411
> 06/27/19978
> 05/24/20046
> 01/20/20014
> 07/12/20046
> 06/01/200424
> 02/20/200413
> 01/01/200512
> 11/18/19978
> 04/19/20014
> 08/25/19996
> 03/22/200429
> 12/01/199411
> Any suggestions why 2004 and 2005 produce wrong results? Thanks