Stored Proc Help

Question

Stored Proc Help

Molly Weierbach

SSCommitted

Points: 1530
More actions
December 11, 2007 at 7:59 am

#69220

I have a stored procedure that I need to somehow change the datetime to a date function. I have created a convert function within my procedure but I am passing this through Crystal Reports and It is using the datetime at the declare for the parameter, therefore I am unable to change the data type in Crystal?
CREATE PROCEDURE dbo.gm_NEOCS_Storage
(
@Company varchar(10),
@MrNo varchar(15) = NULL,
@BoxNum varchar(255) = NULL,
@StartDate datetime = Null, - need these to be just a date
@EndDate datetime = Null
)
AS
SET NOCOUNT ON
IF @Company = ''
SET @Company = NULL
IF @MrNo =''
SET @MrNo= NULL
IF @BoxNum =''
SET @BoxNum= NULL
IF @StartDate =''
SET @StartDate= NULL
IF @EndDate =''
SET @EndDate= NULL
IF NOT @MrNo IS NULL
BEGIN
select CLMASTER.PLNAME, CLMASTER.PFNAME, CLMASTER.PMNAME, CONVERT(varchar(100), CLMASTER.DOB, 101), Parent.SSNO, CLMASTER.ACCOUNT, CLMASTER.PID, CLMASTER. COMPANY,
(SELECT vartext FROM cldictionary WHERE SSNO=Parent.SSNO AND SECTION='STORAGE' AND VARCODE='BOXNUM' AND CONVERT(nvarchar(100), REPORTED, 101)=CONVERT(nvarchar(100), Parent.REPORTED, 101)) AS BOXNUM,
(SELECT vartext FROM cldictionary WHERE SSNO=Parent.SSNO AND SECTION='STORAGE' AND VARCODE='DATE2' AND CONVERT(nvarchar(100), REPORTED, 101)=CONVERT(nvarchar(100), Parent.REPORTED, 101)) AS DATE2,
(SELECT vartext FROM cldictionary WHERE SSNO=Parent.SSNO AND SECTION='STORAGE' AND VARCODE='NAME' AND CONVERT(nvarchar(100), REPORTED, 101)=CONVERT(nvarchar(100), Parent.REPORTED, 101)) AS NAME,
(SELECT vartext FROM cldictionary WHERE SSNO=Parent.SSNO AND SECTION='STORAGE' AND VARCODE='STORECO' AND CONVERT(nvarchar(100), REPORTED, 101)=CONVERT(vnarchar(100), Parent.REPORTED, 101)) AS STORECO
FROM
CLDICTIONARY Parent INNER JOIN CLMASTER ON
Parent.SSNO = CLMASTER.SSNO AND Parent.Company = CLMASTER.Company
WHERE Parent.SECTION='STORAGE' AND STATUS='Y' AND Parent.Company = 'AMAIN'
GROUP BY CONVERT(nvarchar(100), Parent.REPORTED, 101), CLMASTER.PLNAME, CLMASTER.PFNAME, CLMASTER.PMNAME, CONVERT(varchar(100), CLMASTER.DOB, 101), Parent.SSNO, CLMASTER.ACCOUNT, CLMASTER.PID,CLMASTER. COMPANY

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 1

There is no "just date" data type in SQL Server. There are datetime, and smalldatetime, both of which store time portions. If you simply assign a value like '1/1/2007' to a datetime or smalldatetime datatype, then the time portion will just be 00:00:00 (midnight). If all of the times on your dates are midnight, then comparisons will work as expected. However, if you have times on your dates, and you are just passing in a full date from Crystal, you can truncate the time portion of your dates like so:

Declare @myDate datetime

Set @myDate = '1/1/2007 12:30 PM'

Select cast(floor(cast(@myDate as float)) as datetime)

The reason that this works is that the time is stored as the fractional component of a floating point number, so taking the floor (truncating the decimal portion) will effectively strip the time off of a date.

I'm sure that there are other better ways to do this, but is has worked nicely for me in the past.

Hope this helps.

-Jeremy

Tim Mitchell SSCoach Points: 15685 More actions · Answer 2

Not that it will help you now, but SQL Server 2008 will reportedly have a DATE data type for this very purpose.

Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Tim Mitchell (12/20/2007)
Not that it will help you now, but SQL Server 2008 will reportedly have a DATE data type for this very purpose.

That is the best news I have heard so far..;)

I am tired of converting all the datetime to just dates....:D

-Roy

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

There's no need to convert to a number and then back to a date if the data is just being used in a WHERE clause... for example... a modification of a small snippet of your code...

(SELECT vartext

FROM cldictionary

WHERE SSNO=Parent.SSNO

AND SECTION='STORAGE'

AND VARCODE='BOXNUM'

AND DATEDIFF(dd,0,REPORTED) = DATEDIFF(dd,0,Parent.REPORTED) AS BOXNUM,

... it'll also run slightly faster.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)