convert an INT field into a datetime field

• SqlSanctum

  Ten Centuries

  Points: 1229

  More actions

  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+''