How can I convert DateTime to Date as Parameter?

Hi Guys! Need Help on this!! I am using a Datetime data type as my Parameter on my stored procedure in SQL Server 2005. I am also using Crystal Reports XI for my reporting using the stored procedure in SQL but my problem is that I want to use ONLY the DATE data type as my Parameter instead of using the datetime parameter in Crystal Reports! Since the SQL server does not have a Date data type, how can I convert this from DateTime to Only Date data type as my parameter?....Thanks!!

Use datatime data type and pass just date part from CR or strip off the time part wherever you are planning to use it.

declare @.d datetime

set @.d = getdate()

select dateadd(day, datediff(day, 0, @.d), 0)

go

AMB

|||

Thanks! but how do you pass just the date part from CR? Any idea would be greatly appreciated!! I can strip off the time part inside the stored procedure in SQL 2005 but CR is using the parameter which is datetime....

|||

Sorry about that, but I think that question could be answered better in a CR newsgroup. Try:

datetime(datepart("yyyy", {@.d}), datepart("m", {@.d}), datepart("d", {@.d}), 00, 00, 00)

AMB

|||Thanks AMB....that will work but that code is for the inside on the report...my problem lies in the parameter prompt window..how can I let the user only select the date without seeing the the time on the parameter prompt window?....|||

Sorry I have no idea. As I mentioned in my previos post, these questions would be better asked in a CR newsgroup.

AMB

How can I convert a date and an amount in my select statement

I need to convert a date like 08/1/2009 to 0809

I also need to show currency as 100.00 and not 100.0000

How can I do these in a select statement?

SELECT CONVERT(Varchar(20),ExpirationDate,10) AS ExpirationDate, Amount FROM tblPayment

I appreciate any help!

hi Jackxxx,

can you try this

SELECT convert(varchar,datepart(dd,getdate()))+convert(varchar,datepart(yy,getdate())) AS ExpirationDate, convert(decimal(10,2), 2323.2422)

thanks,

Satish.

|||

I tried the expiration date and the date was 7/1/2009 and your statement returned 172007.

Also I goofed on the other the field name is AmountPaid that I need to show 100.00 for.

|||

hi Jackxxx,

what i gave was an example you need to modify your actuall query accordingly like i've put getdate() so you need to put your datetime field in there similary amount field also.

thanks,

satish,

|||

It's almost perfect, the date still shows all for digits of the year. Is there a way to only show the last two digits? Like 09 for 2009

I very much appreciate your help!

|||

hi jackxxx,

i tried alot but its giving 4 digits atlast i had to cheat, use

select right(datepart(yy,getdate()),2)

hope it works now.

regards,

satish.

How can I convert 12/2/ to 12/2/current year

I have a field in my database that holds a date, the only part of the date I care about is the month and day (12/2/). I'm trying to use this field in a view column to show the for example 12/2/ and the current year (2007). Also if the month and day have passed like 11/1/ then it would be next year (2008).

Can anyone help me with this?

Look up the DatePart function in sql server. It has pretty much what you need.

|||

DECLARE @.ddatetimeSET @.d='11/20/2007'SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END

To put this in a view, just remove the DECLARE and SET statements. Copy the code from CASE through END into your select statement, and replace @.d with the field name from your table.Optionally add ' AS MyNewField' after the END to give the column a name.|||

Motley,

Is it possible to use this in a udf so I can use it in other views?

I tried the following, but received and error:

Msg 102, Level 15, State 1, Procedure ufn_getdate, Line 14

Incorrect syntax near 'END'.

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)
RETURNSDATETIME
BEGIN

SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()
THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)
ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)

END
GO

|||

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)RETURNSDATETIMEBEGIN RETURN (SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END )ENDGO

|||

Motley,

I get a new error: maybe I'm have missed something

Msg 102, Level 15, State 1, Procedure ufn_getdate, Line 12

Incorrect syntax near ')'.

CREATE FUNCTION dbo.ufn_getdate (@.ddatetime)RETURNSDATETIMEBEGINRETURN (SELECTCASEWHENDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)<getdate()THENDATEADD(year,DATEDIFF(year,@.d,getdate())+1,@.d)ELSEDATEADD(year,DATEDIFF(year,@.d,getdate()),@.d)END)GO

|||

Sorry, I editted the above code, it should work now. It was missing an END.

|||

Motley,

Thanks very much for your help, please take the rest of the week off.

How can I combine different rows?

Hi!

I have a table looking like

(username) (account number) (start date) (end date) (product)

wich I can have up to 4 lines for the same client.

I wist to transfert those lines into a new table looking like

(username) (account number) (start date 1) (end date 1) (product 1)
(start date 2) (end date 2) ... (product 4)

How (in SQL) I could do it?>I have a table looking like ..

Please post DDL instead of your personal narrative. If you had done
the talbe properly, i mgiht look like this:

CREATE TABLE AccountHistory
(acct_nbr INTEGER NOT NULL,
product_nbr INTEGER NOT NULL,
product_cnt INTEGER DEFAULT 1 NOT NULL
CHECK(product_cnt BETWEEEN ! AND 4),
PRIMARY KEY (acct_nbr, product_nbr, product_cnt),
user_name VARCHAR(25) NOT NULL,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date < end_date));

I left out the REFERENCES clause you would need and some other
things.

Quote:

Originally Posted by

Quote:

Originally Posted by

>which I can have up to 4 lines [sic] for the same client. <<

Lines appear on a paper form or an input screen; a table has rows.
You need a constraint to enforce this rule.

Quote:

Originally Posted by

Quote:

Originally Posted by

>I wish to transfer those lines into a new table looking like .. <<

You also failed to give any rules for sorting the repeating groups.
But th real question is why are you doing this at all?? That would
violate First Normal Form (1NF). This is not a good way to write
SQL.

how can i check date, hour and minutes only?

I am using this code to insert starttime and endtime.

INSERT INTO working_schedule (id_number, starttime, endtime, created_user, created_pc, created_version, created_domain, created_os, created_workingset) VALUES(@.id_number, @.starttime, @.endtime, @.created_user, @.created_pc, @.created_version, @.created_domain, @.created_os, @.created_workingset)

and this code to check for duplicate before inserting..

IF EXISTS (SELECT id_number, starttime, endtime FROM working_schedule WHERE id_number = @.id_number AND starttime = @.starttime AND endtime = @.endtime)

but it's checking the seconds as well..

how can i only check date, hour and minutes (without the seconds?

If you know that your @.starttime and @.endtime variables NEVER include seconds or milliseconds you can check like this:

AND starttime >= @.starttime
AND starttime < dateadd (mi, 1, @.starttime)
AND endtime >= @.endtime
AND endtime < dateadd (mi, 1, @.endtime)

If your @.starttime and @.endtime variables might include seconds or milliseconds you can do your comparisons like this:

AND starttime >= convert (datetime, convert(varchar(20), @.starttime, 100))
AND starttime < dateadd (mi, 1, convert (datetime, convert(varchar(20), @.starttime, 100)))
AND endtime >= convert (datetime, convert(varchar(20), @.endtime, 100))
AND endtime < dateadd (mi, 1, convert (datetime, convert(varchar(20), @.endtime, 100)))

|||

Use the datediff function:

SELECT id_number, starttime, endtime FROM working_schedule
WHERE id_number = @.id_number
AND DATEDIFF(mi,starttime,@.starttime) = 0
AND DATEDIFF(mi,endtime,@.endtime) = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

How can I change the format of a date returned from asp:calendar

Hello!

I have a table in an SQL database, in which I have a field in datetime format.

In my aspx page I would like to get the date the user chooses from an asp: calendar I have and submit it to the DB.

I already have all the code ready, the datasource, the gridview, all other fields to submit, and I just added a template field with the asp:calendar so that the user could choose a date.

I′m getting this error when I run the page: "Conversion from type 'Date' to type 'Boolean' is not valid."

It seems to be a problem about the date that is given by the Calendar object (?) and the one I should submit to my DB.

Here′s the part of the code where I have my standard Calendar binded to the correspondant field:

<asp:Calendar ID="Calendar1" runat="server" SelectedDate='<%# Bind("data")%>' Visible='<%# Eval("data")%>'>
</asp:Calendar>

I′m gessing I should probably change the format of the date somehow before submit it to the DB, but how?

Thank you all,

RR

Format(dateVariable,"MM/dd/yyyy")

|||

sorry the noobness, but where can I do that?

in a script section in the beginnig of the page?

|||

You have bound the "data" column to both the SelectedDate and the Visible property. SelectedDate is of type Date, and Visible is of type Boolean. What datatype is the "data" column?

|||

You′re asking about the datatype in the db, right?

It′s datetime. (don′t know if it′s the best datatype, any advise here?) I only need a data like DD-MM-YYYY but when building my table in SQL, I have no format like this...

An update to this issue, I erased the visible property and the page at least runs, but no connection between the calendar and my field... maybe it′s better to explain my objective:

What I would need is a gridview where I can see my records. (done)
In the default view I would see all the fields in normal textboxes, (ok!, done)
When clicking insert new or edit, I would like to let the user choose a date from the calendar!
Can anyone help me to buid a thing like this?

THKS

How can I bump date results to next business day

I've got a report that bumps projected cash from Weekends to Weekdays. However, I would like to make sure that the day isn't a holiday either.
How can I test if a date falls on a Weekend or Holiday, and move it to the next business day if necessary?I would create a table in your database labeled "Holidays" and list all the holidays you want to include. Add that table to your report and compare them kind of like the way you're doing it with the weekends already except insead of using the WeekDay function (A.K.A. DayOfWeek) and finding the number corresponding to the day - just compare each date record with each date in the holiday table using a for loop. Hope that isn't too vague. If so, I'll be a little more specific. Good luck. :)

How can I assign a value to a textbox programmatically in a report?

For example: If I want to display the date today, txtDate.Text = DateToday;Type
=System.DateTime.Now
into the Textbox and set the Format (right-click->properties) to dd.MM.yyyy or your corresponding date-format
|||that's design time, what i'm trying to do is at run-time I want to assign a value to a particular textbox in the report. ü|||There is no way other than assigning a expression to the Textbox. The "MS Access way" doesn't work anymore ;(
You can do something like:
=iif( some_condition = true, System.DateTime.Now, "No Date")

or create your own function:
Add
Public Function myFunction(val as Object)
if val is Nothing then return System.DateTime.Now
return "Empty"
End Funcion

to Report-Properties->Code
and call it by
=Code.myFunction(Fields!ConditionColumn.Value)

|||ok thanks, i'll try your solution later. sql

how can I add a time stamp on a table

How can I know when a record on a table has been modified ?
I want to add a field and fill it with a date/time when the recors is modified
ThanksThe only way I know is to use a trigger (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp) to update the column.

-PatP|||Take a look at Lumigent Log Explorer. It allows you to peep into transaction logs to find out who did what when.|||Date and Time Functions
These scalar functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

This table lists the date and time functions and their determinism property. For more information about function determinism, see Deterministic and Nondeterministic Functions.

Function Determinism
DATEADD Deterministic
DATEDIFF Deterministic
DATENAME Nondeterministic
DATEPART Deterministic except when used as DATEPART (dw, date). dw, the weekday datepart, depends on the value set by SET DATEFIRST, which sets the first day of the week.
DAY Deterministic
GETDATE Nondeterministic
GETUTCDATE Nondeterministic
MONTH Deterministic
YEAR Deterministic

See Also

Functions

1988-2000 Microsoft Corporation. All Rights Reserved.|||USE Northwind
GO

CREATE TABLE myTable99(
Col1 int IDENTITY(1,1) NOT NULL PRIMARY KEY
, Col2 char(1)
, ADD_TS datetime DEFAULT GetDate()
, ADD_BY varchar(255) DEFAULT System_User)
GO

-- OK We don't know who did what when, except when it was added

INSERT INTO myTable99(Col2)
SELECT 'A'

SELECT * FROM myTable99

UPDATE myTable99
SET Col2 = 'B'
WHERE Col1 = 1

SELECT * FROM myTable99
OK

-- OK Lets see what we can do
-- Alter the table to track the updates

ALTER TABLE myTable99 ADD UPDATE_TS datetime
GO

ALTER TABLE myTable99 ADD UPDATE_BY varchar(255)
GO

-- Set up a trigger to do the work

CREATE TRIGGER myTrigger99 ON myTable99
FOR UPDATE
AS
BEGIN
UPDATE m
SET UPDATE_BY = System_User
, UPDATE_TS = GetDate()
FROM myTable99 m
INNER JOIN inserted i
ON i.Col1 = m.Col1
END
GO

-- viola

INSERT INTO myTable99(Col2)
SELECT 'C'

SELECT * FROM myTable99

UPDATE myTable99
SET Col2 = 'D'
WHERE Col1 = 2
SELECT * FROM myTable99
OK

DROP TRIGGER myTrigger99
DROP TABLE myTable99
GO|||If you don't like triggers, then you could re-write the application to use only stored procedures to update the tables, then remove update permissions from the tables, to make sure no one sneaks in the back way.|||And if you don't care about getting an actual Date/Time value from the field (just uniqueness), then you can use the timestamp datatype. It's a binary value that is unique in the database, but does not actually represent a date or a time. The benefit is that it automatically updates when the row is updated without the need for any additional code.|||And if you don't care about getting an actual Date/Time value from the field (just uniqueness), then you can use the timestamp datatype. It's a binary value that is unique in the database, but does not actually represent a date or a time. The benefit is that it automatically updates when the row is updated without the need for any additional code.

Huh?

And as for using a sproc...it's no guarentee...

No reason not to use a trigger like this...

Anyone?|||SQL Server Books Online

timestamp is a data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.
...
A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated...
...
A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

I'm just saying, if he's looking for a field that will automatically update without having to do any coding, a timestamp field will do that.

He never said he needed to know the date/time the record was updated, he said he wanted to know when a record is modified. You'd know the record has been modified when the timestamp field changes.|||Going with the stored procedure requires that the DBAs ensure that the programmers don't try to back-end him/her. This would take a (politically) strong DBA group, that can enforce such a rule. Or being able to revoke that all important update permission, which forces the application to use the stored procedure.|||Stored procedures are not sufficient to guarantee relational or data integrity. Somebody can and will eventually hook directly into the table and bypass your logic.

Yeah, the timestamp updates. But how do you KNOW it updated unless you retain the previous value?

The thing you have to worry about is when a record thinks it has been updated, but actually the new data is the same as the previous data. If you have a value in your database such as gender that is "Male" and run:

Update mytable set gender = 'Male'

... the update trigger will run even though the data has not changed. In cases where this distinction is important, I've solved the problem by running a binarychecksum comparison between the new record and the old record.|||Right. But we don't know how he's using the date/time field, so any specific recommendation is moot without additional details on his requirements.|||... Somebody can and will eventually hook directly into the table and bypass your logic...?
Can you give us an example on how you'd go about doing it?
:rolleyes:|||Doing what?

Hooking into the table?
update table set thecolumn = somebaddatavalue

Implementing better integrity?
Use a trigger.

Checking to see whether the data had changed?
Use something like where binarychecksum(inserted.*) <> binarychecksum(currentdata.*), but I'd have to look up my old code to see exactly what syntax I used. I seem to recall using having to use subqueries to get around some of the limitations of the binarychecksum input parameters.

If nanou9999 is interested, I look it up when I have time.|||That's why I keep saying you have to be able to remove the permission to update the table.|||That's why I keep saying you have to be able to remove the permission to update the table.EXACTLY!!!

So my question to blindman was how he'd go about "hooking" (what a term!) into a table, if ALL permissions are denied, and the only way to affect the data is through stored procedures.

I guess I need to be more elaborate in stating my questions, huh?! ;)

So, blindman, how would you "hook" into a table (...hmmmmm...your update will fail, you know)?|||Perhaps you trust your Database Administrators never to directly change data in a table, but I do not. Or perhaps I just build my database applications to be more robust than you do.

If a rule applies to the data, then implement it at the data level, not in every procedure that accesses the data. Common sense.

Now go ahead with your next inane, hair-splitting post, because I know you must, but I'm done with this thread. Ta-ta... :cool:|||... Or perhaps I just build my database applications to be more robust than you do.
I doubt it, but...Is this a challenge?
...If a rule applies to the data, then implement it at the data level, not in every procedure that accesses the data. Common sense. That's a front-end coder's answer, not an application architect's one, but then I never suspected you to be of that caliber either ;)
...Now go ahead with your next inane, hair-splitting post, because I know you must, but I'm done with this thread. Ta-ta...
And as you see I do, but only to demonstrate that you are not the one to decide whether the thread should be closed or not. BTW, the rest of us don't think of ourselves that high-up-in-the-sky either ;) Get off of your cloud of self-praising and adoration of your superiority, be simpler, and people will love you :p|||EDIT: Nevermind...sql

How can I add a fiscal year column to a view

I have a view that shows payment amount, payment date. I need to add a column to the view that shows what fiscal year the payment belongs to.

How can this be done?

I do have a table that has the fiscal start and end in it, tblGlobal with fields FiscalYearStart and FiscalYearEnd.

Maybe you should clarify your problem a little bit more. What kind of data are FiscalYearStart and FiscalYearEnd? Can you provide us a sample dump?

If you have a series of disjoint datetime intervals representing fiscal years, a simple join would do...

|||

create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)

create table tblData
(DataDate datetime
)

insert into tblGlobal values (2000, '01-Oct-1999','30-Sep-2000')
insert into tblGlobal values (2001, '01-Oct-2000','30-Sep-2001')
insert into tblGlobal values (2002, '01-Oct-2001','30-Sep-2002')
insert into tblGlobal values (2003, '01-Oct-2002','30-Sep-2003')

insert into tblData values ('15-Oct-1998')
insert into tblData values ('15-Oct-1999')
insert into tblData values ('15-Oct-2000')
insert into tblData values ('15-Oct-2001')
insert into tblData values ('15-Oct-2002')
insert into tblData values ('15-Oct-2003')
insert into tblData values ('15-Oct-2004')

select
tblGlobal.FiscalYear
,tblData.DataDate
from tblData
inner join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd

select
tblGlobal.FiscalYear
,tblData.DataDate
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd

Note that the second query will return data even if it does not find a fiscal year match.

That's useful if you are concerned that you will forget to load tblGlobal on time at the start of a new fiscal year, or if data gets entered with an old or far future date.

It's important to understand that you can join on whatever you want to join on - you are not limited to just foreign key columns!

|||

Ok, my fiscalyearstart and end fields are datetime. The date would look like paymentdate, AmountPaid, from table tblPayments. The fiscalyearstart and end are in tblGlobal.

10/1/2007 12:00:00 PM, $1000.00

The fiscal year might be 7/1/yyyy to 6/30/yyyy or 10/1/yyyy to 9/30/yyyy

I was thinking it might be good figure out a way to put the fiscal year in a field with each payment, unless there is a way to do it in the view.

I appreciate any help you give.

|||

David this looks great. But I need to tell you one more thing, the reason I was looking to possibly have a field or column that showed the fiscal year for each payment is I will be using the view with a reporting tool. So I need a field to group on so I can show the sum of payments for each fiscal year.

Does it sound like this is something you can help with?

I greatly appreciate your help.

|||

create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)

create table tblData
(DataDate datetime
,DataValue int
)

insert into tblGlobal values (2000, '01-Oct-1999','30-Sep-2000')
insert into tblGlobal values (2001, '01-Oct-2000','30-Sep-2001')
insert into tblGlobal values (2002, '01-Oct-2001','30-Sep-2002')
insert into tblGlobal values (2003, '01-Oct-2002','30-Sep-2003')

insert into tblData values ('15-Oct-1998', 5)
insert into tblData values ('15-Oct-1999', 10)
insert into tblData values ('15-Oct-1999', 11)
insert into tblData values ('15-Oct-1999', 12)
insert into tblData values ('15-Oct-2000', 100)
insert into tblData values ('15-Oct-2000', 101)
insert into tblData values ('15-Oct-2000', 102)
insert into tblData values ('15-Oct-2001', 1000)
insert into tblData values ('15-Oct-2001', 1001)
insert into tblData values ('15-Oct-2001', 1002)
insert into tblData values ('15-Oct-2002', 2000)
insert into tblData values ('15-Oct-2003', 3000)
insert into tblData values ('15-Oct-2004', 4000)

select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
inner join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear

select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear

The group by is pretty standard sql, and allows you to produce summaries, counts, averages, etc for the group.

|||

I will give this a try and let you know how it goes. Thank you so much.

|||

David, or anyone that would know how to do this. I found that the reporting tool I must use will only accept views and will not let me join on a date range, only single fields.

I think what I need is something like this: (this is an uneducated thought)

I have a view of all payments ever made.

In my view I need an expression column that returns what fiscal year the payment was madebased on the month part of the fiscalyearstart and fiscalyearend fields from tblGlobal (not sure how we would join them in the view)

So if the payment date is 7/2/2007 and the fiscalyearstart month is 7 and the fiscalyearend month is 6 the expression column would return 2008

The actual fiscal year would range 7/1/2007 to 6/30/2008

I have no idea how to do this, this is just my thought.

|||

Why not do all the joins you need to do in a view in the database. They your reporting tool is just issuing a simple select statement:

create view some_view_name as
select
tblGlobal.FiscalYear
,sum(tblData.DataValue) as FiscalYearDataValue
from tblData
left outer join tblGlobal
on tblData.DataDate between tblGlobal.FiscalYearStart and tblGlobal.FiscalYearEnd
group by tblGlobal.FiscalYear

Your report tool will surely allow you to add a where clause to a query using this view:

select * from some_view_name
where FiscalYear = 2002

ps - When you get the problem you asked for help on solved, you should really mark that thread as answered. If you have a new problem, you should start a new thread. That way, others who have a similar problem can find your thread and its answer.

|||

David,

You are very correct, but I had thought about it and if there was a way to not base this on the entire fiscalyearstart and end from the tblGlobal. What I mean is that if they need to base a report on multiple fiscal years then the between join would only allow them to see the fiscal year entered into the tblGlobal table. So I was hoping there was some way to do what you have done here and base it somehow on the months of the fiscal year not the actual fiscal year. Just incase they need to base the report on multiple fiscal years.

For example;

If they enter a payment on 7/2/2007 and the fiscalyearstart is 7/1 and the fiscalyearend is 6/30 then the fiscal year the payment was entered is 2008 or If they enter a payment on 7/2/2007 and the fiscalyearstart is 10/1 and the fiscalyearend is 9/30 then the fiscal year the payment was entered is 2007.

You may have a more educated idea, but this is just what I was thinking about.

I really do appreciate your help.

|||

This is the sample tblGlobal I guessed at:

create table tblGlobal
(FiscalYear int
,FiscalYearStart datetime
,FiscalYearEnd datetime
)

It would allow you to store as many fiscal years as you want.

Are you telling me that tblGlobal only stores one record, and that FiscalYearStart and FiscalYearEnd are Month/Day, not a Month/Day/Year values?

Because it sure would have saved some time to know that two days ago!

You need to post the create table statements for the tables involved, along with sample data.

|||

Sorry, David,

Yes, the tblGlobal only has one record. The fiscalyearstart and end fields are datetime and do hold the entire date, like 7/1/2007.

Again sorry for missing this key detail. The data samples you have shown look fine to me.

|||

If you mean that if we have FiscalYearStart 2007-07-01, all days before 07/01 (1st of July) of year Y would be in fiscal year Y-1 and all days after 07/01 would be in fiscal year Y, you may create a function to get this year like this:

CREATE FUNCTION [dbo].[GetFiscalYear] (@.tDateTime)RETURNSintASBEGIN-- Declare the return variable hereDECLARE @.ResultintDECLARE @.fmint;DECLARE @.fdint;SELECT @.fm =month(FiscalYearStart), @.fd =day(FiscalYearStart)FROM tblGlobal;IF month(@.t) > @.fmORmonth(@.t) = @.fmANDday(@.t) >= @.fdSET @.Result =year(@.t);ELSESET @.Result =year(@.t)-1;RETURN @.Result;END

Then you may use it like in this example:

select orderid, customerid, orderdate, dbo.GetFiscalYear(orderdate)from Orders

|||

I tried the function, I past it a paymentdate of 7/16/2007 and it returned 2007, but it should be 2008.

If the fiscalyearstart is 7/1 and the fiscalyearend is 6/30 (7/1/2007 to 6/30/2008) so it is plus one here

7/16/2007 = a fiscal year of 2008 because the fiscal year ends on 6/30/2008

If the fiscalyearstart is 10/1 and the fiscalyearend is 9/30 (10/1/2006 to 9/30/2007)

7/16/2007 = a fiscal year of 2007 because the fiscal year ends on 6/30/2007

Does this sound right?

|||

So flip the greater than/less than signs as needed, and add 1 instead of subtract 1 as needed.

It's your turn to do the programming!

How can I Add 02.45 hour to my Date [solution is that best way?]

I found a solution but I am not sure that is one of best solution?
Declare @.StartDate as datetime
Declare @.AddDate as datetime
Set @.AddDate = '1899-12-30 02:15:00.000'
Set @.StartDate = '2005-01-01 05:00'
Select @.StartDate as StartDate,
DateAdd(Minute, DatePart(Minute, @.AddDate), DateAdd(hour, DatePart(Hour,
@.AddDate), @.StartDate))
as FinishDate
Thanks MullerjannieThe most readable solution, in my opinion, is one of these choices:
SELECT @.StartDate + @.AddDate
SELECT @.StartDate + '2:15'
For this, @.AddDate should be set to '2:15'
The 1899 date will not work, since it represents two days
backwards from what you want.
Steve Kass
Drew University
Murat BUDAK wrote:

>I found a solution but I am not sure that is one of best solution?
>Declare @.StartDate as datetime
>Declare @.AddDate as datetime
>Set @.AddDate = '1899-12-30 02:15:00.000'
>Set @.StartDate = '2005-01-01 05:00'
>
>Select @.StartDate as StartDate,
> DateAdd(Minute, DatePart(Minute, @.AddDate), DateAdd(hour, DatePart(Hour,
>@.AddDate), @.StartDate))
>as FinishDate
>Thanks Mullerjannie
>
>sql

How can I Add 02.45 hour to my Date

StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
now.
Set @.StartDate = '1899-12-30 02:15:00.000'
Select GetDate() now, GetDate() + @.StartDate as added
-- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
Thanks
Murat BUDAKYou should specify to what you want to add it to, SQL's date is broken down
into segments, year, month day etc...
Have a look at the dateadd function
Untested :
select dateadd(hour,2.15,getdate())
You would say, dateadd(hour,@.targetdate,2.15) I don't know if the decimal
might bugger up your solution.
Personally I would convert the amount of time in hours to seconds and then
use dateadd seconds to add the seconds to my current date
"Murat BUDAK" wrote:

> StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
> now.
> Set @.StartDate = '1899-12-30 02:15:00.000'
> Select GetDate() now, GetDate() + @.StartDate as added
> -- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
> Thanks
> Murat BUDAK
>
>|||On Tue, 8 Feb 2005 14:15:10 +0200, Murat BUDAK wrote:

>StartDate is actually 02:15 as datetime so why I cannot add 02:15hour to
>now.
>Set @.StartDate = '1899-12-30 02:15:00.000'
>Select GetDate() now, GetDate() + @.StartDate as added
>-- Result is 2005-02-08 14:11:07.860 -- 2005-02-06 16:26:07.860
>Thanks
>Murat BUDAK
>
Hi Murat,
I don't recommend it, but if you really insist on using the + operator
between two datetime variables to add an amount of time, then you need to
get at least your base date and time right.
Run this in QA
SELECT CAST ('02:15:00.000' AS datetime)
to see why getdate() + '1899-12-30 02:15:00.000' won't result in "now + 2
1/4 hour".
The recommended method to add time to a datetime value is to use DATEADD:
SELECT DATEADD (minute, (60*2) + 15, getdate())
or
SELECT DATEADD (hour, 2, DATEADD (minute, 15, getdate()))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Tue, 8 Feb 2005 04:29:02 -0800, Mal .mullerjannie wrote:
(snip)
>Untested :
>select dateadd(hour,2.15,getdate())
Hi Mal,
DATEADD takes an integer as second parameter, so the fractional part
(0.15) will be discarded.
And even if DATEADD would take fractions, this would still be wrong, as
two hours and fifteen minutes equals 2.25 hours, not 2.15 hours.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How can I achieve this

Hi,
I created a report to display in invoice when passed an invoice number. How
can I display multiple invoices when i pass either a date range? I am not
sure how to get multiple invoices, one after the other on different page.
ThanksOn Jun 2, 12:53 pm, Chris <C...@.discussions.microsoft.com> wrote:
> Hi,
> I created a report to display in invoice when passed an invoice number. How
> can I display multiple invoices when i pass either a date range? I am not
> sure how to get multiple invoices, one after the other on different page.
> Thanks
There are a few different options here. You can use a subreport that
will automatically print out on different pages or you can use a table/
matrix control and group on invoice number and right-click the control
-> select Properties -> select the Groups tab -> select Edit... -> and
select 'Page break at end.' Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

How can db_owner restore db from backup and keep his permissions?

Hi there
I'm developing an application, and from time to time I take it to
production environment and keep it up to date with dev version. When I
tried to do this alone (istead of sysadmin) I got some error saying
that I didn't have enough permissions to do restore (despite I was a
db_owner).
Is it possible, that the reason for that is the fact that in dev
version which I was restoring there was no user which had db_owner
permissions? so, I was working on this database as some user with
db_owner permissions and restored it to the state in which there wasn't
any user mapped to my login in db anymore.
Is it possible? If so, then how can I restore db as a db_owner?
thanks a lot
HPFrom BOL:
"If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists)."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How can db_owner restore db from backup and keep his permissions?

Hi there
I'm developing an application, and from time to time I take it to
production environment and keep it up to date with dev version. When I
tried to do this alone (istead of sysadmin) I got some error saying
that I didn't have enough permissions to do restore (despite I was a
db_owner).
Is it possible, that the reason for that is the fact that in dev
version which I was restoring there was no user which had db_owner
permissions? so, I was working on this database as some user with
db_owner permissions and restored it to the state in which there wasn't
any user mapped to my login in db anymore.
Is it possible? If so, then how can I restore db as a db_owner?
thanks a lot
HP
From BOL:
"If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists)."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

How can db_owner restore db from backup and keep his permissions?

Hi there
I'm developing an application, and from time to time I take it to
production environment and keep it up to date with dev version. When I
tried to do this alone (istead of sysadmin) I got some error saying
that I didn't have enough permissions to do restore (despite I was a
db_owner).
Is it possible, that the reason for that is the fact that in dev
version which I was restoring there was no user which had db_owner
permissions? so, I was working on this database as some user with
db_owner permissions and restored it to the state in which there wasn't
any user mapped to my login in db anymore.
Is it possible? If so, then how can I restore db as a db_owner?
thanks a lot
HPFrom BOL:
"If the database being restored does not exist, the user must have CREATE
DATABASE permissions to be able to execute RESTORE. If the database exists,
RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database (for the FROM
DATABASE_SNAPSHOT option, the database always exists)."
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

how are these queries evaluted differently by sql server?

I'm looking for the minimum date of an entry into a history table. The table contains multiple entries for the customer and the item with an activation and deactivation date for each entry.

I could use the following:

select customerId, item, min(activationDate) from history group by customerId, item

or a sub query

select customerId, item, activationDate

from history h1

where activationDate=(select min(activationDate) from history h2 where h2.customerId=h1.customerId and h2.item=h1.item)

How are these two queries parsed differently by SQL.

They return a different number of results.

Thanks,

karen

I'm guessing that customerId and item do not uniquely define a record in the table, is this true? If so, that would explain the difference in the amount of rows between the two result sets.

-The first query finds the minimum activationDate for each unique customerId and item.

-The second query finds the minimum activationDate for each customerId and item.

-If you add a group by customerId, item to the second query you should have matching result sets.

The inner query of the correlated subquery is processed once per record of the outer query.

|||

If you have the following data in the table:

CustomerID - Item - ActivationDate

1 1 1/1/2007

1 1 1/1/2007

The first query will return one record while the second query will return two. This is because (as the above post indicates), that the first query is returning a true grouping while the second will return duplicate records if there is duplicate data in the table.

|||

Bcs there are duplicate entries available on your database..

You can force DISTINCT class to fix this..

Sample..

Create Table #samplehistory (

[customerId] int ,

[item] int ,

[activationDate] datetime

);

Insert Into #samplehistory Values('1','1','1/1/2007');

Insert Into #samplehistory Values('1','1','1/2/2006');

Insert Into #samplehistory Values('1','1','1/3/2006');

Insert Into #samplehistory Values('1','2','1/11/2007');

Insert Into #samplehistory Values('1','2','1/11/2003');

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate) activationDate

from

#samplehistory

group by

customerId, item

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

After duplicating one of the value.. You are result is correct but there are dupicate data in the result

Insert Into #samplehistory Values('1','2','1/11/2002');

select

customerId,

item,

min(activationDate)

from

#samplehistory

group by

customerId, item

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

select

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

122002-01-11 00:00:00.000

*/

The group by class force the First query to avoid the duplicates (already distincted values are return).

After Distinct on second query,

Code Snippet

select distinct

customerId,

item,

activationDate

from

#samplehistory h1

where

activationDate=

(

select

min(activationDate)

from #samplehistory h2

where

h2.customerId=h1.customerId

and h2.item=h1.item)

/*

customerIditemactivationDate

-- -- --

112006-01-02 00:00:00.000

122002-01-11 00:00:00.000

*/

|||

Thank you very much for your help. I really appreciate the time.

Would you recommend a book for dealing with these kinds of sublties in SQL?

Karen

|||

Inside SQL Server 2005 T-SQL Querying by Itzik Ben-Gan is very good.

|||Ken Henderson's "The Guru's Guide to Transact-SQL" is also very good.

How "WHERE" clause can be used in MDX query?

Hi,

I am writing MDX query to retrive a set of data based on selected range of date.

I have written a MDX query but it is not filtering .

My code:

SELECT NON EMPTY { [Measures].[Fact Table Count] } ON COLUMNS, NON EMPTY topcount({ ([Date Time1].[Date Time1].[Date Time1].ALLMEMBERS ) } ,1000)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Date Time1].[Date Time1].&[2006-01-25T05:53:07] : [Date Time1].[Date Time1].&[2006-02-25T15:53:56] ) ON COLUMNS FROM [Cube Analysis]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

This code should display only data with selected range of date but it displaying all data.

Can any one give a solution to filter the data based on Date using WHERE clause.

Thank you.

The subcube filter looks fine. Can you repro this against the Adventure Works cube?|||

Try this

with member [Measures].[FILTERBYTIME] AS

SUM(

CROSSJOIN(

[Measures].[Fact Table Count],

[Date Time1].[Date Time1].&[2006-01-25T05:53:07] : [Date Time1].[Date Time1].&[2006-02-25T15:53:56]

)

)

SELECT

NON EMPTY { [Measures].[FILTERBYTIME] } ON COLUMNS,

NON EMPTY topcount(

{

([Date Time1].[Date Time1].[Date Time1].ALLMEMBERS )

} ,1000

)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Cube Analysis] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

or try this one.

SELECT

NON EMPTY

{ [Measures].[Fact Table Count] } ON COLUMNS,

NON EMPTY topcount(

{

([Date Time1].[Date Time1].[Date Time1].ALLMEMBERS )

} ,1000

)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Cube Analysis]

where

{

[Date Time1].[Date Time1].&[2006-01-25T05:53:07] : [Date Time1].[Date Time1].&[2006-02-25T15:53:56]

}

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

How "WHERE" clause can be used in MDX query?

Hi,

I am writing MDX query to retrive a set of data based on selected range of date.

I have written a MDX query but it is not filtering .

My code:

SELECT NON EMPTY { [Measures].[Fact Table Count] } ON COLUMNS, NON EMPTY topcount({ ([Date Time1].[Date Time1].[Date Time1].ALLMEMBERS ) } ,1000)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Date Time1].[Date Time1].&[2006-01-25T05:53:07] : [Date Time1].[Date Time1].&[2006-02-25T15:53:56] ) ON COLUMNS FROM [Cube Analysis]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

This code should display only data with selected range of date but it displaying all data.

Can any one give a solution to filter the data based on Date using WHERE clause.

Thank you.

The subcube filter looks fine. Can you repro this against the Adventure Works cube?|||

Try this

with member [Measures].[FILTERBYTIME] AS

SUM(

CROSSJOIN(

[Measures].[Fact Table Count],

[Date Time1].[Date Time1].&[2006-01-25T05:53:07] : [Date Time1].[Date Time1].&[2006-02-25T15:53:56]

)

)

SELECT

NON EMPTY { [Measures].[FILTERBYTIME] } ON COLUMNS,

NON EMPTY topcount(

{

([Date Time1].[Date Time1].[Date Time1].ALLMEMBERS )

} ,1000

)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Cube Analysis] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

or try this one.

SELECT

NON EMPTY

{ [Measures].[Fact Table Count] } ON COLUMNS,

NON EMPTY topcount(

{

([Date Time1].[Date Time1].[Date Time1].ALLMEMBERS )

} ,1000

)DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Cube Analysis]

where

{

[Date Time1].[Date Time1].&[2006-01-25T05:53:07] : [Date Time1].[Date Time1].&[2006-02-25T15:53:56]

}

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Hours/minutes in the Calendar date time prompt

Is there any way to get teh date time prompt to display hours & minutes? For example, if I default the field to '=Today', I would like to see '8/10/2006 9:04 AM' instead of '8/10/2006'

Similarly, when I pick a date using the date picker control that is displayed by default, I would like it to also display the time, which I guess would default to 12:00 AM.

Thanks in advance!

I have a report parameter set using :

=DateSerial(Year(now()), Month(now()), 0) that displays the last day of the month as default and when you view the report the end date is populated as:

7/31/2006 12:00:00 AM

does that help?

|||

Hi,

I encountered the same situation once and this is what I did:

in the report parameter --> default values, write the following expression

=today().addseconds(1). This will display the data and time when you run the report. The only thing is that for the default value it will add 1 second.

--Amde

|||

Thanks...your replies got me off on the right path. I ended up using the following:

=DateAdd("h",12,Today)

and

=now

I had been using =Today which just returns a date. Duh.