SQL Server Performance

How to return date from sqlserver stored procedure

Discussion in 'General Developer Questions' started by lalitha_karani, Jul 30, 2005.

  1. lalitha_karani New Member

    Hi,
    in my ASP.Net project, i am using sqlserver as a back end. i want to return date by using stored procedure. Here i am facing one probelm while i am trying to create below procedure it gave below error:

    "Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query."

    i tried with cast function also, it is giving some other problem. i want to return that date. Plz help me out to over come this problem.

    Here is my Stored Procedure:

    create procedure insert_myjobs1
    (
    @jobid varchar(20),
    @resumeid int,
    @dtsaved datetime output
    )
    as
    begin
    set nocount off
    set ansi_warnings off
    if not exists(select 'true' from myjobs where jobid=@jobid)
    begin
    insert into myjobs (jobid, resumeid) values(@jobid, @resumeid)
    end
    else
    begin
    select @dtsaved = dtsaved from myjobs where jobid=@jobid
    return @dtsaved
    end
    end


    Thanking You,

    Lalitha.c
  2. chopeen Member

    What is the data type of dtsaved column in myjobs table?

    --

    "Recommended By Dr. Audioscrobbler."
    http://www.audioscrobbler.com/user/chopeen/
  3. ghemant Moderator

    Hi,<br />instead try to use it like : <br />create procedure insert_myjobs1 <br />( <br />@jobid varchar(20), <br />@resumeid int, <br />@dtsaved <b>varchar(12)</b> output<br />) <br />as <br />begin <br />set nocount off <br />set ansi_warnings off <br />if not exists(select 'true' from myjobs where jobid=@jobid) <br />begin <br />insert into myjobs (jobid, resumeid) values(@jobid, @resumeid) <br />end <br />else<br />begin <br />select @dtsaved = <b>convert(char(12),dtsaved,112)</b> from myjobs where jobid=@jobid <br />return @dtsaved<br />end <br />end <br /><br />Note : the changes in SP are in bold letter<br /><br /><br />[<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />HTH<br />Regards<br /><br /><br />hsGoswami<br />ghemant@gmail.com<br />"Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemant Goswami<br />
  4. lalitha_karani New Member

    Hi,
    Thank you very much. It is working.

    Lalitha.C
  5. satilado New Member

    ------- 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
    @prevrecord_id=@record_id,
    @PrevDischargeDate=@DischargeDate
    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
  6. Madhivanan Moderator

    [quote user="satilado"]
    ------- 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
    @prevrecord_id=@record_id,
    @PrevDischargeDate=@DischargeDate
    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
    [/quote]
    Is this related to this topic?
    Post your question as new topic
  7. Madhivanan Moderator

    [quote user="lalitha_karani"]Hi,
    Thank you very much. It is working.

    Lalitha.C[/quote]
    What is the datatype of dtsaved?

Share This Page