| Author |
 Topic  |
|
|
sross81
Posting Yak Master
USA
228 Posts |
 Posted - 02/06/2013 : 15:01:00
|
I have 4 fields that are imported as nvarchar(255). I concatenated the dates and the time together so that I could have a specific datetime field so that I can do a date diff on it. It works if I import as datetime, but the package doesn't always import right if there are nulls so I thought it would be best to get all of the data in and then work with it.
,(m.AMIArrivalDate + ' ' + m.AMIArrivalTime)as Arrival
,(m.AMIFirstPCIDate + ' ' + m.AMIFirstPCITime) AS PCI
The fields are later used here:
AMI8aTimeToPCI = DateDiff("n",vw.Arrival,vw.PCI)
I get this error:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
I have tried:
,convert(datetime,(m.AMIArrivalDate + ' ' + m.AMIArrivalTime))as Arrival
,convert(datetime,(m.AMIFirstPCIDate + ' ' + m.AMIFirstPCITime)) AS PCI
I still get the same errors.
Can anyone give me any advice?
Thanks in Advance!
Sherri
Thanks in Advance!
Sherri |
|
|
djj55
Posting Yak Master
USA
100 Posts |
Posted - 02/06/2013 : 15:31:37
|
Try to use an ISNULL function ,(ISNULL(m.AMIArrivalDate + ' ','1') + ISNULL(m.AMIArrivalTime,'1'))as Arrival your default values will need to be tweaked.
djj |
 |
|
|
sross81
Posting Yak Master
USA
228 Posts |
Posted - 02/06/2013 : 15:42:57
|
The isnull didn't seem to help. I think I need to convert it, but nothing I do to try to convert it works.
ALTER VIEW [dbo].[vw_AMI8a_CalculateAMI8aPCITime]
As
SELECT
m.EncounterNbr
,m.MeasureSet
,cast(m.AMIArrivalDate as date) as AMIArrivalDate
,cast(m.AMIArrivalTime as time) as AMIArrivalTime
,cast(m.AMIFirstPCIDate as date) as AMIFirstPCIDate
,cast(m.AMIFirstPCITime as time) as AMIFirstPCITime
--,(m.AMIArrivalDate + ' ' + m.AMIArrivalTime)as Arrival
,(ISNULL(m.AMIArrivalDate + ' ','1') + ISNULL(m.AMIArrivalTime,'1'))as Arrival
--,(m.AMIFirstPCIDate + ' ' + m.AMIFirstPCITime) AS PCI
,(ISNULL(m.AMIFirstPCIDate + ' ','1') + ISNULL(m.AMIFirstPCIDate,'1'))as PCIFROM MeasureExportRAW m
WHERE (((m.MeasureSet)='AMI') AND ((m.AMIArrivalTime) Is Not Null))
OR (((m.MeasureSet)='AMI') AND ((m.AMIFirstPCITime) Is Not Null))
This is where I have the error when I try to do a date diff.
UPDATE AMI
SET
AMI8aTimePCI = vw.AMIFirstPCITime
,AMI8aTimePatientArrived = vw.AMIArrivalTime
,AMI8aTimeToPCI = DateDiff("n",vw.Arrival,vw.PCI) From vw_AMI8a_CalculateAMI8aPCITime vw
Join AMI a on a.AMIEncNbr = vw.EncounterNbr
Thanks in Advance!
Sherri |
|
|
|
sross81
Posting Yak Master
USA
228 Posts |
Posted - 02/06/2013 : 15:47:22
|
This is the kind of data in the fields that I am trying to do a date diff on when I execute the view.
Arrival: 2012-07-20 01:00:00.0000000
PCI: 2012-07-21 1899-12-30 08:22:00
Thanks in Advance!
Sherri |
|
|
|
sross81
Posting Yak Master
USA
228 Posts |
Posted - 02/06/2013 : 16:00:17
|
I found a fix.
I created a second view that takes the values of the first view. In my first view I casted all of the fields as either date or time:
ALTER VIEW [dbo].[vw_AMI8a_CalculateAMI8aPCITime]
As
SELECT
m.EncounterNbr
,m.MeasureSet
,cast(m.AMIArrivalDate as date) as AMIArrivalDate
,cast(m.AMIArrivalTime as time) as AMIArrivalTime
,cast(m.AMIFirstPCIDate as date) as AMIFirstPCIDate
,cast(m.AMIFirstPCITime as time) as AMIFirstPCITime
,(m.AMIArrivalDate + ' ' + m.AMIArrivalTime)as Arrival
,(m.AMIFirstPCIDate + ' ' + m.AMIFirstPCITime) AS PCI
FROM MeasureExportRAW m
WHERE (((m.MeasureSet)='AMI') AND ((m.AMIArrivalTime) Is Not Null))
OR (((m.MeasureSet)='AMI') AND ((m.AMIFirstPCITime) Is Not Null))
Then in my second view I converted the actual values that I concatenated to date time. Then in my stored procedure that calls the view I was able to just do a normal date diff.
alter view vw_AMI8a_CalculateAMI8aPCITime_2
as
SELECT
m.EncounterNbr
,m.MeasureSet
,m.AMIArrivalDate
,m.AMIArrivalTime
,m.AMIFirstPCIDate
,m.AMIFirstPCITime
,convert(datetime,m.AMIArrivalDate) + ' ' + convert(datetime,m.AMIArrivalTime)as Arrival
,convert(datetime,m.AMIFirstPCIDate) + ' ' + convert(datetime,m.AMIFirstPCITime) AS PCI
from vw_AMI8a_CalculateAMI8aPCITime m
Thanks in Advance!
Sherri |
|
|
| |
Topic |
|
|
|
Using a Concatenated DateTime Field in a DateDiff
