August 4, 2009 at 1:20 pm
I get the following error message:
Conversion failed when converting datetime from character string, how do I know which field is that and how do I converted?
I know it is in where clause
Msg 241, Level 16, State 1, Line 1
Here is my select statment:
SELECT vEBase.[EbFirstName] AS [First Name],
vEBase.[EbMiddleName] AS [Middle Name],
vEBase.[EbLastName] AS [Last Name],
vEBase.[EbSocNumber] AS [Social Security Number],
vEBase.[EbPSID] AS [PSID],
vEBase.[EbClock] AS [Clock Number],
vEPerson.[EpDateBeg] AS [Personal Begin Date],
vEPerson.[EpDateEnd] AS [Personal End Date],
vEPerson.[EpStreet1] AS [Address 1],
vEPerson.[EpStreet2] AS [Address 2],
vEPerson.[EpCity] AS [City],
vEPerson.[EpState] AS [State],
vEPerson.[EpZip] AS [Zip Code],
vEPerson.[EpCountry] AS [Country],
vEPerson.[EpDateBorn] AS [Birth Date],
vEPerson.[EpSex] AS [Sex],
vEPerson.[EpHomePhone] AS [Home Phone],
vEBenefit.[EnDateBeg] AS [Benefit Begin Date],
vEBenefit.[EnDateEnd] AS [Benefit End Date],
vEBenefit.[EnEECostProrated] AS [Employee Prorated Cost],
vEBenefit.[EnEECostPerPay] AS [Employee Per Pay Cost],
vEBenefit.[EnEECostAnnual] AS [Employee Annual Cost],
vEBenefit.[EnEEBasis] AS [Employee Basis],
vEBenefit.[EnEERate] AS [Employee Rate],
vEBenefit.[EnTierDescrip] AS [Tier Description],
vBenBase.[BbType] AS [Benefit Type ID],
vBenBase.[BbKind] AS [Benefit Kind ID],
vBenBase.[BbDescrip] AS [Benefit Description],
vBenBase.[BbPrintDescrip] AS [Benefit Printable Description],
vEBenefit.[EnErCostProrated] AS [Employer Prorated Cost],
vEBenefit.[EnErCostPerPay] AS [Employer Per Pay Cost],
vEBenefit.[EnErCostAnnual] AS [Employer Annual Cost],
vEBenefit.[EnErBasis] AS [Employer Basis],
vEBenefit.[EnErRate] AS [Employer Rate],
vEBenefit.[En3PCostProrated] AS [Carrier Prorated Cost],
vEBenefit.[En3PCostPerPay] AS [Carrier Per Pay Cost],
vEBenefit.[En3PCostAnnual] AS [Carrier Annual Cost],
vEBenefit.[En3PBasis] AS [Carrier Basis],
vEBenefit.[En3PRate] AS [Carrier Rate],
vEJob.[EjDateBeg] AS [Job Begin Date],
vEJob.[EjDateEnd] AS [Job End Date],
vJobCode.[JbJobCode] AS [Job Code ID],
vJobCode.[JbDescrip] AS [Job Code Description],
vEJob.[EjCompany] AS [Company ID],
vEJob.[EjDivision] AS [Division ID],
vEJob.[EjDepartment] AS [Department ID],
vEJob.[EjLocation] AS [Location ID],
vEJob.[EjRegion] AS [Region ID],
vEJob.[EjTitle] AS [Title],
vEJob.[EjSupervisor] AS [Supervisor Name],
vJobCode.[JbClass] AS [Job Class],
vJobCode.[JbEEO] AS [EEO Category],
vJobCode.[JbEEOGroup] AS [EEO Group],
vJobCode.[JbFLSA] AS [FLSA Code],
vJobCode.[JbBandID] AS [Job Broadband Code],
vEComp.[EmDateBeg] AS [Compensation Begin Date],
vEComp.[EmDateEnd] AS [Compensation End Date],
vEComp.[EmKind] AS [Kind of Compensation],
vEComp.[EmHourlyRate] AS [Hourly Pay Rate],
vEComp.[EmPerPay] AS [Per-pay Rate],
vEComp.[EmAnnual] AS [Annualized Pay Rate],
vEComp.[EmAnnualProrated] AS [Pro-rated Annual Amount],
vEComp.[EmPayStep] AS [Pay Step ID],
vEComp.[EmPayCycle] AS [Pay Frequency],
vEComp.[EmPayType] AS [Pay Type ID],
vEComp.[EmHoursPerPay] AS [Scheduled Hours Per Pay Period],
vEComp.[EmShiftID] AS [Shift ID],
vEComp.[EmReason] AS [Comp Record Reason],
vEPayrollBase.[PrbRateCode] AS [Hourly or Salary Code],
vEEmploy.[EeDateBeg] AS [Employment Begin Date],
vEEmploy.[EeDateEnd] AS [Employment End Date],
vEEmploy.[EeStatus] AS [Employment Status],
vEEmploy.[EeTermDate] AS [Termination Date],
vEEmploy.[EeTermWhy] AS [Termination Reason],
vEEmploy.[EeCategory] AS [Employment Category],
vEEmploy.[EeDateOriginalHire] AS [Original Hire Date],
vEEmploy.[EeDateLastHire] AS [Recent Hire Date],
vEEmploy.[EeDateSeniorityCalc] AS [Seniority Date],
vEEmploy.[EeDateBenefitCalc] AS [Benefit Calc Date]
FROM ((((((((vEbase
INNER JOIN vEPerson
ON vEbase.EbFlxID = vEPerson.EpFlxIDEb)
LEFT JOIN vEBenefit
ON vEbase.EbFlxID = vEBenefit.EnFlxIDEb)
LEFT JOIN vBenBase
ON vEBenefit.EnFlxIDBb = vBenBase.BbFlxID)
INNER JOIN vEComp
ON vEbase.EbFlxID = vEComp.EmFlxIDEb)
INNER JOIN vEEmploy
ON vEbase.EbFlxID = vEEmploy.EeFlxIDEb)
INNER JOIN vEPayrollBase
ON vEbase.EbFlxID = vEPayrollBase.PrbFlxIDEb)
INNER JOIN vEJob
ON vEbase.EbFlxID = vEJob.EjFlxIDEb)
LEFT JOIN vJobCode
ON vJobCode.JbJobCode = vEJob.EjJobCode)
WHERE
EbFlagEmp = 'Y'
AND (vEPerson.[EpDateBeg]<= '#STARTDATE#' AND (vEPerson.[EpDateEnd] >= '#STARTDATE#' OR vEPerson.[EpDateEnd] IS NULL))
AND (vEBenefit.[EnDateBeg]<= '#STARTDATE#' AND (vEBenefit.[EnDateEnd] >= '#STARTDATE#' OR vEBenefit.[EnDateEnd] IS NULL))
AND (vEComp.[EmDateBeg]<= '#STARTDATE#' AND (vEComp.[EmDateEnd] >= '#STARTDATE#' OR vEComp.[EmDateEnd] IS NULL))
AND vecomp.[emkind] = 'BASE'
AND (vEEmploy.[EeDateBeg]<= '#STARTDATE#' AND (vEEmploy.[EeDateEnd] >= '#STARTDATE#' OR vEEmploy.[EeDateEnd] IS NULL))
AND (vEPayrollBase.[PrbDateBeg]<= '#STARTDATE#' AND (vEPayrollBase.[PrbDateEnd] >= '#STARTDATE#' OR vEPayrollBase.[PrbDateEnd] IS NULL))
AND (vEJob.[EjDateBeg]<= '#STARTDATE#' AND (vEJob.[EjDateEnd] >= '#STARTDATE#' OR vEJob.[EjDateEnd] IS NULL))
August 4, 2009 at 1:25 pm
hopefully #STARTDATE# contains a valid character representation of a date and/or time. IIRC Access requires the date to be bracketed by '#', SQL Server does not.
The probability of survival is inversely proportional to the angle of arrival.
August 4, 2009 at 1:36 pm
like this 'STARTDATE'
August 4, 2009 at 1:47 pm
This is the data:7/9/1998 12:00:00 AM
August 4, 2009 at 1:54 pm
that would be equivalent to '7/9/1998'. It looks okay to me, perhaps a localization setting. I give up, except perhaps try this:
declare @myDate
set @myDate = '7/9/1998 12:00:00 AM'
select @myDate
tell us what happens?
The probability of survival is inversely proportional to the angle of arrival.
August 4, 2009 at 2:15 pm
... I must be missing something, but that query, in T-SQL would look different. First of all, STARTDATE would need to be a valid variable, ie @STARTDATE and then the string '#STARTDATE#' would be replaced by @STARTDATE (without the quotes), and @STARTDATE would contain a valid date string as posted above.
You are running this in a query window or Stored procedure, not in MSACCESS, right?
August 4, 2009 at 2:53 pm
you are correct, fat finger error on my part. I meant to type:
declare @myDate datetime
The probability of survival is inversely proportional to the angle of arrival.
August 5, 2009 at 5:34 am
This is a view and I am trying to run from query analyzer.
August 5, 2009 at 5:40 am
declare @myDate datetime
set @myDate = '7/9/1998 12:00:00 AM'
select @myDate
this works, but '7/9/1998 12:00:00 AM' just an example of my date format, I have different dates in my dates, do I need to set @myDate
August 5, 2009 at 6:27 am
You need to provide full details because something is being left out.
You are running a view? post the create statement.
You are running many dates though query analyzer?
- How are you doing this? Running the query manually with find replace or running manually with a declared variable or batching it somehow?
- What is the select statement you are using to query the view?
August 5, 2009 at 6:46 am
I was giving a new db and multiple quries to run and to check if they running,this query was running from other application and I didn't know what StartDtae means,but now I understand that I would need to run this select statment and hard code the start date, am I right? one of them is this:
SELECT vEBase.[EbFirstName] AS [First Name],
vEBase.[EbMiddleName] AS [Middle Name],
vEBase.[EbLastName] AS [Last Name],
vEBase.[EbSocNumber] AS [Social Security Number],
vEBase.[EbPSID] AS [PSID],
vEBase.[EbClock] AS [Clock Number],
vEPerson.[EpDateBeg] AS [Personal Begin Date],
vEPerson.[EpDateEnd] AS [Personal End Date],
vEPerson.[EpStreet1] AS [Address 1],
vEPerson.[EpStreet2] AS [Address 2],
vEPerson.[EpCity] AS [City],
vEPerson.[EpState] AS [State],
vEPerson.[EpZip] AS [Zip Code],
vEPerson.[EpCountry] AS [Country],
vEPerson.[EpDateBorn] AS [Birth Date],
vEPerson.[EpSex] AS [Sex],
vEPerson.[EpHomePhone] AS [Home Phone],
vEBenefit.[EnDateBeg] AS [Benefit Begin Date],
vEBenefit.[EnDateEnd] AS [Benefit End Date],
vEBenefit.[EnEECostProrated] AS [Employee Prorated Cost],
vEBenefit.[EnEECostPerPay] AS [Employee Per Pay Cost],
vEBenefit.[EnEECostAnnual] AS [Employee Annual Cost],
vEBenefit.[EnEEBasis] AS [Employee Basis],
vEBenefit.[EnEERate] AS [Employee Rate],
vEBenefit.[EnTierDescrip] AS [Tier Description],
vBenBase.[BbType] AS [Benefit Type ID],
vBenBase.[BbKind] AS [Benefit Kind ID],
vBenBase.[BbDescrip] AS [Benefit Description],
vBenBase.[BbPrintDescrip] AS [Benefit Printable Description],
vEBenefit.[EnErCostProrated] AS [Employer Prorated Cost],
vEBenefit.[EnErCostPerPay] AS [Employer Per Pay Cost],
vEBenefit.[EnErCostAnnual] AS [Employer Annual Cost],
vEBenefit.[EnErBasis] AS [Employer Basis],
vEBenefit.[EnErRate] AS [Employer Rate],
vEBenefit.[En3PCostProrated] AS [Carrier Prorated Cost],
vEBenefit.[En3PCostPerPay] AS [Carrier Per Pay Cost],
vEBenefit.[En3PCostAnnual] AS [Carrier Annual Cost],
vEBenefit.[En3PBasis] AS [Carrier Basis],
vEBenefit.[En3PRate] AS [Carrier Rate],
vEJob.[EjDateBeg] AS [Job Begin Date],
vEJob.[EjDateEnd] AS [Job End Date],
vJobCode.[JbJobCode] AS [Job Code ID],
vJobCode.[JbDescrip] AS [Job Code Description],
vEJob.[EjCompany] AS [Company ID],
vEJob.[EjDivision] AS [Division ID],
vEJob.[EjDepartment] AS [Department ID],
vEJob.[EjLocation] AS [Location ID],
vEJob.[EjRegion] AS [Region ID],
vEJob.[EjTitle] AS [Title],
vEJob.[EjSupervisor] AS [Supervisor Name],
vJobCode.[JbClass] AS [Job Class],
vJobCode.[JbEEO] AS [EEO Category],
vJobCode.[JbEEOGroup] AS [EEO Group],
vJobCode.[JbFLSA] AS [FLSA Code],
vJobCode.[JbBandID] AS [Job Broadband Code],
vEComp.[EmDateBeg] AS [Compensation Begin Date],
vEComp.[EmDateEnd] AS [Compensation End Date],
vEComp.[EmKind] AS [Kind of Compensation],
vEComp.[EmHourlyRate] AS [Hourly Pay Rate],
vEComp.[EmPerPay] AS [Per-pay Rate],
vEComp.[EmAnnual] AS [Annualized Pay Rate],
vEComp.[EmAnnualProrated] AS [Pro-rated Annual Amount],
vEComp.[EmPayStep] AS [Pay Step ID],
vEComp.[EmPayCycle] AS [Pay Frequency],
vEComp.[EmPayType] AS [Pay Type ID],
vEComp.[EmHoursPerPay] AS [Scheduled Hours Per Pay Period],
vEComp.[EmShiftID] AS [Shift ID],
vEComp.[EmReason] AS [Comp Record Reason],
vEPayrollBase.[PrbRateCode] AS [Hourly or Salary Code],
vEEmploy.[EeDateBeg] AS [Employment Begin Date],
vEEmploy.[EeDateEnd] AS [Employment End Date],
vEEmploy.[EeStatus] AS [Employment Status],
vEEmploy.[EeTermDate] AS [Termination Date],
vEEmploy.[EeTermWhy] AS [Termination Reason],
vEEmploy.[EeCategory] AS [Employment Category],
vEEmploy.[EeDateOriginalHire] AS [Original Hire Date],
vEEmploy.[EeDateLastHire] AS [Recent Hire Date],
vEEmploy.[EeDateSeniorityCalc] AS [Seniority Date],
vEEmploy.[EeDateBenefitCalc] AS [Benefit Calc Date]
FROM ((((((((vEbase
INNER JOIN vEPerson
ON vEbase.EbFlxID = vEPerson.EpFlxIDEb)
LEFT JOIN vEBenefit
ON vEbase.EbFlxID = vEBenefit.EnFlxIDEb)
LEFT JOIN vBenBase
ON vEBenefit.EnFlxIDBb = vBenBase.BbFlxID)
INNER JOIN vEComp
ON vEbase.EbFlxID = vEComp.EmFlxIDEb)
INNER JOIN vEEmploy
ON vEbase.EbFlxID = vEEmploy.EeFlxIDEb)
INNER JOIN vEPayrollBase
ON vEbase.EbFlxID = vEPayrollBase.PrbFlxIDEb)
INNER JOIN vEJob
ON vEbase.EbFlxID = vEJob.EjFlxIDEb)
LEFT JOIN vJobCode
ON vJobCode.JbJobCode = vEJob.EjJobCode)
WHERE
EbFlagEmp = 'Y'
AND (vEPerson.[EpDateBeg]= '#STARTDATE#' OR vEPerson.[EpDateEnd] IS NULL))
AND (vEBenefit.[EnDateBeg]= '#STARTDATE#' OR vEBenefit.[EnDateEnd] IS NULL))
AND (vEComp.[EmDateBeg]= '#STARTDATE#' OR vEComp.[EmDateEnd] IS NULL))
AND vecomp.[emkind] = 'BASE'
AND (vEEmploy.[EeDateBeg]= '#STARTDATE#' OR vEEmploy.[EeDateEnd] IS NULL))
AND (vEPayrollBase.[PrbDateBeg]= '#STARTDATE#' OR vEPayrollBase.[PrbDateEnd] IS NULL))
AND (vEJob.[EjDateBeg]= '#STARTDATE#' OR vEJob.[EjDateEnd] IS NULL))
August 6, 2009 at 6:07 am
Why isn't this code in a stored procedure instead of a view? If you need to be able to substitute an actual datetime value for each occurrence of #STARTDATE# then the right way to achieve that goal is to use a stored proecedure, as VIEWS cannot be parameterized. If that code is in a view, as is, it will likely never produce a result, and any code relying on that view would get the same error.
Here's how I'd set this up: Create a stored procedure, as follows:
CREATE PROCEDURE dbo.spPROC_NAME_GOES_HERE(@STARTDATE AS DateTime)
AS
BEGIN
SELECT vEBase.[EbFirstName] AS [First Name],
vEBase.[EbMiddleName] AS [Middle Name],
vEBase.[EbLastName] AS [Last Name],
vEBase.[EbSocNumber] AS [Social Security Number],
vEBase.[EbPSID] AS [PSID],
vEBase.[EbClock] AS [Clock Number],
vEPerson.[EpDateBeg] AS [Personal Begin Date],
vEPerson.[EpDateEnd] AS [Personal End Date],
vEPerson.[EpStreet1] AS [Address 1],
vEPerson.[EpStreet2] AS [Address 2],
vEPerson.[EpCity] AS [City],
vEPerson.[EpState] AS [State],
vEPerson.[EpZip] AS [Zip Code],
vEPerson.[EpCountry] AS [Country],
vEPerson.[EpDateBorn] AS [Birth Date],
vEPerson.[EpSex] AS [Sex],
vEPerson.[EpHomePhone] AS [Home Phone],
vEBenefit.[EnDateBeg] AS [Benefit Begin Date],
vEBenefit.[EnDateEnd] AS [Benefit End Date],
vEBenefit.[EnEECostProrated] AS [Employee Prorated Cost],
vEBenefit.[EnEECostPerPay] AS [Employee Per Pay Cost],
vEBenefit.[EnEECostAnnual] AS [Employee Annual Cost],
vEBenefit.[EnEEBasis] AS [Employee Basis],
vEBenefit.[EnEERate] AS [Employee Rate],
vEBenefit.[EnTierDescrip] AS [Tier Description],
vBenBase.[BbType] AS [Benefit Type ID],
vBenBase.[BbKind] AS [Benefit Kind ID],
vBenBase.[BbDescrip] AS [Benefit Description],
vBenBase.[BbPrintDescrip] AS [Benefit Printable Description],
vEBenefit.[EnErCostProrated] AS [Employer Prorated Cost],
vEBenefit.[EnErCostPerPay] AS [Employer Per Pay Cost],
vEBenefit.[EnErCostAnnual] AS [Employer Annual Cost],
vEBenefit.[EnErBasis] AS [Employer Basis],
vEBenefit.[EnErRate] AS [Employer Rate],
vEBenefit.[En3PCostProrated] AS [Carrier Prorated Cost],
vEBenefit.[En3PCostPerPay] AS [Carrier Per Pay Cost],
vEBenefit.[En3PCostAnnual] AS [Carrier Annual Cost],
vEBenefit.[En3PBasis] AS [Carrier Basis],
vEBenefit.[En3PRate] AS [Carrier Rate],
vEJob.[EjDateBeg] AS [Job Begin Date],
vEJob.[EjDateEnd] AS [Job End Date],
vJobCode.[JbJobCode] AS [Job Code ID],
vJobCode.[JbDescrip] AS [Job Code Description],
vEJob.[EjCompany] AS [Company ID],
vEJob.[EjDivision] AS [Division ID],
vEJob.[EjDepartment] AS [Department ID],
vEJob.[EjLocation] AS [Location ID],
vEJob.[EjRegion] AS [Region ID],
vEJob.[EjTitle] AS [Title],
vEJob.[EjSupervisor] AS [Supervisor Name],
vJobCode.[JbClass] AS [Job Class],
vJobCode.[JbEEO] AS [EEO Category],
vJobCode.[JbEEOGroup] AS [EEO Group],
vJobCode.[JbFLSA] AS [FLSA Code],
vJobCode.[JbBandID] AS [Job Broadband Code],
vEComp.[EmDateBeg] AS [Compensation Begin Date],
vEComp.[EmDateEnd] AS [Compensation End Date],
vEComp.[EmKind] AS [Kind of Compensation],
vEComp.[EmHourlyRate] AS [Hourly Pay Rate],
vEComp.[EmPerPay] AS [Per-pay Rate],
vEComp.[EmAnnual] AS [Annualized Pay Rate],
vEComp.[EmAnnualProrated] AS [Pro-rated Annual Amount],
vEComp.[EmPayStep] AS [Pay Step ID],
vEComp.[EmPayCycle] AS [Pay Frequency],
vEComp.[EmPayType] AS [Pay Type ID],
vEComp.[EmHoursPerPay] AS [Scheduled Hours Per Pay Period],
vEComp.[EmShiftID] AS [Shift ID],
vEComp.[EmReason] AS [Comp Record Reason],
vEPayrollBase.[PrbRateCode] AS [Hourly or Salary Code],
vEEmploy.[EeDateBeg] AS [Employment Begin Date],
vEEmploy.[EeDateEnd] AS [Employment End Date],
vEEmploy.[EeStatus] AS [Employment Status],
vEEmploy.[EeTermDate] AS [Termination Date],
vEEmploy.[EeTermWhy] AS [Termination Reason],
vEEmploy.[EeCategory] AS [Employment Category],
vEEmploy.[EeDateOriginalHire] AS [Original Hire Date],
vEEmploy.[EeDateLastHire] AS [Recent Hire Date],
vEEmploy.[EeDateSeniorityCalc] AS [Seniority Date],
vEEmploy.[EeDateBenefitCalc] AS [Benefit Calc Date]
FROM ((((((((vEbase
INNER JOIN vEPerson
ON vEbase.EbFlxID = vEPerson.EpFlxIDEb)
LEFT JOIN vEBenefit
ON vEbase.EbFlxID = vEBenefit.EnFlxIDEb)
LEFT JOIN vBenBase
ON vEBenefit.EnFlxIDBb = vBenBase.BbFlxID)
INNER JOIN vEComp
ON vEbase.EbFlxID = vEComp.EmFlxIDEb)
INNER JOIN vEEmploy
ON vEbase.EbFlxID = vEEmploy.EeFlxIDEb)
INNER JOIN vEPayrollBase
ON vEbase.EbFlxID = vEPayrollBase.PrbFlxIDEb)
INNER JOIN vEJob
ON vEbase.EbFlxID = vEJob.EjFlxIDEb)
LEFT JOIN vJobCode
ON vJobCode.JbJobCode = vEJob.EjJobCode)
WHERE EbFlagEmp = 'Y'
AND (vEPerson.[EpDateBeg]= @STARTDATE OR vEPerson.[EpDateEnd] IS NULL))
AND (vEBenefit.[EnDateBeg]= @STARTDATE OR vEBenefit.[EnDateEnd] IS NULL))
AND (vEComp.[EmDateBeg]= @STARTDATE OR vEComp.[EmDateEnd] IS NULL))
AND vecomp.[emkind] = 'BASE'
AND (vEEmploy.[EeDateBeg]= @STARTDATE OR vEEmploy.[EeDateEnd] IS NULL))
AND (vEPayrollBase.[PrbDateBeg]= @STARTDATE OR vEPayrollBase.[PrbDateEnd] IS NULL))
AND (vEJob.[EjDateBeg]= @STARTDATE OR vEJob.[EjDateEnd] IS NULL))
END
GO
Then you can call that procedure with any given date as follows:
EXEC sp_PROC_NAME_GOES_HERE '07/09/1998 12:00:00'
You can even change that hard coded date to a variable by declaring one and replacing the hard-coded date with it, as follows:
DECLARE @SD AS DateTime
SET @SD = '07/09/1998 12:00:00'
EXEC sp_PROC_NAME_GOES_HERE @SD
Steve
(aka smunson)
:-):-):-)
[/code]
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply