Comparing Financial values across random dates

I submitted this issue to the reporting services thread, but I'm beginning to think I may have to do something in SSAS:

I want to enable my users to select 2 random month end dates in a report parameter. After selecting these dates, I want to be able to take the corresponding financial values, be able to line them up next to each other in a report, and calculate the difference. Obviously in a matrix I can line the values up, but I am then not able to calc a difference. I do have a time hierarchy that allows me to do this across dates at the same level of granularity, i.e. year to year, qtr to qtr, etc., but if my users want to compare February month end to November month end, it's a problem. I am more than willing to add calculated members to the cube to get this done, I just haven't figured out the MDX for it.

Thanks in Advance...

I think this can be done without any calculated members, simply by using the following query:

SELECT

{Measures.ImportantFinancialValue1, Measures.ImportantFinancialValue2, ...} ON COLUMNS

, {Time.Date1, Time.Date2} ON ROWS

FROM Cube

HTH,

Mosha (http://www.mosha.com/msolap)

|||

Mosha-

Thanks for your quick response. I am using SSAS on top of SSRS, and am trying to avoid writing MDX as these reports may get very involved. Can I replicate this in a calc'd member, can it be dynamic enough to accept values at runtime?

|||I am supposed to find out the total number of open account between February - April and i am having difficulties. Below is a working statement for SQL which i am supposed to do in MDX for my Cube:

SELECT COUNT(*) AS Expr1

FROM ConsumerAccount

WHERE (DATEPART(yyyy, AccountOpenedDate) = '2007')

AND (DATEPART(mm, AccountOpenedDate) IN (02, 03, 04))

I have a cube called Batch process, column AccountOpenedDate and CrearedOnDate, how do i do it

comparing datime(SQL ) Year, Month, Day,Time

hi

i want to comparetow dates in my procedure, comparing all(year, month, day, time).

can anyone help me. thanhs.

Take a look following link

http://www.databasejournal.com/features/mssql/article.php/2209321

Hope this help!!!

comparing DateTime in UK Format

Hello friends,

I am trying to return all records between 2 dates. The Date columns are in DateTime format, and i am ignoring the timestamp. The user should be able to input UK Date Format (dd/mm/yyyy) and return the rows. This sql code works fine for American date format, but i get an error: converting from varchar to datetime when i put in a UK format. eg. 22/11/06. Please advise on this problem! many thanks!

ALTER PROCEDURE SalaryBetweenDates
(

@.WeekStart datetime,

@.WeekEnd datetime
)
AS

BEGIN
SET @.WeekStart = (SELECT REPLACE(CONVERT(DATETIME,@.WeekStart ,103),' ','-'))
SET @.WeekEnd = (SELECT REPLACE(CONVERT(DATETIME,@.WeekEnd ,103),' ','-'))
END

BEGIN
SELECT s.StaffNo,s.StaffName,s.StaffAddress, s.HourlyRate,
sh.HoursWorked, CONVERT(varchar(12), sh.WeekStart, 103) AS StartDate, CONVERT(varchar(12), sh.WeekEnd, 103)As EndDate,(sh.HoursWorked * s.HourlyRate)"Salary"
From Staff As S INNER JOIN StaffHours As Sh
On S.StaffNo = Sh.StaffNo
WHERE sh.WeekStart >= (@.WeekStart)
AND sh.WeekEnd <= (@.WeekEnd)

FOR XML RAW ('paySlip'), root('Staff'), ELEMENTS XSINIL
END

ReturnYou need to convert the UK format date into a format that Sql can read.
I always use the following ones
'YYYY-MM-DD' for date
'YYYY-MM-DD HH:NN:SS' for date & time
use exactly as is... don't change the sperators

so '22/11/06' should be passed to sqlserver as '2006-11-22'|||

If you want to be able to call the procedure like this

EXEC SalaryBetweenDates '22/11/06', '1/12/06'

you're going to have to make the procedure parameters varchars and write some string handling code to figure out the strings that are passed in. I'd recommend that you leave it as it is and have the application pass dates in the format that SQL Server expects, if necessary have the application do the work at figuring out what date the user actually entered.

|||

thanks for your help guys. I set the parameters as strings in the end, and used REPLACE(CONVERT) to handle the function

:-)

Comparing Dates.

Hi All,

I have a database field (datestamp) which returns the date the records were inserted into the database. The datestamp was created with the now(); function in .net and is in the following format:5/23/2006 2:27:45 AM

I basically want to return all records that were inputted more than 28 days ago. I have had alook though some other posts and below is the closest query that i could find but unfortunately it does not work for me.

SELECT id, datestamp
FROM table
WHERE datestamp > DateAdd(d, 28, GetDate())

Thanks in advance,

Jake

You are looking for a date 28 days ago, so try -28:

SELECT id, datestamp
FROM table
WHERE datestamp > DateAdd(d,-28, GetDate())

|||

Spot on Douglas, thankyou for you quick reply!

Jake

comparing dates(Minutes, Hours etc)

guys - is this a decent query to pull all columns (dateCreate)
that have a timestamp less than five minutes?
i know its simple, but i've never done a date compare with minutes or hours
in sql server
thanks
rik:o

select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,getdate()) <=5

select top 10 * from ptpuritm
where datediff(MINUTE,dateCreate,current_timestamp) <=5Type CTRL+K and look at the execution plans for both of the following examples

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99 (dateCreate datetime)
GO

CREATE INDEX myIndex99 ON myTable99(dateCreate)
GO

INSERT INTO myTable99(dateCreate)
SELECT '12/31/1999 23:00:00' UNION ALL
SELECT '12/31/1999 23:10:00' UNION ALL
SELECT '12/31/1999 23:20:00' UNION ALL
SELECT '12/31/1999 23:30:00' UNION ALL
SELECT '12/31/1999 23:40:00' UNION ALL
SELECT '12/31/1999 23:50:00' UNION ALL
SELECT '12/31/1999 23:55:00' UNION ALL
SELECT '12/31/1999 23:56:00' UNION ALL
SELECT '12/31/1999 23:57:00' UNION ALL
SELECT '12/31/1999 23:58:00' UNION ALL
SELECT '12/31/1999 23:59:00' UNION ALL
SELECT '12/31/1999 23:59:59'
GO


SELECT *
FROM myTable99
WHERE datediff(MINUTE,dateCreate,'1/1/2000 00:00:00') <=5

SELECT *
FROM myTable99
WHERE dateCreate <= dateadd(MINUTE,-5,'1/1/2000 00:00:00')

GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Brett, thanks so much for the help on this. Coming from an Oracle background, i can tell you i'm growing to appreciate SQL SERVER each day.
thanks
again|||You like that?

Look here

http://www.sqlteam.com/

Comparing dates with today dates

I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.
The statement I used is:
Select Date from Table where Date > 'Today.now'
I get an error
Could this be done or is there another approach?Select Date from Table where Date > getdate()
If you just want to match dates and not times:
Select Date from Table where convert(varchar(10), Date, 101) > convert(varchar(10), getDate(), 101)

Nick

Comparing Dates in Subqueries

Hi,

I am trying to write a basic select query with a subquery.

select x
from y
where DateTime > '27 January 2003'
and DateTime < '02 February 2003'
and a = 'ttt'
and x IN (SELECT x
from y
WHERE a = 'vvv'
and rcd.DateTime > '27 January 2003'
and rcd.DateTime < '02 February 2003')

I only want to see the data for the records where ttt happened before vvv.

Any ideas?select x
from y
where DateTime > '27 January 2003'
and DateTime < '02 February 2003'
and a = 'ttt'
and exists
(SELECT *
from y y2
WHERE y2.a = 'vvv'
and y2.DateTime > '27 January 2003'
and y2.DateTime < '02 February 2003'
and y2,DateTime > y.DateTime
)|||select y.x
from y
left join
(
select x,MinDateTime=min(DateTime)
from y
where a='vvv' and DateTime > '27 January 2003' and DateTime < '02 February 2003'
group by x
) sy on sy.x=y.x and a='ttt' and ( (DateTime<MinDateTime) or MinDateTime is null )sqlsql

Comparing Dates in SQL

I am wondering how I would create a SELECT that will select the most recent date from one of two tables. For example, table1 has a field called LastUpdate and table 2 has a field called LastUpdate. I need to grab only the most recent date. I tried this using an inner join...but that didn't work because it only picks the lastupdate form one table only. talbe1 and table2 are tied by table2.table1id.

Can anyone help?

you could do a MAX(lastupdatedate) and then do the INNER JOIN.
SELECT
table1.column
FROM
table1
INNER JOIN table2
on table1.table1id = able2.table1id
WHERE
MAX(table1.lastupdatedate) = MAX(table2.lastupdatedate)|||I'm sorry I don't understand how this works. If the last update date is today in table2, I want to return that date. Otherwise I want to return the last update date in table1. Does this mak sense?|||SELECTCASE WHEN (table1.LastUpdate > table2.LastUpdate) THEN table1.LastUpdate ELSE table2.LastUpdate END AS MaxLastUpdate
FROM ... usual join statement|||

This is almost perfect! Thanks!

Followup: what if there is no instance of table2.table1id? Then I get nothing returned but in reality I still want table1.lastupdate returned.

Thanks again!

|||Ok I'm an idiot. I just switched the when statement and the then and else so the else displays the main from table1 and walaa. Thank you so much for this. I will get better at these case statements yet!|||

You have to use OUTER JOIN to make sure all rows are included. For example (using my own dummy master/detail tables):

SELECT A.ID_MASTER, CASE WHEN (A.DateEntered > ISNULL(B.DateEntered, '01-01-1900'))
THEN A.DateEntered ELSE b.DateEntered END AS MaxLastUpdate
FROM TestDate AS A LEFT OUTER JOIN
TestDate2 AS B ON A.ID_MASTER = B.ID_MASTER

ISNULL() function is used to make sure NULL date value (for the missing row in Detail) defaults to "01-01-1900", assuming that date will be small enough. Also, this query will return multiple rows if there are more than one Detail rows for a given Master row. Depending on your situation, you may or may not have to change this.

|||

SELECT keyid,MAX(DateEntered)
FROM (
SELECT keyid,DateEntered FROM table1
UNION
SELECT keyid,DateEntered FROM table2
) z
GROUP BY keyid

Would work as well, and only return a single result for each keyid.

Comparing Dates in Previous and Current Rows

Dear All,
I am having textboxes on two detail rows (as an example), I want to calculate the date difference between two textbox date values. The date difference is between the previous row and the current row. As an example:

A B

5/2/2005 09:39:02 AM	5/3/2005 06:29:32 PM
5/4/2005 08:21:31 AM	5/5/2005 07:02:29 PM
5/6/2005 09:52:33 AM	5/6/2005 07:04:31 PM
5/7/2005 09:20:33 AM	5/8/2005 08:26:36 AM

I want to do A2-B1 in the report. I need to calculate the date difference between 5/4/2005 8:21:31 AM and 5/3/2005 6:29:32 PM as an example. The same thing for A3 and B2. ...
Do you have any idea how I can achieve this? Is there a way to do that? Do I need some special code to accomplish this?
Thank you for your help.
BTHi,
a way is to create a store proc that make the calculation
(fetching results in a temp table).|||
yes you can
you can use formula like this
=ReportItems!A.Value.Subtract(ReportItems!B.Value)
where A and B are the name of the cells in your table
noteSubtractfunction in datetime returns timespan|||

Hi,
IMHO the formula
ReportItems!A.Value.Subtract(ReportItems!B.Value)
calculate difference between value of the same row.
I've understood that Salmiya have to calculate between a value of column A with
value of column B, but of previous row.

|||Yes you are right. I have to compare previous row with current one.
Thank you.|||tblDates :
PK A B
----------------

1 5/2/2005 09:39:02 AM	5/3/2005 06:29:32 PM
2 5/4/2005 08:21:31 AM	5/5/2005 07:02:29 PM
3 5/6/2005 09:52:33 AM	5/6/2005 07:04:31 PM
4 5/7/2005 09:20:33 AM	5/8/2005 08:26:36 AM

You can do :
Select [Difference] = A-IsNull((Select B from tblDates where PK=2 ),0) from tblDates where PK=3
HTH,
Best Regards,
Hemchand|||Hi.....
U can use =ReportItems!textbox1.Value - ReportItems!textbox2.Value
Then U will get it..........
Best Regards....

Comparing dates in one field

I have a table with an ID, Date and Comments and need to compare the ID and
the date and retrieve the latest comment for a filing based on the date. So
you could have many records with the same ID that refer to the same filing,
only with a number of different dates. I've tried various things, but do no
t
get the right result.
example:
ID Date Comment
01 6/1/2006 This is the first comment
01 6/2/2006 This is the second comment *
02 6/2/2006 This is a different comment
02 6/5/2006 This is a new comment for this filing *
What I need is to get the second row with the 01 and the last row with 02 ID
in this example indicated by *.
I'm using SQL Server 2000. Thanks for your help in advance.CREATE TABLE #Temp ([ID] VARCHAR(2),
[Date] DATETIME,
[Comment] VARCHAR(2000),
PRIMARY KEY ([ID], [Date]))
INSERT INTO #Temp ([ID], [Date], [Comment])
SELECT '01', '2006-06-01', 'This is the first comment'
UNION SELECT '01', '2006-06-02', 'This is the second comment*'
UNION SELECT '02', '2006-06-02', 'This is a different comment'
UNION SELECT '02', '2006-06-05', 'This is a new comment for this filing'
UNION SELECT '02', '2006-06-06', 'This is a newer comment'
UNION SELECT '03', '2006-06-01', 'New ID, New comment*'
UNION SELECT '02', '2006-07-01', 'The newest comment*'
SELECT t1.[ID], t1.[Date], t1.[Comment]
FROM #Temp t1
WHERE t1.[Date] =
(
SELECT MAX([Date])
FROM #Temp t2
WHERE t2.[ID] = t1.[ID]
)
GROUP BY t1.[ID], t1.[Date], t1.[Comment]
DROP TABLE #Temp
"SK" <SK@.discussions.microsoft.com> wrote in message
news:3B67DA49-A987-44A9-B403-8CA85D581817@.microsoft.com...
>I have a table with an ID, Date and Comments and need to compare the ID and
> the date and retrieve the latest comment for a filing based on the date.
> So
> you could have many records with the same ID that refer to the same
> filing,
> only with a number of different dates. I've tried various things, but do
> not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
> ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Try this..
SELECT ID,Date,Comment FROM
YourTable WHERE Date =
(SELECT MAX(Date) FROM YourTable yt1 WHERE YourTable.Id = yt1.Id)
- Sha Anand
"SK" wrote:

> I have a table with an ID, Date and Comments and need to compare the ID an
d
> the date and retrieve the latest comment for a filing based on the date.
So
> you could have many records with the same ID that refer to the same filing
,
> only with a number of different dates. I've tried various things, but do
not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Hi there
One query that you can try is:
SELECT A.ID, A.Date, A.Comment
FROM dbo.[Comments] A
INNER JOIN (SELECT ID, MAX(Date) FROM dbo.[Comments] GROUP BY ID) B
ON A.ID = B.ID
Lucas
"SK" wrote:

> I have a table with an ID, Date and Comments and need to compare the ID an
d
> the date and retrieve the latest comment for a filing based on the date.
So
> you could have many records with the same ID that refer to the same filing
,
> only with a number of different dates. I've tried various things, but do
not
> get the right result.
> example:
> ID Date Comment
> 01 6/1/2006 This is the first comment
> 01 6/2/2006 This is the second comment *
> 02 6/2/2006 This is a different comment
> 02 6/5/2006 This is a new comment for this filing *
> What I need is to get the second row with the 01 and the last row with 02
ID
> in this example indicated by *.
> I'm using SQL Server 2000. Thanks for your help in advance.|||Thank you very much for your quick response! It seems to work perfectly!
I had the second Where clause in the wrong place!
"Sha Anand" wrote:
> Try this..
> SELECT ID,Date,Comment FROM
> YourTable WHERE Date =
> (SELECT MAX(Date) FROM YourTable yt1 WHERE YourTable.Id = yt1.Id)
> - Sha Anand
>
> "SK" wrote:
>|||Wow! This was quite fast and thorough!
I've never tried it this way before with Union Select. But it works
beautifully.
Thank you Mike for taking the time to go to such length! I Appreciate it!
Have a lovely day!
SK
"Mike C#" wrote:

> CREATE TABLE #Temp ([ID] VARCHAR(2),
> [Date] DATETIME,
> [Comment] VARCHAR(2000),
> PRIMARY KEY ([ID], [Date]))
> INSERT INTO #Temp ([ID], [Date], [Comment])
> SELECT '01', '2006-06-01', 'This is the first comment'
> UNION SELECT '01', '2006-06-02', 'This is the second comment*'
> UNION SELECT '02', '2006-06-02', 'This is a different comment'
> UNION SELECT '02', '2006-06-05', 'This is a new comment for this filing'
> UNION SELECT '02', '2006-06-06', 'This is a newer comment'
> UNION SELECT '03', '2006-06-01', 'New ID, New comment*'
> UNION SELECT '02', '2006-07-01', 'The newest comment*'
> SELECT t1.[ID], t1.[Date], t1.[Comment]
> FROM #Temp t1
> WHERE t1.[Date] =
> (
> SELECT MAX([Date])
> FROM #Temp t2
> WHERE t2.[ID] = t1.[ID]
> )
> GROUP BY t1.[ID], t1.[Date], t1.[Comment]
> DROP TABLE #Temp
> "SK" <SK@.discussions.microsoft.com> wrote in message
> news:3B67DA49-A987-44A9-B403-8CA85D581817@.microsoft.com...
>
>

Comparing dates in Case statement

hi

I am having problem in converting dates either to varchar or integer

I need to compare (just month and date part , not the year)

If

mm/dd (of current date) i.e 07/12 > 06/30

i.e if today's month and date is greater than june 30th then perform task A

if today's month and date is less than july 1st perform task B

i.e

07/12 < 07/01

please help

Thanks

Code Snippet

select case when (month(getdate())* 100 + day(getdate())) > 0630

then ... -- task a

else ... --task b

end

from ....

|||

Thank you very much , that helped

But I have a new problem

I am trying to execute task A

as below

SELECT
case
when (month(getdate())* 100 + day(getdate())) > 0630
then
(select * from dbo.V_FUN_SCOPES_LEVELS0506)
end

It gives error

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

|||

You need to use IF syntax instead of CASE syntax; give a look to the related article in books online. That should look something like:

Code Snippet

if (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506

Also, beware of using "SELECT *" syntax from within a stored procedure or function.

|||

yea, it won't work in that manner.

Can you elaborate a little more on what you're trying to do and how it is going to be used.

From this is looks like you'll need a stored procedure or maybe a function.

|||

I think in you're probably just looking at some standard IF...ELSE control of flow.

IF (SELECT (month(getdate())* 100 + day(getdate()))) > 0630

PRINT 'do this'

ELSE

PRINT 'do that'

|||

Hi

the IF syntax works independently but If i try to create a store proc as below it gives error

CREATE PROCEDURE [dbo].[P_Latest]

AS

IF (month(getdate())* 100 + day(getdate())) > 0630
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506

END

ELSE
IF (month(getdate())* 100 + day(getdate())) < 0701
BEGIN
select * from dbo.V_FUN_SCOPES_LEVELS0506
END

G0

I need to display select * from PROCEDURE [dbo].[P_Latest]

i.e Exec PROCEDURE [dbo].[P_Latest] from SQL reporting services

P.S. Here the view dbo.V_FUN_SCOPES_LEVELS0506 is as below

I need the subquery for Display purpose

(select
a.student_id,
a.at_sss_read_score as Score_06,
b.at_sss_read_score as Score_07,
a.at_test_month + '/' + '20' + a.fcat_test_year as Date_06,
b.at_test_month + '/' + '20' + b.fcat_test_year as Date_07,
a.at_test_month as Month_06,
b.at_test_month as Month_07,
a.at_test_year as Year_06,
b.at_test_year as Year_07,
a.at_sss_read_level as Level_06,
b.at_sss_read_level as Level_07

from
(
(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate())-1 , 2)) a
--AND STUDENT_ID IN ('0011307')) a

full join

(select student_id, at_sss_read_score ,at_test_month,at_test_year,at_sss_read_level
from
DW_STUDENT.DBO.AT_TEST
where AT_TEST_YEAR = right (year (getdate()),2 )) b
--AND STUDENT_ID IN ('0011307')) b

on a.student_id = b.student_id
)

where
a.at_test_year is not null
and b.at_test_year is not null

)

|||

This might work:

Code Snippet

CREATE PROCEDURE [dbo].[P_Latest]
AS

IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506

G0

But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.

I feel like I am going wrong with this. Anyone? Help?

|||

In your Reporting Services dataset, just set the command type to StoredProcedure and the QueryString to [dbo].[P_Latest].

That is the equivalent of select * from ...

|||

I chose to believe that that was just test code

|||

Kent Waldrop Jl07 wrote:

This might work:

Code Snippet

CREATE PROCEDURE [dbo].[P_Latest]
AS

IF (month(getdate())* 100 + day(getdate())) > 0630
select * from dbo.V_FUN_SCOPES_LEVELS0506
ELSE
select * from dbo.V_FUN_SCOPES_LEVELS0506

G0

But I feel like I am missing something major. I see no point to the IF statement nor the ELSE Statement. Also, the SELECT * from inside a procedure is a bad idea because the MEANING of the SELECT * statement is determined at compile time and not at run time. You should explicitly list the columns that you return.

I feel like I am going wrong with this. Anyone? Help?

I've always heard this... "...SELECT * from inside a procedure is a bad idea.." without really understanding why. I apologize if i'm deviating from the point but...

when you say compile time and not run time, do you mean if a store procedure is compiled with select * then subsequently, the table changes (ie. column added) the sp will return everything without the extra column?

|||Yes, that is exactly what I mean; it is for that reason that I will sometimes say that use of "SELECT *" leaves "land mines" that blow up sometime later.

Comparing Dates

Does anyone know of a quick and easy to compare 2 datetime values based only on the month and year.

For example,

FromDate = 11/30/2004
ToDate = 12/30/2004

I just need to compare the 11/2004 to 12/2004 using function like <=, =, >=, etc.

Any suggestions?

ThanksI'd use:IF Convert(CHAR(7), FromDate, 121) = Convert(CHAR(7), ToDate, 121)-PatP|||...or:

CONVERT(CHAR(7), [YourDate], 120) + '/01'

...will implicitly convert your date value to the first day of the month.|||...or:

CONVERT(CHAR(7), [YourDate], 120) + '/01'

...will implicitly convert your date value to the first day of the month.My machine doesn't like the mixed separators... It does Ok with:CONVERT(CHAR(7), [YourDate], 120) + '-01'-PatP|||My bad. I meant "-01"...|||I'd create a computed column. This way you can index it and avoid table/clustered index scan overhead.sqlsql

Comparing dates

HI,

In my report there are two parameters,One is for StartDate and One is for enddate.Startdate must lessthan enddate.How to compare two dates in sqlserver report,and if send date is earlier than startdate how to display error message.

Thanks in advance

You can use the Parameters collection to reference the value of a parameter - Parameters!StartDate.Value. So you can compare the two dates like =IIF(Parameters!StartDate.Value > Parameters!EndDate.Value, "Error", "OK").|||

Hi,

where we have to write that iif condition.Please help me.

|||You can use the expression as the value of a textbox where you want to display the comparison result.

comparing dates

Hi all,
I'm trying to compare two dates, from two tables. Both are of datetime
datatype.
I dont want the time portion to be involved in the date, as I only need to
compare days. Having the datetime in the comparison yeilds unexpected
results
Thanks
RobertRobert Bravery wrote:
> Hi all,
> I'm trying to compare two dates, from two tables.
In a join statement?

> Both are of datetime
> datatype.
> I dont want the time portion to be involved in the date, as I only
> need to compare days. Having the datetime in the comparison yeilds
> unexpected results
>
You could simply use CONVERT on both datetime columns:
... CONVERT(char(8),table1.datecol,112) =
CONVERT(char(8),table2.datecol,112)
but that would prevent an existing index from being used on both tables
resulting in poor performance. An alternative that may perform better,
especially if an index exists for the datetime coumn in table 1, would be
this:
... table1.datecol >=dateadd(d,datediff(d,0,table2.datecol),0) and
table1.datecol < dateadd(d,1 + datediff(d,0,table2.datecol),0)
HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Convert the dates to a ISO date, thats my best practise for this.
CONVERT(VARCHAR(10),GETDATE(),112)
HTH, jens Suessmeyer.|||Compare after taking off the time part of DATETIME field...
SELECT getdate()
SELECT CAST(FLOOR(CAST( getdate() AS float)) AS DATETIME)
Thanks,
Sree
"Jens" wrote:

> Convert the dates to a ISO date, thats my best practise for this.
> CONVERT(VARCHAR(10),GETDATE(),112)
> HTH, jens Suessmeyer.
>|||Hi Bob,
Thanks for the response.
Could I well use, youre response gave me an idea, if
convert(int,@.dol,112)>=convert(int,@.sdate+1,112)
@.dol and @.sdate are both datetime datatypes
Would that comparison work
Thanks
Robert
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:OCeiNByKGHA.536@.TK2MSFTNGP09.phx.gbl...
> Robert Bravery wrote:
> In a join statement?
>
> You could simply use CONVERT on both datetime columns:
> ... CONVERT(char(8),table1.datecol,112) =
> CONVERT(char(8),table2.datecol,112)
> but that would prevent an existing index from being used on both tables
> resulting in poor performance. An alternative that may perform better,
> especially if an index exists for the datetime coumn in table 1, would be
> this:
> ... table1.datecol >=dateadd(d,datediff(d,0,table2.datecol),0) and
> table1.datecol < dateadd(d,1 + datediff(d,0,table2.datecol),0)
> HTH,
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||Robert Bravery wrote:
> Hi Bob,
> Thanks for the response.
> Could I well use, youre response gave me an idea, if
> convert(int,@.dol,112)>=convert(int,@.sdate+1,112)
> @.dol and @.sdate are both datetime datatypes
I thought we were dealing with columns, not variables ... ?

> Would that comparison work
>
Did you try it? If you had, you would have answered your own question.
Yes, that will work, but the ",112" part will be ignored. When converting
the datetime to int, you will get the number of days since the seed date,
run this script to see:
select convert(int,getdate(),112),convert(int,g
etdate())
Steve Kass pointed out to me a while back that there is a performance impact
involved in converting a datetime to another datatype, which is why I used
the dateadd(d,datediff ... technique in my post, which Steve showed to be
faster.
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||HI Bob
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message news:%
> I thought we were dealing with columns, not variables ... ?
We are and we arent. I need to compare a few things, all involve variables
and columns, and a combination of them

> Did you try it? If you had, you would have answered your own question.
I did. ANd it did seem to work. Just wanted some confirmation that I was
doing the right thing or doing t the right way.

> Yes, that will work, but the ",112" part will be ignored. When converting
> the datetime to int, you will get the number of days since the seed date,
> run this script to see:
> select convert(int,getdate(),112),convert(int,g
etdate())
> Steve Kass pointed out to me a while back that there is a performance
impact
> involved in converting a datetime to another datatype, which is why I used
> the dateadd(d,datediff ... technique in my post, which Steve showed to be
> faster.
>
Thanks FOr this
Robert|||Thanks
Robert
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:48FD7950-C281-4B31-BB69-B38C76A77EC2@.microsoft.com...
> Compare after taking off the time part of DATETIME field...
> SELECT getdate()
> SELECT CAST(FLOOR(CAST( getdate() AS float)) AS DATETIME)
>
> Thanks,
> Sree
>
> "Jens" wrote:
>

Comparing dates

HI,

In my report there are two parameters,One is for StartDate and One is for enddate.Startdate must lessthan enddate.How to compare two dates in sqlserver report,and if send date is earlier than startdate how to display error message.

Thanks in advance

Try this:

= IIF( CDate(First(Fields!EndDate.Value)) > CDate(First(Fields!StartDate.Value)), "ERROR!", "OK!")

Comparing dates

Hello,

I am trying to retrieve the data that are not more than 3 months. How do I do this? The closest thing that I can do is...

(dbo.CLASSIFIEDADS.PostDate > CONVERT(DATETIME, '2006-03-26 00:00:00', 102))

I want to be able to put 3 months in there somehow...

Thanks in Advance!

check out the DATEDIFF function.|||FYI, it didn't exactly work out the way I wanted so I found another function, DATEADD with a minus number(-90) for days. Thanks for your help.

comparing dates

I try to compare dates in a NON EXISTS expression like this:
SELECT ArticleNo, SerialNumber, DateShipped, DateAssembled,
DatePackedForShipment
FROM ArticleSerialNumbersTempEgersund a
WHERE (NOT EXISTS
(SELECT b.ArticleNo, b.SerialNumber,
b.DateShipped, b.DateAssembled, b.DatePackedForShipment
FROM ArticleSerialNumbers b
WHERE (b.ArticleNo = a.ArticleNo) AND
(b.SerialNumber = a.SerialNumber) AND (CAST(b.DateShipped AS BINARY) =
CAST(a.DateShipped AS BINARY)) AND (CAST(b.DateAssembled AS BINARY) =
CAST(a.DateAssembled AS BINARY)) AND (CAST(b.DatePackedForShipment AS
BINARY) = CAST(a.DatePackedForShipment AS BINARY))))
I have also tried with CONVERT(DATETIME, DateShipped, 102) (same on all the
other dates) but still all records show. If i use only ArticleNo and
SerialNumber then it is no problem. What is it i am missing here?
I use SQL 2000 std server with SP4
Best regards
TrondTry DATEDIFF ( datepart , startdate , enddate ) function.
/Karin
"Trond Hoiberg" wrote:

> I try to compare dates in a NON EXISTS expression like this:
> SELECT ArticleNo, SerialNumber, DateShipped, DateAssembled,
> DatePackedForShipment
> FROM ArticleSerialNumbersTempEgersund a
> WHERE (NOT EXISTS
> (SELECT b.ArticleNo, b.SerialNumber,
> b.DateShipped, b.DateAssembled, b.DatePackedForShipment
> FROM ArticleSerialNumbers b
> WHERE (b.ArticleNo = a.ArticleNo) AND
> (b.SerialNumber = a.SerialNumber) AND (CAST(b.DateShipped AS BINARY) =
> CAST(a.DateShipped AS BINARY)) AND (CAST(b.DateAssembled AS BINARY) =
> CAST(a.DateAssembled AS BINARY)) AND (CAST(b.DatePackedForShipment AS
> BINARY) = CAST(a.DatePackedForShipment AS BINARY))))
> I have also tried with CONVERT(DATETIME, DateShipped, 102) (same on all th
e
> other dates) but still all records show. If i use only ArticleNo and
> SerialNumber then it is no problem. What is it i am missing here?
> I use SQL 2000 std server with SP4
> Best regards
> Trond
>
>|||> SELECT ArticleNo, SerialNumber, DateShipped, DateAssembled,
> DatePackedForShipment
> FROM ArticleSerialNumbersTempEgersund a
> WHERE (NOT EXISTS
> (SELECT b.ArticleNo, b.SerialNumber,
> b.DateShipped, b.DateAssembled, b.DatePackedForShipment
> FROM ArticleSerialNumbers b
> WHERE (b.ArticleNo = a.ArticleNo) AND
> (b.SerialNumber = a.SerialNumber) AND (CAST(b.DateShipped AS BINARY) =
> CAST(a.DateShipped AS BINARY)) AND (CAST(b.DateAssembled AS BINARY) =
> CAST(a.DateAssembled AS BINARY)) AND (CAST(b.DatePackedForShipment AS
> BINARY) = CAST(a.DatePackedForShipment AS BINARY))))
> I have also tried with CONVERT(DATETIME, DateShipped, 102) (same on all
> the other dates) but still all records show. If i use only ArticleNo and
> SerialNumber then it is no problem. What is it i am missing here?
Why are you casting all of these related columns? Are they of different
datatypes? What do you hope to accomplish with the casting?|||I did compare without casting but that had same result. The columns i try to
compare is datetime but value stored in them is 01.10.2005. The date is
inserted from a Access frontend and the developer there has done it this way
for some reason. It is strange tho and i really think it is annoying. When i
make a query in Enterprise manager itself suggest to use CONVERT by
simplyadding it to the expression when i enter 01.10.2005 in criteria field.
So i was asking what is it i am missing here, because it is obviously that i
do and i dont get it.
Best regards
Trond
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uoCR7PDIGHA.916@.TK2MSFTNGP10.phx.gbl...
> Why are you casting all of these related columns? Are they of different
> datatypes? What do you hope to accomplish with the casting?
>|||
>I did compare without casting but that had same result. The columns i try
>to
> compare is datetime but value stored in them is 01.10.2005. The date is
There should be no need to cast columns of the same datatype during the
comparison. Datetime values are not stored in columns (or variables) of
datetime datatype in a format that is readable or that has formatting
characters. Perhaps the values stored in the compared columns are not
exactly the same?

> inserted from a Access frontend and the developer there has done it this
> way for some reason. It is strange tho and i really think it is annoying.
> When i make a query in Enterprise manager itself suggest to use CONVERT by
> simplyadding it to the expression when i enter 01.10.2005 in criteria
> field.
No idea what this means. However, you should avoid using EM as an editing
tool. Use QA where you can control exactly what query is used to view
information as well as what statement is used to insert / update / delete
rows.

> So i was asking what is it i am missing here, because it is obviously that
> i do and i dont get it.
Show the DDL for the tables involved, as well as some sample data that
illustrates the problem. Otherwise, we cannot offer any useful suggestions.sqlsql

Comparing dates

Hello!
I want to check if a date (not time) is found in a column with datetime.
If I have the following rows in a table:
2005-02-02 02:58:00
2005-02-02 07:22:30
2005-01-28 11:33:56
I want to find rows using a stored procedure with the @.date parameter set to
2005-02-02, but within all time during that day.
So with one criteria I want to find the to rows with 2005-02-02...
I don't want to use text, because I want the application and database to run
on different servers in different countries with different configuration for
date format.
This must be a very common task, but I can't figure out how to solve it, so
please help...
Regards MagnusDECLARE @.MyDateParameter char(8)
SET @.MyDateParameter = '20050209'
SELECT *
FROM MyTable
WHERE CONVERT(CHAR(8),MyDateColumn,112) = @.MyDateParameter
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:%23W7sRAvDFHA.1392@.tk2msftngp13.phx.gbl...
> Hello!
> I want to check if a date (not time) is found in a column with datetime.
> If I have the following rows in a table:
> 2005-02-02 02:58:00
> 2005-02-02 07:22:30
> 2005-01-28 11:33:56
> I want to find rows using a stored procedure with the @.date parameter set
> to
> 2005-02-02, but within all time during that day.
> So with one criteria I want to find the to rows with 2005-02-02...
> I don't want to use text, because I want the application and database to
> run
> on different servers in different countries with different configuration
> for
> date format.
> This must be a very common task, but I can't figure out how to solve it,
> so
> please help...
> Regards Magnus
>|||Hi,
try using datediff function eg :
--
Use Northwind
Go
DECLARE @.date as datetime
Set @.date = '04-Jul-1996 19:45:23'
SELECT @.date
SELECT * from Orders
where Datediff(day,Orders.OrderDate,@.date) =0
Let us know if it helps
siaj
"Magnus Blomberg" wrote:

> Hello!
> I want to check if a date (not time) is found in a column with datetime.
> If I have the following rows in a table:
> 2005-02-02 02:58:00
> 2005-02-02 07:22:30
> 2005-01-28 11:33:56
> I want to find rows using a stored procedure with the @.date parameter set
to
> 2005-02-02, but within all time during that day.
> So with one criteria I want to find the to rows with 2005-02-02...
> I don't want to use text, because I want the application and database to r
un
> on different servers in different countries with different configuration f
or
> date format.
> This must be a very common task, but I can't figure out how to solve it, s
o
> please help...
> Regards Magnus
>
>|||On Wed, 9 Feb 2005 22:39:16 +0100, Magnus Blomberg wrote:

>I want to find rows using a stored procedure with the @.date parameter set t
o
>2005-02-02, but within all time during that day.
Hi Magnus,
You've already gotten some suggestions that will work, but if there's an
index on the datetime column, you should use this one instead:
SELECT ...
FROM ...
WHERE MyDateCol >= @.date
AND MyDateCol < DATEADD(day, 1, @.date)
An index can only be used if the indexed column is on it's own on one side
of a comparison operator - if it's in a function or other expression, the
index can't be used to quickly locate the rows you need.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello!
Thanks all of you.
I think all of your suggestions will work, but I'll go for the DateDiff
variant.
Regards Magnus
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

Comparing Date ranges

I have two sets of dates to work with. One is an existing booking with a start and an end date. The other is a new booking with a start and an end date. I want to compare them and calculate how much overlap there is. If the overlap is over a certain amount (say 4 days), then I want to flag the user.

Is there any thing I can use in terms of a SQL query to assist in this comparison? I'm relatively new to SQL so I'm not entirely sure what functions and keywords are available to me to make this comparison.BOL (Books On Line) is the best source for some date functions..they also have sample codes..

hth|||You can you the DATEDIFF(datepart, datetime1,datetime2) function to get the difference between two dates.
Take a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2c1f.asp
for SQL Date Function.|||I actually ended up checking whether the start and end dates of each set were with in a certain range using a BETWEEN X AND Y syntax.

Compare Two Dates

I need to compare tow dates DateField1 and DateField2 and find number of hours between these two dates. Then I need to deduct non-business days and hours (Business days: Monday-Friday and Business Hours: 7:00am-7:00pm) from this and find net hours. How can I do this?

hi jim

could you give with examples what you want to do you question is not very clear?

thanks,

satish.

|||

Ok. Let's say I have DateField1=12/15/2006 7:00pm and DateField2=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.

|||

Hi

What you need is not as simple as it appears.

Here is some code sample but you still need to add more code to finish it:

declare @.resulthourint declare @.resultdayint declare @.begin_end_hourintdeclare @.fromDatedatetimedeclare @.thruDatedatetimeset @.fromDate ='12/15/2006 6:00am'set @.thruDate ='12/18/2006 9:00am'set @.begin_end_hour = 0--caculate the first work hourif(datepart(hh,@.fromDate) <datepart(hh,'7:00am'))beginset @.begin_end_hour = @.begin_end_hour +datepart(hh,'7:00pm') -datepart(hh,'7:00am')endelseif(datepart(hh,@.fromDate) >datepart(hh,'7:00am')anddatepart(hh,@.fromDate) <datepart(hh,'7:00pm'))beginset @.begin_end_hour = @.begin_end_hour +datepart(hh,'7:00pm') -datepart(hh,@.fromDate)endprint @.begin_end_hour--todo caculate the end day work hour-- add code here--todo remember to check @.fromDate and @.thruDate are located in the same day--todo remember to reset the @.fromDate to next day and @.thruDate to the day beforeset @.fromDate =dateadd(day,casewhendatepart(weekday, @.fromDate) % 7 <= 1then 2 -datepart(weekday, @.fromDate) % 7else 0end, @.fromDate)print @.fromDateset @.thruDate =dateadd(day,casewhendatepart(weekday, @.thruDate) % 7 <= 1then -1 -datepart(weekday, @.thruDate) % 7else 0end, @.thruDate)print @.thruDateset @.resultday =datediff(hour,@.fromDate,@.thruDate) / 24 -datediff(week,@.fromDate,@.thruDate) * 2if(@.resultday < 0)set @.resultday = 0print @.resultdayset @.resulthour = @.resultday * (datepart(hh,'7:00pm') -datepart(hh,'7:00am')) + @.begin_end_hourprint @.resulthour

If you have any other problems pls let us know.

Hope this helps.

|||

Hi,

DateTime.ParseExact() supports parsing string to DateTime.

DateTime dt = DateTime.ParseExact("03/29/06", "MM/dd/yy", frmt);

Then you can use DateTime.Compare() to compare your DataTime instances:

DateTime t1( 100 );DateTime t2( 20 );if ( DateTime::Compare( t1, t2 ) > 0 ) Console::WriteLine( "t1 > t2" );if ( DateTime::Compare( t1, t2 ) == 0 ) Console::WriteLine( "t1 == t2" );if ( DateTime::Compare( t1, t2 ) < 0 ) Console::WriteLine( "t1 < t2" );
 
For more information, please see
http://msdn2.microsoft.com/en-us/library/system.datetime.parseexact(VS.80).aspx
http://msdn2.microsoft.com/en-us/library/system.datetime.compare(VS.80).aspx

|||

Hi guy's i'm stuck in a similar problem.

The problem is that i have two fields one shows theLogIn timeof the user and other shows theDuration since the user LogIn

and now i want to store the logout time which is addition ofLogIn time andDuration How i achive this.Plz Help me.

thanx In advance.

Mangat Phogat

Alea IT Soluations

Jaipur(India)

|||

This is what you want i think.

You have few informations fixed .

like Monday-Friday are your working days with timing 7:00 AM to 7PM

that makes (12 hours on 5 days) which equals 60 hours.

Total days are 7 so 7*12 = 84

Your non working hours will be 84-60= 24 hours .

Number of hours will be 7*12 =84;

if your days in between are non working days ...like sat and sunday deduct 24 from the figure u get.

A simple pseudocode might help you

DateTime firstDate = Convert.ToDateTime("8/24/2007");
DateTime secondDate = Convert.ToDateTime("8/31/2007");

DifferenceofDays = firstDate.Day-secondDate.Day // this will give 7

DateTime tempDate = date1;
int nonWorkingHours = 0;

//Check whether there is a non working day in between then add the corresponding non working hours.

for (int dayIndex = 1; dayIndex <= dayDifference; dayIndex++)
{
tempDate = tempDate.Date.AddDays(1);
if (tempDate.Date.DayOfWeek == DayOfWeek.Saturday
|| tempDate.Date.DayOfWeek == DayOfWeek.Sunday)
{
nonWorkingHours += 12;
}
}

int totalWorkingHours = dayDifference * 12;
int netHours = totalWorkingHours - nonWorkingHours;

Thanks

Muhammad Tabish Sarwar