December 11, 2007 at 7:59 am
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
December 11, 2007 at 10:22 am
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
December 20, 2007 at 7:18 pm
Not that it will help you now, but SQL Server 2008 will reportedly have a DATE data type for this very purpose.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
December 21, 2007 at 11:23 am
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
December 21, 2007 at 1:03 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply