Gantt charts?

I have a table with start and end dates for useage of an asset and would
like to generate a sort of gantt chart for a date range. ie a horizontal
bar graph like Microsoft Project with the assests down the left-hand-side
and dates along the top.
Is this possible with SSRS? I feel like I have read about RS features that
would assist with this but can not now find an example.
Any ideas would be much appreciated.
cheers,
Paul.On Apr 28, 9:08 pm, Paul Ritchie <REMOVEpritc...@.xtraREMOVE.co.nz>
wrote:
> I have a table with start and end dates for useage of an asset and would
> like to generate a sort of gantt chart for a date range. ie a horizontal
> bar graph like Microsoft Project with the assests down the left-hand-side
> and dates along the top.
> Is this possible with SSRS? I feel like I have read about RS features that
> would assist with this but can not now find an example.
> Any ideas would be much appreciated.
> cheers,
> Paul.
I myself haven't done Gantt charts via SSRS: it seems to be a rare
topic. This company seems to have experience in this area:
http://www.randsmanagement.com/
I would suggest using a stacked bar chart to accomplish this.
Regards,
Enrique Martinez
Sr. Software Consultantsql

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.
Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields are
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||See update statement at end of the script. The code is not dependent on any language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Date_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>
|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields ar
e
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See update statement at end of the script. The code is not dependent on any
language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gb
l...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Dat
e_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields are
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See update statement at end of the script. The code is not dependent on any language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Date_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>> Hello all
>> I have two fields Date and Time. I've just created a new filed called
>> Date_Time, I need put the old information in the new field. All fields
>> are DateTime type data.
>> Thanks a lot.
>

Further Question on American Date Format

Thanks Scott and Aron for the info on American date formatting. However I'm
still slightly confused.
recap of problem:
One of my table's in SQL Server contains a field of a field type 'datetime'
and recording of dates are stored in American format and not Irish/Uk
format. As sugessted by previous posting that the client application control
this, I used Enterprise Manager to insert a record into the table and the
date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
not as "16/08/2004 4:34:52PM"
This is causing me major proiblems as I'm trying to run simple reporting
form a web base client base on dates. The only way the reporting will work if
I change the date format on my machine to american format.
Any ideas ?
Thanks,
Liam
Hi
SQL does not store the date in an human readable format and does not persist
the formatting you used to enter it. It is up to you, when you select the
data, to format it to what you want.
Look at CAST or CONVERT in BOL for formatting information.
Regards
Mike
"Liam Mac" wrote:

> Thanks Scott and Aron for the info on American date formatting. However I'm
> still slightly confused.
> recap of problem:
> One of my table's in SQL Server contains a field of a field type 'datetime'
> and recording of dates are stored in American format and not Irish/Uk
> format. As sugessted by previous posting that the client application control
> this, I used Enterprise Manager to insert a record into the table and the
> date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
> not as "16/08/2004 4:34:52PM"
> This is causing me major proiblems as I'm trying to run simple reporting
> form a web base client base on dates. The only way the reporting will work if
> I change the date format on my machine to american format.
> Any ideas ?
> Thanks,
> Liam
sql

Further Question on American Date Format

Thanks Scott and Aron for the info on American date formatting. However I'm
still slightly confused.
recap of problem:
One of my table's in SQL Server contains a field of a field type 'datetime'
and recording of dates are stored in American format and not Irish/Uk
format. As sugessted by previous posting that the client application control
this, I used Enterprise Manager to insert a record into the table and the
date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
not as "16/08/2004 4:34:52PM"
This is causing me major proiblems as I'm trying to run simple reporting
form a web base client base on dates. The only way the reporting will work if
I change the date format on my machine to american format.
Any ideas '
Thanks,
LiamHi
SQL does not store the date in an human readable format and does not persist
the formatting you used to enter it. It is up to you, when you select the
data, to format it to what you want.
Look at CAST or CONVERT in BOL for formatting information.
Regards
Mike
"Liam Mac" wrote:
> Thanks Scott and Aron for the info on American date formatting. However I'm
> still slightly confused.
> recap of problem:
> One of my table's in SQL Server contains a field of a field type 'datetime'
> and recording of dates are stored in American format and not Irish/Uk
> format. As sugessted by previous posting that the client application control
> this, I used Enterprise Manager to insert a record into the table and the
> date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
> not as "16/08/2004 4:34:52PM"
> This is causing me major proiblems as I'm trying to run simple reporting
> form a web base client base on dates. The only way the reporting will work if
> I change the date format on my machine to american format.
> Any ideas '
> Thanks,
> Liam

Functions in Parameter Definitions

I have a pair of reports that are the same except that one takes date parameters and another runs for the calculated lst full month. Currently they run off of separate stored procedures, but I would like to combine them. I don't know if my reporting tool will handle it or not, but I want to test it, so I need to combine the 2 sp's into one. I thought the first thing to try would be to set the default values to the start and end date of the previous month, like so:

CREATE PROCEDURE [dbo].[usp_blahblah]

(

@.StartDate SmallDateTime = DateAdd(mm,-1,DateAdd(mm,DateDiff(mm,0,GetDate()),0)), -- 1st of Last Month

@.EndDate SmallDateTime = DateAdd(ms,-3,DateAdd(mm, DateDiff(mm,0,GetDate()),0)) -- End of Last Month

)

..but it won't parse ("Incorrect syntax near '('."). So I'm thinking that you can't use a function in the definition of a param, although I can't find any documentation.

I'm sure there are other approaches, but I thought this would be the most straight forward... Does anybody have a really elegant idea?

From the CREATE PROCEDURE documentation - "Is a default value for the parameter. If a default is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL."

Just make the default value NULL, then test to see if the parameters are NULL and set them in the body of the proc. Like this

CREATE PROCEDURE [dbo].[usp_blahblah]
(
@.StartDate SmallDateTime = NULL,
@.EndDate SmallDateTime = NULL)
IF @.StartDate IS NULL AND @.EndDate IS NULL
BEGIN
SET @.StartDate = DateAdd(mm,-1,DateAdd(mm,DateDiff(mm,0,GetDate()),0)), -- 1st of Last Month
SET @.EndDate = DateAdd(ms,-3,DateAdd(mm, DateDiff(mm,0,GetDate()),0)) -- End of Last Month
END

|||That's slick. I'll give it a try and hope Crystal can handle it!|||

Be careful using the above approach though if you are using the variables further in a query. The query optimizer can do parameter sniffing to get optimal plan based on the parameter values but if you modify it within the SP then the sniffing cannot happen. So if you need to modify the parameter values then move the actual query into another SP and pass the modified parameters to that instead. See the link below for more details on how plan caching works.

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

Functions

I have a subquery that is running really slow when i use a date from the parent query

* the function udfMinContact returns a table of the earliest contact for every child that occured after the date passed to the function

* the function udfMaxReferral returns a table of the latest Referral for every child that occured before the date passed to the function

* the referral happens first, then the child is contacted. i'm looking for contacts that happed over 45 days after the referral
*************************************************************************************
DECLARE
@.EndDate DateTime,
@.StartDate DateTime

SET @.StartDate = '4/1/2007'
SET @.EndDate = '6/30/2007'

SELECT c.ChildId, c.FN, c.LN, c.DOB

FROM Child c INNER JOIN udfMinContact(@.StartDate) ct ON c.ChildID = ct.ChildId

WHERE ct.ContactDate BETWEEN @.StartDate AND @.EndDate

AND EXISTS
(
SELECT ChildId
FROM udfMaxReferral(ct.ContactDate) r
WHERE r.ChildId = c.ChildId
AND r.ReferralDate < DATEADD(dd, -45, ct.ContactDate)

)
*********************************************************************************************

If i run as is, it takes over 40 min. If i replace 'ct.ContactDate' which i highlighted with a static date like '1/1/2007' it runs in just a few seconds.

any idea why the drastic time difference and any suggestions on how to speed it up?

thanks

Check the Execution Plan.

Your FUNCTION has to fully execute for each and every row in the table, perhaps two times per row if it needs to re-calculate for the sub-query..

You may be able to substanially improve execution speed if you JOIN with the data that has the earliest contact INSTEAD of using the function. (I'm assuming that the function is a query.)

Please post the entire FUNCTION code and we can better determine the optimal way to deal with your issue.

|||

Is udfMaxReferral a multi-statement or inline TVF? Look at the query plan to see how the join is being done. If it is a nested loop join then it is possible that the TVF is invoked for every row that is being joined in the outer SELECT statement. Also, if the TVF is multi-statement then there are no statistics on the rows being returned so the plan will be sub-optimal. You should consider using inline TVF so that the query can be optimized as a whole.

Now, as for the question why if you use a variable or column in the TVF it is slower than a value or constant is due to plan caching and query optimization. When you specify a constant in a predicate or parameter to SP or function etc then the query optimizer can use that value and determine the best plan based on the available statistics. On the other hand, if you specify a variable or column then the value is not known and it can be any value within the domain of a data type so the query optimizer will pick a plan that works optimally for any search value. See the white paper on compilation, recompilation for more details on how this works.

|||Function: udfMinContact
Description: Returns info on the earliest 'IF' Contact (that occured on or after the StartDate) for every child

ALTER FUNCTION [dbo].[udfMinContact]
(
@.StartDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ContactId uniqueidentifier,
ContactDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ContactId, ContactDate
FROM Contact ct
WHERE ContactId =
(
SELECT TOP(1) ContactId
FROM Contact
WHERE ChildId = ct.ChildId
AND ContactDate >= @.StartDate
ORDER BY ContactDate ASC, CREATE_TIME ASC
)

RETURN
END;

*********************************************************************************

Function: udfMaxReferral
Description: Returns info on the most current Referral (that occured on or before the EndDate) for every child

ALTER FUNCTION [dbo].[udfMaxReferral]
(
@.EndDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ReferralId uniqueidentifier,
ReferralDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ReferralId, ReferralDate
FROM Referral r
WHERE ReferralId =
(
SELECT TOP(1) ReferralId
FROM Referral
WHERE ChildId = r.ChildId
AND ReferralDate <= @.EndDate
ORDER BY ReferralDate DESC, CREATE_TIME DESC
)

RETURN
END;

*********************************************************************************************************

I know the functions are not ideal, but the way the database is set up, it's the best i could do. there are many cases where a child will have several contacts or referrals on the same day so this is how i forced only 1 to be returned
|||

you can try this, and check if it can change your query speed,

notice, the get_datetime is a function needed you defined it.

i think ORDER BY clause always waste resource!!!

ALTER FUNCTION [dbo].[udfMaxReferral]
(
@.EndDate datetime
)
RETURNS @.retChildList TABLE
(
ChildId uniqueidentifier,
ReferralId uniqueidentifier,
ReferralDate datetime
)

AS
BEGIN

INSERT @.retChildList
SELECT ChildId, ReferralId, ReferralDate
FROM Referral a inner join

(

SELECT ChildId,ReferralId,MAX(get_datetime(ReferralDate,ReferralTime) datetime
FROM Referral

WHERE ReferralDate <= @.EndDate

GROUP BY ChildId,ReferralId

) b on a.ChildId=b.ChildId and a.ReferralId=ReferralId.ReferralId and get_datetime(a.ReferralDate,a.ReferralTime)=b.datetime

WHERE ReferralDate <= @.EndDate

RETURN
END;

|||

As per Uma's suggestion you have to convert your Multilined TVF to Inline TVF,

Use the following functions,

Code Snippet

CREATE FUNCTION [dbo].[udfMinContact]

(

@.StartDate datetime

)

RETURNS TABLE

AS

RETURN (

SELECT ChildId, ContactId, ContactDate

FROM Contact ct

WHERE ContactId =

(

SELECT TOP(1) ContactId

FROM Contact

WHERE ChildId = ct.ChildId

AND ContactDate >= @.StartDate

ORDER BY ContactDate ASC, CREATE_TIME ASC

)

)

GO

CREATE FUNCTION [dbo].[udfMaxReferral]

(

@.EndDate datetime

)

RETURNS TABLE

AS

RETURN

(

SELECT ChildId, ReferralId, ReferralDate

FROM Referral r

WHERE ReferralId =

(

SELECT TOP(1) ReferralId

FROM Referral

WHERE ChildId = r.ChildId

AND ReferralDate <= @.EndDate

ORDER BY ReferralDate DESC, CREATE_TIME DESC

)

)

|||

Manivannan.D.Sekaran wrote:

As per Uma's suggestion you have to convert your Multilined TVF to Inline TVF,

What is the difference? Sorry I'm still pretty new to anything more the simple SQL

function using comparing dates not working right

Hi,

I'm trying to write a function to return all notes with date. Sample data for 1 record=187189 as follows:
iincidentid,iWorkNoteId,iSeqnum, dtEntryDate, workNoteAll
187189 3440 1 2006-04-24 note1
187189 3545 1 2006-06-22 note2
187189 3547 1 2006-06-22 note3
187189 3653 1 2006-08-10 note4
187189 3653 2 2006-08-10 note5

funtion will return = 2006_08-10 note4 note5 for iincidentid=187189
----------------
CREATE FUNCTION dbo.getIncidentNotesRev(@.iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
declare @.incidentId int
declare @.worknoteid int
declare @.worknotesaveid int
declare @.seqnum int
declare @.dtEntryDate smalldatetime
declare @.worknoteall varchar(8000)
declare @.allnotes varchar(8000)
declare @.currentWEDate smalldatetime
declare @.beginWEDate smalldatetime

select @.allnotes=''
select @.currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date
select @.beginWEDate = DATEADD(d, - 28, @.currentWEDate)--get the last 4 weeks

declare CursorIncident CURSOR
LOCAL FOR SELECT iIncidentId, iWorkNoteID, iSeqNum, dtEntryDate,worknoteall FROM dbo.rpt_weekly_prospect_status_vw
where iIncidentId=@.iIncidentID order by iWorkNoteId

OPEN CursorIncident
FETCH NEXT FROM CursorIncident INTO @.incidentId,@.worknoteid,@.seqnum,@.dtEntryDate,@.work noteall

--store 1st record of cursor
select @.worknotesaveid =@.worknoteid
WHILE (@.@.FETCH_STATUS=0)
BEGIN
if @.dtEntryDate >=@.beginWEDate AND @.dtEntryDate <= @.currentWEDate
Begin
if @.worknotesaveid <> @.worknoteid
Begin
Select @.allnotes = @.allnotes + @.dtEntryDate + @.worknoteall
End
else
BEgin
select @.allnotes = @.allnotes + @.worknoteall
End

select @.worknotesaveid = @.worknoteid --save next worknoteId
End
else
Begin
select @.allnotes=''
End
FETCH NEXT FROM CursorIncident INTO @.incidentId,@.worknoteid,@.seqnum,@.dtEntryDate,@.work noteall
END --WHILE (@.@.FETCH_STATUS=0)

CLOSE CursorIncident
DEALLOCATE CursorIncident

return @.allnotes
END

----
Function not working right. I appreciate any help.
Thanks in advance.There is a problem with you logic. If there is more than one dtEntryDate value for an incidentId within the past four weeks, which date should your function return? It is only able to return a single date value.

Think about it.

In the meantime, here is a bit of wisdom: If you find yourself using a cursor, you are either an SQL guru or you are doing something wrong.

This is the type of logic you want to use (returns only notes, because unclear of date issue above):CREATE FUNCTION dbo.getIncidentNotesRev(@.iIncidentID int)
RETURNS varchar(8000)
AS
BEGIN
declare @.allnotes varchar(8000)
declare @.currentWEDate smalldatetime
declare @.beginWEDate smalldatetime

select @.currentWEDate=currentweekEndDate from csCurrentweekEndDate --get the current week end date
select @.beginWEDate = DATEADD(d, - 28, @.currentWEDate)--get the last 4 weeks

select @.AllNotes = Coalesce(@.AllNotes, '') + worknoteall
from dbo.rpt_weekly_prospect_status_vw
where iIncidentId=@.iIncidentID
and dtEntryDate between @.beginWEDate and @.currentWEDate
order by iWorkNoteId

return @.AllNotes
End|||Hi,
In my example above, it should return only the date for note4 and note5 (they are the same) since they have the same iWorkNoteId. In my code I'm checking that; otherwise, if the iWorkNoteId is different, then the date for that note is added to the @.allnotes.

I originally have a similar code as yours, but then I need to modify it because the users need the last 4 weeks notes in which the date is also a part of the allnotes.

I ran your code but it's alwyas giving NUll, although there is data. I think the date comparison is not working.

Thanks.|||Fine...what you supplied with the sample data and expected results is perfect...I just decided not to go through your code...

But this does what you want

USE Northwind
GO

CREATE TABLE myTable99(Incident int, col2 int, col3 int, col4 datetime, note char(10))
GO

INSERT INTO myTable99(Incident, col2, col3, col4, note)
SELECT 187189, 3440, 1, '2006-04-24', 'note1' UNION ALL
SELECT 187189, 3545, 1, '2006-06-22', 'note2' UNION ALL
SELECT 187189, 3547, 1, '2006-06-22', 'note3' UNION ALL
SELECT 187189, 3653, 1, '2006-08-10', 'note4' UNION ALL
SELECT 187189, 3653, 2, '2006-08-10', 'note5'
GO

SELECT * FROM myTable99
GO

SELECT *
FROM myTable99 o
WHERE EXISTS (
SELECT Incident
FROM myTable99 i
WHERE i.Incident = o.Incident
GROUP BY Incident
HAVING o.Col4 = MAX(i.Col4))
GO

DROP TABLE myTable99
GO

Function update

A gentleman from here helped me to write a function to convert oracle
date to a MS Sql date.
Here is the link
http://groups.google.com/group/micr...r />
8Jm9ADFUU
Yr6IuaVp8r6Fxhin3IJmS3764Q
The previous function you wrote
CREATE FUNCTION dbo.to_date
(@.dt VARCHAR(50), @.dt_format VARCHAR(50))
RETURNS DATETIME
AS
BEGIN
RETURN
CONVERT(DATETIME,
CONVERT(CHAR(12),
CAST(
SUBSTRING(@.dt,PATINDEX('%YYYY%',@.dt_form
at),4)+
SUBSTRING(@.dt,PATINDEX('%MM%',@.dt_format
),2)+
SUBSTRING(@.dt,PATINDEX('%DD%',@.dt_format
),2) AS DATETIME)
,0)+
SUBSTRING(@.dt,PATINDEX('%HH%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%MI%',@.dt_format
),2)+':'+
SUBSTRING(@.dt,PATINDEX('%SS%',@.dt_format
),2)+' '+
SUBSTRING(@.dt,PATINDEX('%AM%',@.dt_format
),2),9)
END
works for TO_Date( '12/27/1996 03:48:26 PM', 'MM/DD/YYYY HH:MI:SS AM')
I would like to modify your function for the data below
to_date('05/31/2000 10:17:22', 'MM/DD/YYYY HH24:MI:SS')
Can you help me to modify the function to work with the new format.
Thanks.Thanks. I figured it out. simple solution to just Return
convert(datetime,@.dt) without all the complexity.

Function to return week number

Does anyone now how I can create a SQL function to return a w
number for any date with the following guidlines?
-Ws begin on a Thursday and end on a Wednesday.
-1st January is always in w 1.
-W 1 for this year would go from 30/12/2004 to 05/01/2005
I have been going round in circles trying to crack this one. I would
be very grateful if anyone has any ideas.Mark
Use DateFirst and DatePart
an Example in T-SQL:
Set DateFirst 4
Declare @.D DateTime Set @.D = '20050101'
Select DatePart(wk, @.d)
To make a function:
-- **********************************
Create Functiondbo.WNumber
(@.D DateTime,
@.FDOW TinyInt) -- The day of w yo want to be first Mon = 1; Sunday= 7
Returns TinyInt
As
Begin
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d ) +
@.FDOW + 4) / 7
Return @.WkNo
End
-- ---
Use it like this:
Select dbo.WNumber('20050106',4)
"Mark Powell" wrote:

> Does anyone now how I can create a SQL function to return a w
> number for any date with the following guidlines?
> -Ws begin on a Thursday and end on a Wednesday.
> -1st January is always in w 1.
> -W 1 for this year would go from 30/12/2004 to 05/01/2005
> I have been going round in circles trying to crack this one. I would
> be very grateful if anyone has any ideas.
>|||The you tried the built-in DATEPART after setting appropriate SET DATEFIRST?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Mark Powell" <mark@.muddyboots.com> wrote in message
news:f10f176.0503300059.5537cf67@.posting.google.com...
> Does anyone now how I can create a SQL function to return a w
> number for any date with the following guidlines?
> -Ws begin on a Thursday and end on a Wednesday.
> -1st January is always in w 1.
> -W 1 for this year would go from 30/12/2004 to 05/01/2005
> I have been going round in circles trying to crack this one. I would
> be very grateful if anyone has any ideas.|||Thanks - that's much better than anything I could come up with. The
only problem with this function is that 30/12/04 and 31/12/04 are
returning w 53 and I want them to return w 1.|||That IS counterintuitive, that the last days of 2004 should be considered as
being in the first w of 2005, But if you want it that way, modify
function so that if value calculates to 53, it returns 1 instead
Alter Functiondbo.WNumber
(@.D DateTime,
@.FDOW TinyInt) -- The day of w yo want to be first Mon = 1; Sunday= 7
Returns TinyInt
As
Begin
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d ) +
@.FDOW + 4) / 7
Return Case @.WkNo When 53
Then 1 Else @.WkNo End
End
"mark@.muddyboots.com" wrote:

> Thanks - that's much better than anything I could come up with. The
> only problem with this function is that 30/12/04 and 31/12/04 are
> returning w 53 and I want them to return w 1.
>|||It doesn't make sense to me either, exept that it keeps the same w
number across the year end. It is how our client wants it, so it's the
way it needs to be done.
The next problem I have found is that the w number does not return
the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
imagine the +4 in your procedure should be a variable based on the
year, but I am not sure what it does?
Thanks for your help.
Mark|||Mark,
This was much harder than I thought at first, but this is it... Give it a
shot...
ALTER FUNCTION dbo.WNumber
(@.D DateTime, @.FDOW TinyInt)
Returns TinyInt
As
Begin
Declare @.FDOY Smallint, @.Shft Smallint, @.Yr SmallInt
Set @.Yr = Year(@.D)
Set @.FDOY = DatePart(dw, Str(@.Yr,4,0) + '0101') +
(@.@.DateFirst%7) - 1
Set @.Shft = (@.FDOY - @.FDOW + 7) % 7
Declare @.WkNo TinyInt
Set @.WkNo = (DatePart(dy, @.d) + @.Shft + 6) / 7
-- Now adjust for last partial w
Return Case When @.WkNo < 53 Then @.WkNo
When @.WkNo > 53 Or @.Shft < 5 Then 1
When @.Shft = 6 Then 53
-- Leap Year Consideration
When DatePart(dy, Str(@.Yr,4,0) + '1231') = 365
Then 1
Else 53 End
End
-- ****************************************
***************
And here is the code to test it...
Declare @.Y SmallInt Set @.Y = 2000
Set NoCOunt On
Declare @.D DateTime
Declare @.FDOW TinyInt Set @.FDOW = 4
Declare @.DP TinyInt,@.DF TinyInt,
@.DY SmallInt, @.I TInyInt
Declare @.DTs Table(DT TinyInt)
Set @.I = 0
While @.I < 7 Begin
Set @.I = @.I + 1
Insert @.DTs(DT) Values(@.I)
End
Print 'Year Day Date WNo Day Date WNo '
While @.Y < 2010 Begin
--Set @.D = STR(@.Y, 4,0) + '0101'
Set @.DF = @.@.DateFirst
--Set @.DP = datepart(wday, @.D)
--Set @.DY = datepart(dy, @.D)
Select @.Y,
Left(DateName(dw, Str(@.Y, 4,0) + '01' + Replace(Str(DT, 2,0),' ', '0')),2)
+
' ' + Str(@.Y, 4,0) +'01' + Left(Replace(Str(DT, 2,0),' ', '0'),8) +
' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'01' + Left(Replace(Str(DT, 2,0),'
', '0'),2), 4) as Char(2)),
Left(DateName(dw, Str(@.Y, 4,0) + '12' + Replace(Str(DT+24, 2,0),' ',
'0')),2) +
' ' + Str(@.Y, 4,0) +'12' + Left(Replace(Str(DT+24, 2,0),' ', '0'),8)
+ ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'12' + Left(Replace(Str(DT+24,
2,0),' ', '0'),2), 4)as Char(2))
From @.DTs
Set @.Y = @.Y + 1
End
"mark@.muddyboots.com" wrote:

> It doesn't make sense to me either, exept that it keeps the same w
> number across the year end. It is how our client wants it, so it's the
> way it needs to be done.
> The next problem I have found is that the w number does not return
> the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
> imagine the +4 in your procedure should be a variable based on the
> year, but I am not sure what it does?
> Thanks for your help.
> Mark
>|||Sorry , error in test script...
Use the following to test UDF In Prev Post
-- ****************************************
********
Set NoCount On
Declare @.D DateTime
Declare @.FDOW TinyInt Set @.FDOW = 2
Declare @.Y SmallInt Set @.Y = 2000
-- --
Declare @.I TInyInt Set @.I = 0
Declare @.DTs Table(DT TinyInt)
While @.I < 7 Begin
Set @.I = @.I + 1
Insert @.DTs(DT) Values(@.I)
End
-- ---
Print 'Year Day Date WNo Day Date WNo '
While @.Y < 2010 Begin
Select @.Y,
Left(DateName(dw, Str(@.Y, 4,0) + '01' +
Replace(Str(DT, 2,0),' ', '0')),2) +
' ' + Str(@.Y, 4,0) +'01' +
Left(Replace(Str(DT, 2,0),' ', '0'),8) + ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'01' +
Left(Replace(Str(DT, 2,0),' ', '0'),2), @.FDOW) as Char(2)),
Left(DateName(dw, Str(@.Y, 4,0) + '12' +
Replace(Str(DT+24, 2,0),' ', '0')),2) +
' ' + Str(@.Y, 4,0) +'12' +
Left(Replace(Str(DT+24, 2,0),' ', '0'),8) + ' ' +
Cast(dbo.WNumber(Str(@.Y, 4,0) +'12' +
Left(Replace(Str(DT+24, 2,0),' ', '0'),2), @.FDOW)as Char(2))
From @.DTs
Set @.Y = @.Y + 1
End
-- ****************************************
********
"mark@.muddyboots.com" wrote:

> It doesn't make sense to me either, exept that it keeps the same w
> number across the year end. It is how our client wants it, so it's the
> way it needs to be done.
> The next problem I have found is that the w number does not return
> the correct value if I use a date from 2004 or 2006 (i.e. not 2005). I
> imagine the +4 in your procedure should be a variable based on the
> year, but I am not sure what it does?
> Thanks for your help.
> Mark
>

Function to convert a 'date range' to table of starting and ending

I have a table that contains two fields: effectiveFrom, effectiveTo.
The time elapsed between these day could be greater than one year.
I would like to develop a UDF that would convert these dates into a
table of values. For example, if the dates are 7/1/03 and 4/1/06 the
resulting table would look like:
startingDate endingDate
07/01/03 12/31/03
01/01/04 12/31/04
01/01/05 12/31/05
01/01/06 04/01/06
I am confident that I can do this in a stored procedure, but I'm having
difficulty in a table-set UDF.
Suggestions appreciated.
Craig BuchananAdi-
Not exactly what I was looking for, but useful in other situations.
Thanks a lot for your response.
Craig
Adi wrote:
> You can do it this way:
> create function ShowDates (@.StartDate smalldatetime, @.EndDate
> smalldatetime)
> returns @.DatesTable table (DateCol smalldatetime)
> as
> begin
> while @.startDate <= @.EndDate
> BEGIN
> insert into @.DatesTable (DateCol) values (@.StartDate)
> set @.StartDate = dateadd(dd,1, @.StartDate)
> END
> return
> END
> If you are using SQL Server 2005, then you can use recursive CTE to do
> the same thing
>|||Razvan-
Perfect! Thanks a lot.
Craig Buchanan
Razvan Socol wrote:
> Hello, Craig
> You can use this function (after you give it a better name):
> CREATE FUNCTION YourFunctionName(
> @.EffectiveFrom datetime,
> @.EffectiveTo datetime
> )
> RETURNS @.Result TABLE (
> StartingDate datetime NOT NULL PRIMARY KEY,
> EndingDate datetime NOT NULL UNIQUE,
> CHECK (startingDate<=endingDate)
> ) AS BEGIN
> WHILE YEAR(@.EffectiveFrom)<YEAR(@.EffectiveTo) BEGIN
> DECLARE @.EndOfYear datetime
> SET @.EndOfYear=DATEADD(year,DATEDIFF(year,0,
@.EffectiveFrom)+1,0)-1
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EndOfYear)
> SET @.EffectiveFrom=@.EndOfYear+1
> END
> IF @.EffectiveFrom<=@.EffectiveTo BEGIN
> INSERT INTO @.Result VALUES (@.EffectiveFrom, @.EffectiveTo)
> END
> RETURN
> END
> Razvan
>|||Uri-
Thanks for the reply. Not exactly what I needed, but I can use this in
another situation.
Thanks,
Craig
Uri Dimant wrote:
> Try this one
> CREATE FUNCTION fn_dates(@.from AS DATETIME, @.to AS DATETIME)
> RETURNS @.Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
> AS
> BEGIN
> DECLARE @.rc AS INT
> SET @.rc = 1
> INSERT INTO @.Dates VALUES(@.from)
> WHILE @.from + @.rc * 2 - 1 <= @.to
> BEGIN
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> SET @.rc = @.rc * 2
> END
> INSERT INTO @.Dates
> SELECT dt + @.rc FROM @.Dates
> WHERE dt + @.rc <= @.to
> RETURN
> END
> GO
> SELECT dt FROM fn_dates('20060101', '20060130')
>
> "Craig Buchanan" <user@.example.net> wrote in message
> news:OAAuwYKMGHA.2992@.tk2msftngp13.phx.gbl...
>

Function Return First Date of Week of Year

Hi
I've been using this function to return the date og the 'w of year' - but
until 2004-53 - > it calculates wrong.
W 53-2004 -> 2004/12/27 OK
W 01-2005 -> 2005/01/01 Wrong
W 02-2005 -> 2005/01/02 Wrong
W 03-2005 -> 2005/01/10 OK
btw: I have the Set DateFirst 1 ( Monday )
Can anyone Help?
And here the function
create FUNCTION WEEK_TO_DATE(@.w int, @.year int)
RETURNS datetime
AS
BEGIN
declare @.date datetime
set @.date = convert(datetime, '1/1/' + cast(@.year as char(4)) )
while datepart(wk, @.date) <> @.w
set @.date = dateadd(dd, 1, @.date)
RETURN @.date
END
Kind Regards
J. E. JensenTake a look at the ISOWEEK function under the CREATE FUNCTION topic in
Books Online.
David Portas
SQL Server MVP
--

Function results

I am trying to convert a string value, from a parameter field, into a date t
o
insert into a SQL2005 db smalldatetime field. The function returns a bit
value, which I test by using the isdate function, and if true convert this t
o
a smalldatetime value, else I set to null.
I get error that "Syntax incorrect at @.test".
What is incorrect about what I am trying to do'
...
DECLARE @.RecNumber bigint,
@.AGM1 smalldatetime,
@.AGM2 smalldatetime,
@.SDt smalldatetime,
@.Test bit
SET NOCOUNT ON;
@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
if @.test = 1 then
@.AGM1 = convert(varchar(10),@.Adv_Met1,101)
else
@.AGM1 = null
end if
Any help is appreciated.@.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
Yes, there is! This is not VBScript, you need to use SET or SELECT
SET @.test = dbo.fn_testdate(@.Adv_Met1);
SELECT @.test = dbo.fn_testdate(@.Adv_Met1);
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message
news:ABD935E6-40A8-4F73-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
>to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
> to
> a smalldatetime value, else I set to null.
> I get error that "Syntax incorrect at @.test".
> What is incorrect about what I am trying to do'
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
> Any help is appreciated.|||> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
Also a bunch of problems here. I'm going to assume you come from a VB or
VBScript background?
IF @.Test = 1 -- there is no THEN with IF in T-SQL
SET @.AGM1 = ... -- again, need SET or SELECT
ELSE -- this line was okay!
SET @.AGM1 = NULL -- you don't really need to do this though, it is
already NULL!
-- END IF -- there is no such thing as END IF in T-SQL
A more structured way, and my prefered way, if you really needed the ELSE
clause, would be:
IF @.Test = 1
BEGIN
SET @.AGM1 = ...
END
ELSE
BEGIN
SET @.AGM1 = NULL
END
While it makes the code more verbose, I see two benefits:
(1) you really know where the boundaries of the conditional are, unless your
indenting / coding conventions are so bad that a BEGIN/END struct don't
help.
(2) if you need to add more statements to the result of a conditional,
you're already set. I find the lazier syntax leads to unexpected behavior,
not sure if people coming from lisp or cobol expect indentation and white
space to mean more than the code itself, but they can't seem to understand
why the following always prints 'bar' no matter what the value of @.foo:
DECLARE @.foo TINYINT;
SET @.foo = 2;
IF @.foo = 1
PRINT 'foo';
PRINT 'bar'; -- they expect this to run
-- with BEGIN / END it would be much more intuitive
IF @.foo = 2
PRINT 'blat';|||>> I am trying to convert a string value, from a parameter field, into a date to insert int
o a SQL2005 db smalldatetime field [sic] The function returns a bit [sic] value, which I te
st by using the isdate function, and if true [sic] convert this to a smal
ldatetime value, else I set to null. <<
Your whole approach is wrong. Columns are not fields -- nothing alike.
Formatting of data is done in the front end and not in the database.
Good SQL do not write with BIT data since it is too low-level, avoid
BIGINT since it is absurdly large and avoid the proprietary
SMALLDATETIME type. SQL has no BOOLEAN data types. The ISO-11179
Standard prohibits that silly "fn-" affix on name. The use of numbers
in a data element name are a sign of repeating groups and 1NF
violations.
And you are full of syntax and conceptual errors in your effort to
mimic a non-SQL procedural language. Why did you use CONVERT()? To
get a string!! Temporal datatypes do not exist in the 3GL language you
are trying to mimci and you do not understand them. That is too
abstract, so you nee to see a picture.
Why did you use IF-THEN_ELSE constructs? Because you do not understand
CASE expressions. You do not understand declarative programming, so
you avoid it with the proprietary 20+ year old T-SQL 4GL instead.
If you must do this kind of kludging, try something like this:
SET @.agm = CAST (@.adv_met1 AS DATETIME);
Let the CAST() catch the errors then handle them. I also hope you know
about ISO-8601 formats for temporal data.|||It seems like your functions 'should' be doing the test and the conversion a
nd handing you back the date value you desire. Something like this:
CREATE FUNCTION dbo.fn_testdate
( @.StringDateToConvert as varchar(25) )
RETURNS datetime
AS
IF isdate( @.StringDateToConvert )
RETURN convert( datetime, @.StringDateToConvert, 101 )
ELSE
RETURN NULL
GO
This eliminates the entire 'If @.Test' block.
Check in Books on Line for the proper 'Style' value (the 101 above.) Look up
CAST and CONVERT and note the 'Style' indicators.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"sparty1022" <sparty1022@.discussions.microsoft.com> wrote in message news:ABD935E6-40A8-4F7
3-9B12-D8B458D824F8@.microsoft.com...
>I am trying to convert a string value, from a parameter field, into a date
to
> insert into a SQL2005 db smalldatetime field. The function returns a bit
> value, which I test by using the isdate function, and if true convert this
to
> a smalldatetime value, else I set to null.
>
> I get error that "Syntax incorrect at @.test".
>
> What is incorrect about what I am trying to do'
>
> ...
> DECLARE @.RecNumber bigint,
> @.AGM1 smalldatetime,
> @.AGM2 smalldatetime,
> @.SDt smalldatetime,
> @.Test bit
> SET NOCOUNT ON;
>
> @.test = dbo.fn_testdate(@.Adv_Met1) ***is something wrong here?
>
> if @.test = 1 then
> @.AGM1 = convert(varchar(10),@.Adv_Met1,101)
> else
> @.AGM1 = null
> end if
>
> Any help is appreciated.|||>> A more structured way, and my prefered way, if you really needed the ELSE
clause, would be: <<
A more **declarative** way and my prefered way, if you really needed
the ELSE
clause, would be:
SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
Why encourage newbies to mimic a 3GL in SQL when you do not have to?|||Thank you
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also a bunch of problems here. I'm going to assume you come from a VB or
> VBScript background?
> IF @.Test = 1 -- there is no THEN with IF in T-SQL
> SET @.AGM1 = ... -- again, need SET or SELECT
> ELSE -- this line was okay!
> SET @.AGM1 = NULL -- you don't really need to do this though, it is
> already NULL!
> -- END IF -- there is no such thing as END IF in T-SQL
> A more structured way, and my prefered way, if you really needed the ELSE
> clause, would be:
> IF @.Test = 1
> BEGIN
> SET @.AGM1 = ...
> END
> ELSE
> BEGIN
> SET @.AGM1 = NULL
> END
> While it makes the code more verbose, I see two benefits:
> (1) you really know where the boundaries of the conditional are, unless yo
ur
> indenting / coding conventions are so bad that a BEGIN/END struct don't
> help.
> (2) if you need to add more statements to the result of a conditional,
> you're already set. I find the lazier syntax leads to unexpected behavior
,
> not sure if people coming from lisp or cobol expect indentation and white
> space to mean more than the code itself, but they can't seem to understand
> why the following always prints 'bar' no matter what the value of @.foo:
> DECLARE @.foo TINYINT;
> SET @.foo = 2;
> IF @.foo = 1
> PRINT 'foo';
> PRINT 'bar'; -- they expect this to run
> -- with BEGIN / END it would be much more intuitive
> IF @.foo = 2
> PRINT 'blat';
>
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1151678883.305430.63220@.p79g2000cwp.googlegroups.com...
> clause, would be: <<
> A more **declarative** way and my prefered way, if you really needed
> the ELSE
> clause, would be:
> SET @.AGM1 = CASE WHEN @.test = 1 THEN .. ELSE NULL END;
> Why encourage newbies to mimic a 3GL in SQL when you do not have to?
Because unlike you, I don't expect them to become SQL gurus overnight. :-)
Let's let them get the syntax figured out, then the optimal way to do
things. Much of this they'll find out on their own, because as you and I
both know, the "experts" don't always agree on the best way to do something.
A

Function or SP for date conversion

i have a field that date stored in it, format of this date is somethin like timestamp. for example today(9Nov2005) saved as 38665.
how can i convert this value to a normal date format?
any function or sp?i have a field that date stored in it, format of this date is somethin like timestamp. for example today(9Nov2005) saved as 38665.
how can i convert this value to a normal date format?
any function or sp?

SELECT CAST ( 38665 as datetime)

Function for "yesterday"

Can anyone help with the function for reporting the day before current date? I'm using Crystal 10. Being new to Crystal I can't seem to get the formula to work that I have. I want the report that I have built to display the day before's data. Thanks very much in advance!!DateAdd ('d',-1 ,CurrentDate )|||The database field that I pull the date from is the {Trips.tdate}, therefor it would be this?

{Trips.tdate} dateadd('d',-1 ,CurrentDate )|||Just go to REPORT -> SELECT EXPERT and in that write:

{Trips.tdate} = dateadd('d',-1 ,CurrentDate )|||I am getting an error stating "a string is required". It follows the =|||in trips, tdate is string|||This is what I have
{Trips.tdate} = dateadd('d',-1 ,CurrentDate )

I get a "number amount, currency amount, boolean or sting is expected here" Here being between the = and dateadd

I know now why I became a paramedic!|||You are comparing a string (which happens to look like a date to you) with a date.
In the field explorer, right click, show field type to check - should say string.
Convert your trips.tdate string to a date.
Alternatively, make sure you store dates as dates, not strings :)

