SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Using a Concatenated DateTime Field in a DateDiff
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/06/2013 :  15:01:00  Show Profile  Reply with Quote
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
Constraint Violating Yak Guru

USA
312 Posts

Posted - 02/06/2013 :  15:31:37  Show Profile  Reply with Quote
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
Go to Top of Page

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/06/2013 :  15:42:57  Show Profile  Reply with Quote
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
Go to Top of Page

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/06/2013 :  15:47:22  Show Profile  Reply with Quote
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
Go to Top of Page

sross81
Posting Yak Master

USA
228 Posts

Posted - 02/06/2013 :  16:00:17  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000