Home Forums SQL Server 2005 T-SQL (SS2K5) convert an INT field into a datetime field RE: convert an INT field into a datetime field

  • June 26, 2015 at 11:04 am

    #1808482

    dmarz96 (6/26/2015)

    Good Day To All, with respect and thanks to all!

    OK I think I have a work around with everyones input and a breif discussion with the users. I can get the user to enter the first of the month for both parameter just have to put verbage in the report design when interacting with the date picker.

    I include only the code that was added, prior code worked.

    --But I get this error

    Msg 241, Level 16, State 1, Procedure ALSP_ROLLING12_MONTH_REPORT_BILLABLE_HOURS_ONLY, Line 52

    Conversion failed when converting date and/or time from character string.

    -- how the stored procedure is executed

    EXEC ALSP_ROLLING12_MONTH_REPORT_BILLABLE_HOURS_ONLY '2014-05-01','2015-04-01'

    -- Declare Parameters to passed to report

    (

    @BeginPeriod DateTime,

    @EndPeriod DateTime

    )

    DECLARE @BeginPeriod2 VARCHAR(20) SET @BeginPeriod2 = CONVERT(VARCHAR(20), @BeginPeriod, 100)

    DECLARE @EndPeriod2 VARCHAR(20) SET @EndPeriod2 = CONVERT(VARCHAR(20), @EndPeriod, 100)

    -- code in my where clause

    WHERE CONVERT(DATETIME,CONVERT(VARCHAR,TT11.PERIOD)+'01') >= ''+@BeginPeriod2+''

    AND CONVERT(DATETIME,CONVERT(VARCHAR,TT11.PERIOD)+'01') <= ''+@EndPeriod2+''

    Is your period column an INT, or is there some bad data in your table? I ask because I received no error when I tried to make some sample data and execute your code. If I throw in some bad data, like making the DateInt = 2015016, then I get the same error you got.

    --Table for Sample Data

    DECLARE @Example TABLE

    (DateInt INT, Data INT)

    INSERT INTO @Example VALUES

    (201501,1),(201502,7),(201503,3),(201503,2),(201504,5),(201506,6),(201506,7),(201507,2),(201507,4),(201508,8)

    --Change the first value to 2015016 and you'll get the error message.

    --Setting the original values

    declare @beginperiod datetime , @endperiod datetime

    SET @beginperiod = '2014-05-01'

    SET @endperiod = '2015-04-01'

    --your code below

    DECLARE @BeginPeriod2 VARCHAR(20) SET @BeginPeriod2 = CONVERT(VARCHAR(20), @BeginPeriod, 100)

    DECLARE @EndPeriod2 VARCHAR(20) SET @EndPeriod2 = CONVERT(VARCHAR(20), @EndPeriod, 100)

    select * from @example

    WHERE CONVERT(DATETIME,CONVERT(VARCHAR,dateint)+'01') >= ''+@BeginPeriod2+''

    AND CONVERT(DATETIME,CONVERT(VARCHAR,dateint)+'01') <= ''+@EndPeriod2+''