So something like:
CDate({trip.tdate}) = dateadd('d',-1 ,CurrentDate )

To avoid having to do this throughout a report (if you use trip.tdate a lot) you can create a formula to return the string as a date and use the formula instead of the database field.
Still better to store it properly in the first place though!|||You are correct in the fact it is a string. I have no way of changing how the software captures the date. I pasted the following

CDate({trip.tdate}) = dateadd('d',-1 ,CurrentDate )

It now tells me that trip.tdate is an unknown field.|||You weren't supposed to blindly copy my typo! It's Trips (with an 'S') not trip.|||I got it, I got it!!!! Thanks everyone!!!!!!|||Thanks JaganEllis!

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
Fred
Hi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

Function default parameter

I am creating a udh function that requires to know the current date.
Since GetDate() can't be used inside a UDF I thought
I would use a date parameter with a default value of GetDate()
i.e.:
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate()
) Returns
or
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = (Select GetDate())
) Returns
These do not even compile
However
Create function dbo.ufn_GetWeekAreaAvailability(
@.WeekId int,
@.AreaId char(1),
@.Now DateTime = GetDate
) Returns
Does but when I call it it
Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
It tells me that string cannot be converted to a date
Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
Syntax error converting datetime from character string.
Any Idea...
Thanks
--
FredHi Fred,
You can't use a function as the default value for a parameter, only
literals. You can use a view that just returns the value of GETDATE() for
example:
CREATE VIEW vw_getdate
AS
SELECT GETDATE() AS getdate
but the view will be accessed each time the function is executed, which
means that you can end up with having different values for each execution.
The safest is not to use a default value and just pass in the value for
GETDATE().
--
Jacco Schalkwijk
SQL Server MVP
"Fred" <Fred@.discussions.microsoft.com> wrote in message
news:7315A12D-37ED-4F5F-8855-2DFE1F2153E6@.microsoft.com...
>I am creating a udh function that requires to know the current date.
> Since GetDate() can't be used inside a UDF I thought
> I would use a date parameter with a default value of GetDate()
> i.e.:
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate()
> ) Returns
> or
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = (Select GetDate())
> ) Returns
> These do not even compile
> However
> Create function dbo.ufn_GetWeekAreaAvailability(
> @.WeekId int,
> @.AreaId char(1),
> @.Now DateTime = GetDate
> ) Returns
> Does but when I call it it
> Select * from dbo.ufn_GetWeekAreaAvailability(1,'1',Default)
> It tells me that string cannot be converted to a date
> Server: Msg 241, Level 16, State 1, Procedure ufn_Test, Line 0
> Syntax error converting datetime from character string.
> Any Idea...
>
> Thanks
>
>
> --
> Fred

Function Date

A have initial date 20/04/2005
and a final date 25/09/2005. id like a function that return how many days
there are for each month until the final date.
Ex: 04/2005 - 10 days
05/2005 - 31 days
_
_
09/2005 - 25 days.
Is that possible, or i have to create some function that do it' Id like
some already done!
ThanksIf you don't care about wends / holidays,
SELECT DATEDIFF(DAY, '20050420', '20050925')
If you need to incorporate/ignore wends and holidays, see
http://www.aspfaq.com/2519
(I also *strongly* suggest avoiding usage and assumptions based on ambiguous
date formats like dd/mm/yyyy.)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Daniel Caetano" <dcaetano@.ig.com.br> wrote in message
news:eqFrlesPFHA.244@.TK2MSFTNGP12.phx.gbl...
> A have initial date 20/04/2005
> and a final date 25/09/2005. id like a function that return how many days
> there are for each month until the final date.
> Ex: 04/2005 - 10 days
> 05/2005 - 31 days
> _
> _
> 09/2005 - 25 days.
> Is that possible, or i have to create some function that do it' Id like
> some already done!
> Thanks
>|||Try This:
Create FUNCTION dbo.MonthDays (@.StartDT Datetime, @.EndDT DateTime)
RETURNS @.Dates Table (DT DateTime, DaysInMonth SmallInt)
AS
BEGIN
Declare @.DT DateTime, @.EDT DateTime
Select @.DT = Convert(VarChar(6), @.StartDT, 112) + '01',
@.EDT = DateAdd(month, 1, @.DT)
While @.DT < @.EndDT Begin
Insert @.Dates(DT, DaysInMonth)
Values (@.DT, DateDiff(day,
Case When @.StartDT > @.DT Then @.StartDT Else @.DT END,
Case When @.EDT < @.EndDT Then @.EDT Else @.EndDT END))
Set @.DT = @.EDT
Set @.EDT = DateAdd(month, 1, @.DT)
End
RETURN
END|||create procedure GetDayNumbers(
@.StartDate datetime,@.EndDate datetime
)
as
declare @.StartDiff smallint,@.EndDiff smallint,@.Diff int
declare @.Test table (StartDate datetime,EndDate datetime)
select
@.StartDiff =
datediff(d,@.StartDate,left(convert(varch
ar(6),dateadd(m,1,@.StartDate),112),6
)+'01')-1;
select
@.EndDiff =
datediff(d,left(convert(varchar,@.EndDate
,112),6)+'01',@.EndDate)+1;
select
@.Diff =
datediff(m,@.StartDate,@.EndDate)+1
set rowcount @.Diff
select i=identity(int,1,1)
into Months
from sysobjects,syscolumns
insert @.Test
select @.StartDate StartDate,@.EndDate EndDate
select
Month=month(dateadd(m,i-1,StartDate)),
Year=year(dateadd(m,i-1,StartDate)),
NumOfDays=
case i
when 1 then @.StartDiff
when datediff(m,StartDate,EndDate)+1 then @.EndDiff
else datediff(d,dateadd(m,i-1,StartDate),dateadd(m,i,StartDate))
end
from @.Test join Months
on (datediff(m,StartDate,EndDate)+1)>=Months.i
drop table Months;
set rowcount 0
Regards,
Marko Simic
"Daniel Caetano" wrote:

> A have initial date 20/04/2005
> and a final date 25/09/2005. i′d like a function that return how many day
s
> there are for each month until the final date.
> Ex: 04/2005 - 10 days
> 05/2005 - 31 days
> _
> _
> 09/2005 - 25 days.
> Is that possible, or i have to create some function that do it' I′d like
> some already done!
> Thanks
>
>

function

hi..
i have one holiday master where all holiday are defined..now in below function i want to add payoutday to my trade_day.and this date will insert into my funding table.but before that i have to check after adding payoutday to the trade_day, this date not fall in holiday list.if that day will holidaty than i have to take next working day.and if again next day also holiday than i want to skip that day also and take next working day...and again check for holiday so on.
how to do this?

[code]

ALTER FUNCTION dbo.NewPayOutDate(@.id numeric(9))
Returns datetime
as
Begin

DECLARE @.CALDATE int
DECLARE @.ACTDATE DATETIME
Declare @.adddate DATETIME
declare @.moreCALDATE int
Begin
SELECT @.CALDATE=payoutday ,@.ACTDATE=trade_date
FROM pruamc.Tbl_GroupMst GM
INNER JOIN pruamc.Tbl_Redemption_UploadDetails (NOLOCK)
ON
GM.Group_Name = pruamc.Tbl_Redemption_UploadDetails.scheme_group
WHERE pruamc.Tbl_Redemption_UploadDetails.RedemptionUploadMaster_Id=@.id
SET @.adddate = dateadd(dd,@.CALDATE,@.ACTDATE)
SELECT @.moreCALDATE=count(*) from pruamc.Tbl_holidaymst where holiday_date between @.ACTDATE and @.adddate
END
set @.adddate = dateadd(dd,@.moreCALDATE,@.adddate)
RETURN @.adddate
END

[/code]

Moving to the Transact-SQL forum.|||

i m sorry ...thanx for

Moving to the Transact-SQL forum.

but any solution about my problem?

thanx a lot

|||

The following function will be help you to get the next working day...

Code Snippet

Create Function GetWorkingDay(@.Date as DateTime)

Returns DateTime

as

Begin
Declare @.Holiday as DateTime;
Select @.Holiday = holiday_date From Tbl_holidaymst Where holiday_date = @.Date;

If @.Holiday is Null
return @.Date;

return dbo.GetWorkingDay(DateAdd(DD, 1, @.Date));

End

Output:

if 1/1/2007 & 1/2/2007 is holiday

Select dbo.GetWorkingDay('1/1/2007') => 1/3/2007

Select dbo.GetWorkingDay('12/31/2006') => 12/31/2007

|||great thanx a lot..manid