February 19, 2009 at 3:06 am
I have a char variable in a sproc which is passed to SSRS. It accepts a date from which another variable is updated according to the month (for tax purposes). So
@ReportDate 19/01/09 (date input) which then updates
@TaxPeriod 2008/10 (tax period)
When the sproc is executed in Reporting Services, I want to convert the date back to datetime again so the date can be selected from the calendar icon. Code is more or less as follows. Any ideas?
ALTER PROCEDURE procname
@TheDate Char(10)
AS
SET DATEFORMAT dmy
IF EXISTS (SELECT *
FROM Sys.Objects
WHERE Object_id = OBJECT_ID(N'[dbo].[#source_data, #allow_data]')
AND TYPE IN (N'U'))
DROP TABLE [dbo].[#source_data, #allow_data]
DECLARE @PayPeriod Char(7)
-- If Jan, Feb, Mar then get previous year for Apr - Dec of the same calendar year.
IF MONTH(@TheDate) < 4
BEGIN
SET @PayPeriod = CAST(YEAR(@TheDate) - 1 AS CHAR(4)) + '/' + CAST(MONTH(@TheDate) + 9 AS CHAR(2))
END
ELSE
SET @PayPeriod = CAST(YEAR(@TheDate) AS CHAR(4)) + '/' + CAST(MONTH(@TheDate) - 3 AS CHAR(2)) -- changed month from 4 to 2
DECLARE @Date Char(10)
SET @Date = DATEPART(yy,@TheDate) + '-' + DATEPART(mm,@TheDate) + '-' + DATEPART(dd,@TheDate)
SELECT
cols INTO #source_data
FROM
tables
WHERE
conditions
SELECT
more cols INTO #allow_data
FROM
other
WHERE
other conditions
AND col = @PayPeriod
SELECT
#source_data.*,
#allow_data.*
FROM #source_data
LEFT OUTER JOIN #allow_data
ON #source_data."Emp No" = #allow_data."Emp No"
ORDER BY #source_data.Category,
#source_data."Last Name",
#source_data."First Name",
#source_data."Emp No"
ASC
February 19, 2009 at 10:36 am
If I'm understanding your process, you pass in a date in a character string and get back a result set. You'd rather have the date as a datetime so you can use the calendar control.
Why not just change the parameter to a proper datetime? Then you just need to alter the Procedure to accept @TheDate as a Datetime instead of a char(10). It doesn't seem that you need @TheDate as anything other than a datetime based on your code. Even if you do use it in your report somewhere, you can always format it with format properties of the text box you are displaying it it.
Hope that helps a bit,
-Luke.
February 19, 2009 at 10:38 am
additionally, you then create a new @Date variable again as a char(10). Why not use these directly as datetimes? your code will be more efficient if it doesn't have to continually build and convert these text strings to dates and back.
-Luke.
February 20, 2009 at 5:07 am
Thanks for replying.
If I have my date var set as datetime in management studio I get the following error
Msg 8114, Level 16, State 5, Procedure spGdbaSourceExport, Line 0
Error converting data type nvarchar to datetime.
I suppose what I really want is to be able to convert my datevar to datetime in SSRS as I want to utilise the calendar control but I'm not sure where to do this as my dataset merely consists of spGdbaSourceExport
February 20, 2009 at 6:16 am
If I have my date var set as datetime in management studio I get the following error
Msg 8114, Level 16, State 5, Procedure spGdbaSourceExport, Line 0
Error converting data type nvarchar to datetime.
In the code you posted I don't really see anything that would cause that error. Not sure why you'd be converting an nvarchar to a datetime. Any chance you could post some DDL and sample data as well as the rest of the procedure as you just ran it?
-Luke.
February 20, 2009 at 8:16 am
Previously this data was used to populate an excel spreadsheet but I've been asked to produce it in reporting services with a date parameter. No date vars were used previously except @date
Code is more or less as below but I am actually selecting more cols than shown here.
In the code kaekey is a char field with the format eg. 2008/10. I just want a user to be able to execute this is SSRS on any date they choose.
USE HR_Reporting
GO
SET ANSI_NULLS ON
SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spExport]
@TheDate Char(10)
AS
SET DATEFORMAT dmy
IF EXISTS (SELECT *
FROM Sys.Objects
WHERE Object_id = OBJECT_ID(N'[dbo].[#source_data, #allow_data]')
AND TYPE IN (N'U'))
DROP TABLE [dbo].[#source_data, #allow_data]
DECLARE @PayPeriod Char(7)
IF MONTH(@TheDate) < 4
BEGIN
SET @PayPeriod = CAST(YEAR(@TheDate) - 1 AS CHAR(4)) + '/' + CAST(MONTH(@TheDate) + 9 AS CHAR(2))
END
ELSE
SET @PayPeriod = CAST(YEAR(@TheDate) AS CHAR(4)) + '/' + CAST(MONTH(@TheDate) - 3 AS CHAR(2)) -- changed month from 4 to 2
DECLARE @Date Char(10)
SET @Date = DATEPART(yy,@TheDate) + '-' + DATEPART(mm,@TheDate) + '-' + DATEPART(dd,@TheDate)
SELECT
Base.EmpNo,
Person.FirstName,
Person.LastName,
CAST((@TheDate - Person.DateBorn) AS NUMERIC(10,2)) / 365 AS Age,
Employ.eeDateBeg,
Employ.eeDateEnd,
Job.ejSupervisorflxIdeb,
Job.ejSupervisor
INTO #source_data
FROM
Base, Person, Job, eEmploy
WHERE
Base. Flagemp = 'Y'
AND Employ.[Status] = 'Active'
AND Base.Archive <> 'Y'
AND Base.flxId = Person.epflxId
AND Base.flxId = Employ.eeflxId
AND Base.flxId = Job.ejflxId
AND Base.flxId = comp.emflxId
AND (Base.DateBeg <= @Date
AND (Base.DateEnd IS NULL
OR Base.DateEnd >= @Date))
AND (Person.DateBeg <= @Date
AND (Person.DateEnd IS NULL
OR Person.DateEnd >= @Date))
AND (Job.DateBeg <= @Date
AND (Job.DateEnd IS NULL
OR Job.DateEnd >= @Date))
AND (Employ.DateBeg <= @Date
AND (Employ.DateEnd IS NULL
OR Employ.DateEnd >= @Date))
AND Employ.Status <> 'TERMINATED'
AND (comp.DateBeg <= @Date
AND (comp.DateEnd IS NULL
OR comp.DateEnd >= @Date))
AND comp.Kind = 'BASE'
AND ( psId = '114454'
AND eob.JobCode <> 'BS')
SELECT
PayCyclePeriod.KaeKey,
PayDetail.KbkEmpNo,
Allowance.KaaNo ,
Allowance.KaaLongDesc
INTO #allow_data
FROM
PayCyclePeriod,
PayDetail,
AllowanceHistory,
Allowance,
Base
WHERE
1 = 1
AND KaeFlxid=KbkFlxidKae
AND KbkFlxid=KbrFlxidKbk
AND KbrAllowNo=KaaNo
AND KbkFlxid = Flxid
AND KaaDelmkr <> 'Y'
AND KbkttStatus <> 'Z'
AND KaaPSid = KaePSid
AND KaePSid = '114454'
AND KaaNo IN ('006','011','016','017','030','032','057')
AND KaeKey IS NOT NULL
AND Kaekey = @PayPeriod
SET @TheDate = CAST( @TheDate AS Datetime)
SELECT
#source_data.*,
#allow_data.*
FROM #source_data
LEFT OUTER JOIN #allow_data
ON #source_data.EmpNo = #allow_data.EmpNo
ORDER BY #source_data.LastName,
#source_data.FirstName,
#source_data.EmpNo
ASC
February 20, 2009 at 9:01 am
in your code you have a lot of date columns in your where statement...
AND (Base.DateBeg <= @Date
AND (Base.DateEnd IS NULL
OR Base.DateEnd >= @Date))
AND (Person.DateBeg <= @Date
AND (Person.DateEnd IS NULL
OR Person.DateEnd >= @Date))
AND (Job.DateBeg <= @Date
AND (Job.DateEnd IS NULL
OR Job.DateEnd >= @Date))
AND (Employ.DateBeg <= @Date
AND (Employ.DateEnd IS NULL
OR Employ.DateEnd >= @Date))
AND Employ.Status <> 'TERMINATED'
AND (comp.DateBeg <= @Date
AND (comp.DateEnd IS NULL
OR comp.DateEnd >= @Date))
Are these columns datetimes as they should be or are they stored as some sort of crazy character type?
The reason why I ask is that it seems you are doing quite of bit of manipulation to get @theDate into a character datatype and perhaps you don't really need to. SQL Server stores dates and works with dates best when they are stored as datetimes... The front end can display them in whatever format the user requires, but they should always be stored in a date datatype (datetime, smalldatetime, Time or Date (last 2 only in 2008)).
IF they are stored as dates all of this converting back and forth from and to character strings can be avoided, and you don't have to worry about regional settings as well, because January 1, 2009 will be the same no matter if it's displayed as yyyy-mm-dd or yyyy-dd-mm.
Anyhow, I'm assuming that your date columns are actually stored as datetimes... if so, have a look at the code, I commented much of the character logic out that didn't appear to be needed... Have a look and see if it makes sense.
ALTER PROCEDURE [dbo].[spExport]
--pass in the param as a date
@TheDate datetime
AS
--don't need this to work with datetimes
-- SET DATEFORMAT dmy
--No need for this unless you are creating the table multiple times in the same connection.
--Temp Tables are dropped automagically when the connection is dropped so you most likely will never need this
-- IF EXISTS (SELECT *
-- FROM Sys.Objects
-- WHERE Object_id = OBJECT_ID(N'[dbo].[#source_data, #allow_data]')
-- AND TYPE IN (N'U'))
-- DROP TABLE [dbo].[#source_data, #allow_data]
DECLARE @PayPeriod Char(7), @theDate DATETIME
IF MONTH(@TheDate) < 4
BEGIN
SET @PayPeriod = CAST(YEAR(@TheDate) - 1 AS CHAR(4)) + '/' + CAST(MONTH(@TheDate) + 9 AS CHAR(2))
END
ELSE
SET @PayPeriod = CAST(YEAR(@TheDate) AS CHAR(4)) + '/' + CAST(MONTH(@TheDate) - 3 AS CHAR(2)) -- changed month from 4 to 2
--Not needed cause dates are dates to SQL server...
-- DECLARE @Date Char(10)
-- SET @Date = DATEPART(yy,@TheDate) + '-' + DATEPART(mm,@TheDate) + '-' + DATEPART(dd,@TheDate)
SELECT
Base.EmpNo,
Person.FirstName,
Person.LastName,
DATEDIFF(y, Person.DateBorn, @TheDate) AS Age, --unless you really need partial years I'd do it this way faster without the cast
Employ.eeDateBeg,
Employ.eeDateEnd,
Job.ejSupervisorflxIdeb,
Job.ejSupervisor
INTO #source_data
FROM
Base, Person, Job, eEmploy
WHERE
Base. Flagemp = 'Y'
AND Employ.[Status] = 'Active'
AND Base.Archive <> 'Y'
AND Base.flxId = Person.epflxId
AND Base.flxId = Employ.eeflxId
AND Base.flxId = Job.ejflxId
AND Base.flxId = comp.emflxId
AND (Base.DateBeg <= @TheDate
AND (Base.DateEnd IS NULL
OR Base.DateEnd >= @TheDate))
AND (Person.DateBeg <= @TheDate
AND (Person.DateEnd IS NULL
OR Person.DateEnd >= @TheDate))
AND (Job.DateBeg <= @TheDate
AND (Job.DateEnd IS NULL
OR Job.DateEnd >= @TheDate))
AND (Employ.DateBeg <= @TheDate
AND (Employ.DateEnd IS NULL
OR Employ.DateEnd >= @TheDate))
AND Employ.Status <> 'TERMINATED'
AND (comp.DateBeg <= @TheDate
AND (comp.DateEnd IS NULL
OR comp.DateEnd >= @TheDate))
AND comp.Kind = 'BASE'
AND ( psId = '114454'
AND eob.JobCode <> 'BS')
SELECT
PayCyclePeriod.KaeKey,
PayDetail.KbkEmpNo,
Allowance.KaaNo ,
Allowance.KaaLongDesc
INTO #allow_data
FROM
PayCyclePeriod,
PayDetail,
AllowanceHistory,
Allowance,
Base
WHERE
1 = 1
AND KaeFlxid=KbkFlxidKae
AND KbkFlxid=KbrFlxidKbk
AND KbrAllowNo=KaaNo
AND KbkFlxid = Flxid
AND KaaDelmkr <> 'Y'
AND KbkttStatus <> 'Z'
AND KaaPSid = KaePSid
AND KaePSid = '114454'
AND KaaNo IN ('006','011','016','017','030','032','057')
AND KaeKey IS NOT NULL
AND Kaekey = @PayPeriod
-- Not sure why this is here
-- SET @TheDate = CAST( @TheDate AS Datetime)
SELECT
#source_data.*,
#allow_data.*
FROM #source_data
LEFT OUTER JOIN #allow_data
ON #source_data.EmpNo = #allow_data.EmpNo
ORDER BY #source_data.LastName,
#source_data.FirstName,
#source_data.EmpNo ASC
February 23, 2009 at 1:44 am
Hi - thanks very much for looking over my code. Your amended code compiles OK but I still get the same error on execution, when @TheDate is declared as datetime
Msg 8114, Level 16, State 5, Procedure spExport, Line 0
Error converting data type nvarchar to datetime.
Could it be that it's to do with the way SQL Server has been set up on my machine?
February 23, 2009 at 6:10 am
Are you running this from SSRS or directly from QA? If it's from SSRS make sure you change your parameter to the correct datatype as well. If it's from QA can you post the exact syntax you are using?
Thanks,
-Luke.
February 23, 2009 at 7:28 am
I am getting the error message even when I run the very simple script below. Continuousdate is definitely of type datetime in the table. Something must be set up incorrectly somewhere I reckon
use Hr_Reporting
go
alter procedure sptemp
@Thedate datetime
as
select * from CurrentEmployees
where continuousdate < @Thedate
February 23, 2009 at 7:54 am
what is the datatype for the column continuousdate? and again when you call the procedure how are you executing it?
Please post what you are doing to call the procedure... Something like the following?
USE Hr_Reporting
GO
sptemp '2009-02-01'
-Luke.
February 23, 2009 at 8:05 am
Hi - I mentioned the datatype for continuousdate is of type datetime. I am rightclicking on the procedure name in sql server 2005 management studio to execute. If I set @datewhatever to char I don't get an error; when it's set to datetime I do. I test in MS before using SSRS.
February 23, 2009 at 8:21 am
Sorry, I didn't see the bit about continuousdate being a datetime.
When you execute the Procedure in Management studio by right clicking on it, what do you type into the value box for that procedure? I believe this is where the error is occurring, not in the procedure itself. More specifically, I believe the the issue is in the placement of the month and day. I'm not sure what your regional settings and such are for either your database or your client machine, but try using the following format to pass your date in via the dialog box that ssms gives you. If you pass it as 2009-31-01 I receive the error you receive. If I pass it as 2009-01-31 or 2009-jan-31 it works correctly. I prefer the format with the 3 letter month name because it removes all question of which part is the month and which is the day.
yyyy-mmm-dd or 2009-feb-01
-Luke.
February 23, 2009 at 8:54 am
Hi Luke,
I can see what the problem is now!! I have been entering in dates as dd/mm/yy which is the format many of us use in the UK. I have just run the script with yyyy-mm-dd and it worked this time without the error. I thought that by using SET DATEFORMAT dmy this would allow me to enter dates in this format. When I execute this in SSRS is there anything in there I will need to do as users will want to execute the procedure using the calendar control.
February 23, 2009 at 9:12 am
If you set it up as a datetime parameter, it should be passed through to the procedure in the appropriate format. For me it passes them in as 'yyyy-mm-dd 00:00:00.000', but based on the regional settings of a) your database server, b) your report server, c) regional settings within the report and d) regional settings on the client PC your mileage may vary.
using the set dateformat woudl have worked correctly, however it would have needed to be set in the code prior to executing the procedure. All the SSMS gui does is build a SQL statement for you...
For example, my test sproc is called as such by the SSMS GUI.
USE [AdventureWorks]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[uspDateTest]
@TheDate = N'2005-jan-05'
SELECT'Return Value' = @return_value
GO
to use the alternate dateformat, more you would have had to issue it like such...
USE [AdventureWorks]
GO
DECLARE@return_value int
SET DATEFORMAT DMY
EXEC@return_value = [dbo].[uspDateTest]
@TheDate = N'31/01/09'
SELECT'Return Value' = @return_value
GO
The error was being generated as the server attempted to convert the N'' string to a datetime to be passed to the procedure. It just needed some help to figure out what it was getting.
I'd be interested in how you make out making the switch in SSRS. I never really have need to think in international date formats, which is why it didn't initially occur to me you could be passing the date in a way the server wouldn't understand it. I know that dates in SSRS can be tricky based on the above list of locations where regional settings can be set, so just pay attention to exactly what your SSRS instance is passing through to your backend database.
Hope that clears up some of the confusion.
-Luke.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply