Implicit conversion from data type datetime | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Implicit conversion from data type datetime

Hi All I am new to this SQL coding thingy I get the error "Implicit conversion from data type datetime"
SELECT @booking_week = ((@startdate – @booking_year_start) /7) + 1
what I am trying to do is create a week dimesion and work out when the booking week starts I have this so far but am stuck Would appricate some guidance here please Cheers
Harry
———————————————————————————————————–
DECLARE @startdateDATETIME
DECLARE @enddateDATETIME
[email protected]_lstDATETIME
[email protected]_numINT
[email protected]_year_start DATETIME
[email protected]
[email protected]_weekINT
———————————————————————————————————–
[email protected]=’2000-06-25′
[email protected]=’2011-12-31′ ———————————————————————————————————–
WHILE @startdate <= @enddate
BEGIN
SELECT @startdate= DATEADD(day, 7, @startdate)
SELECT @month= DATEPART(mm,@startdate)
SELECT @july_lst= DATEPART(yy,CAST(’01-JUL-‘+CAST(DATEPART(m,@startdate)AS VARCHAR(32))AS VARCHAR(32)))
SELECT @day_num= DATEPART(dd,@july_lst) –It is a sunday therefore start of fin year
IF (DATEPART(mm,@startdate) = 7 )
SELECT @booking_year_start = @july_lst
ELSE
SELECT @booking_year_start = @july_lst – @day_num
END
— booking year started last years fin year
IF (DATEPART(mm,@startdate) <= 6 )
AND @startdate < @booking_year_start
—
SELECT @july_lst= DATEPART(yy,CAST(’01-JUL-‘+CAST(DATEPART(m,@startdate)AS VARCHAR(32))AS VARCHAR(32)))
SELECT @day_num= DATEPART(dd,@july_lst) –It is a sunday therefore start of fin year
IF (DATEPART(dw,@day_num) = 7)
SELECT @booking_year_start = @july_lst
ELSE
SELECT @booking_year_start = @july_lst – @day_num
SELECT @booking_week = ((@startdate – @booking_year_start) /7) + 1
You say week dimension, are you involving this in Analysis Services or in any cube? Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
<<
SELECT @booking_week = ((@startdate – @booking_year_start) /7) + 1
>>
Can you explain what you are trying to do? Madhivanan Failing to plan is Planning to fail
Dimensions? Analysis Services? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
hi
Harry Zaharopoulos U do one thing as i try
use below code
SELECT @booking_week = ((cast(@startdate as int) – cast(@booking_year_start as int)) /7) + 1 and try your whole sp as below ———————————————————————————————————-
DECLARE @startdate DATETIME
DECLARE @enddate DATETIME
DECLARE @july_lst DATETIME
DECLARE @day_num INT
DECLARE @booking_year_start DATETIME
DECLARE @month INT
DECLARE @booking_week INT
———————————————————————————————————–
SET @startdate = ‘2000-06-25’
SET @enddate = ‘2011-12-31’ ———————————————————————————————————–
WHILE @startdate <= @enddate
BEGIN
SELECT @startdate = DATEADD(day, 7, @startdate)
SELECT @month = DATEPART(mm,@startdate)
SELECT @july_lst = DATEPART(yy,CAST(’01-JUL-‘+CAST(DATEPART(m,@startdate)AS VARCHAR(32))AS VARCHAR(32)))
SELECT @day_num = DATEPART(dd,@july_lst) –It is a sunday therefore start of fin year
IF (DATEPART(mm,@startdate) = 7 )
SELECT @booking_year_start = @july_lst
ELSE
SELECT @booking_year_start = @july_lst – @day_num
END
— booking year started last years fin year
IF (DATEPART(mm,@startdate) <= 6 )
AND @startdate < @booking_year_start
—
SELECT @july_lst = DATEPART(yy,CAST(’01-JUL-‘+CAST(DATEPART(m,@startdate)AS VARCHAR(32))AS VARCHAR(32)))
SELECT @day_num = DATEPART(dd,@july_lst) –It is a sunday therefore start of fin year
IF (DATEPART(dw,@day_num) = 7)
SELECT @booking_year_start = @july_lst
ELSE
SELECT @booking_year_start = @july_lst – @day_num
SELECT @booking_week = ((cast(@startdate as int) – cast(@booking_year_start as int)) /7) + 1 regards
shashi kant chauhan Keep In Touch
Thanks & Regards
Shashi Kant Chauhan
——- PRELIMINARY1: TEST SELECT QUERY
SELECT record_id, spell_start_date,
spell_discharge_date,provider
FROM dbo.cube_sus_apc_5NA_05_08_qtr1
WHERE (admission_method BETWEEN ’21’ AND ’28’)
AND (spell_start_date BETWEEN ‘2007/04/01’ AND ‘2008/03/31’)AND provider like ‘RF4%’
ORDER BY spell_start_date,spell_discharge_date
——- STAGE 1: SELECT READMISSIONS WITHIN 28 DAYS OF PREVIOUS DISCHARGE DATE
USE Adhoc
GO
SET NOCOUNT ON
GO
DECLARE
@record_id INT,
@AdmissionDate DATETIME,
@DischargeDate DATETIME,
@prevrecord_id INT,
@PrevDischargeDate DATETIME,
@local_patient_identifier INT
DECLARE @Readmissions TABLE
(
record_id INT,
AdmissionDate DATETIME,
DischargeDate DATETIME,
ReadmissionInterval INT
)
DECLARE Readmission CURSOR FAST_FORWARD FOR
SELECT record_id, local_patient_identifier,spell_start_date,
spell_discharge_date
FROM dbo.cube_sus_apc_5NA_05_08_qtr1
WHERE
(admission_method BETWEEN ’21’ AND ’28’)
AND (spell_discharge_date BETWEEN ‘2007/04/01’ AND ‘2008/03/31’)
ORDER BY record_id, spell_start_date,spell_discharge_date
OPEN Readmission
SET @prevrecord_id=NULL
FETCH NEXT FROM Readmission
INTO @record_id,@AdmissionDate,@DischargeDate,@local_patient_identifier
WHILE @@FETCH_STATUS=0
BEGIN
IF
(@record_id<>@prevrecord_id OR @record_id IS NULL)
INSERT INTO @Readmissions
(record_id,AdmissionDate,DischargeDate,ReadmissionInterval)
VALUES(@record_id,@AdmissionDate,@DischargeDate,NULL)
ELSE
INSERT INTO @Readmissions
(record_id,AdmissionDate,DischargeDate,ReadmissionInterval)
VALUES(@record_id,@AdmissionDate,@DischargeDate,
DATEDIFF(dd,@PrevDischargeDate,@AdmissionDate))
SELECT
@[email protected]_id,
@[email protected]
FETCH NEXT FROM Readmission
INTO @record_id,@AdmissionDate,@DischargeDate
END
CLOSE Readmission
DEALLOCATE Readmission
/* CREATE A TABLE FOR READMISSION WITHIN 14 DAYS OF INTERVAL*/
IF OBJECT_ID(‘Fourteen_Days_Readmissions’,’U’) IS NOT NULL
DROP TABLE fourteen_Days_Readmissions
SELECT *
INTO fourteen_Days_Readmissions
FROM Readmissions
WHERE ReadmissionInterval BETWEEN 0 AND 14
/* RETURN THE TOTAL COUNT OF READMISSIONS WITHIN 14 DAYS OF DISCHARGE*/
SELECT COUNT(*)
FROM fourteen_Days_Readmissions
WHERE ReadmissionInterval BETWEEN 0 AND 14
i am getting error mesage Cursor Fetch: Implicit conversion from data type datetime to int is not allowed,
can you help me to solve this
The highlighted bit is NOT where your problem is occurring. Compare the column list of the cursor –
record_id, local_patient_identifier,spell_start_date, spell_discharge_date
– to the variable list for the FETCH INTO:
@record_id,@AdmissionDate,@DischargeDate,@local_patient_identifier
The order of the columns and variables must match.
If I would take a closer look I might start wondering why you’re not using a straight INSERT INTO query here.
]]>