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

    Question

  • Dear all,

    I'm trying to get the following query to run but it fails every time with the error message:

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

    Here is my query:

    --Declarations
    	DECLARE @iCurDayNumber INT
    	DECLARE @dToday DATE
    	
    	DECLARE @dFrom datetime
    	DECLARE @dTo datetime
    	
    	--Initialization
    	SELECT @iCurDayNumber = datepart(weekday, GETDATE())-1
    	SET @dToday = CAST(GETDATE() as DATE)
    	SET @dFrom = CAST(DATEADD(MINUTE, -5, GETDATE()) as datetime)
    	SET @dTo = CAST(DATEADD(MINUTE, 30, GETDATE()) as datetime)
    	
    
    	SELECT *
    	FROM MS_CRM.dbo.FilteredNew_scheduleline sched_line
    	WHERE new_recurrence = @iCurDayNumber
    	AND CAST(cast(@dToday as nvarchar(10))+ ' '+new_executiontime+':00' as datetime) BETWEEN @dFrom AND @dTo

    It fails on the last line. If I manually replace new_executiontime with '16:00' it works. (In the field executiontime values like 16:00 are stored)

    Thanks in advance,

    Jeppen


    Jeppen Twitter: @DoSTHGreat

    Thursday, April 26, 2012 2:36 PM

Answers

  • No, I believe it may not work.

    This works fine for me:

    declare @Schedule table (New_Recurrence tinyint, New_ExecutionTime nvarchar(50))
    
    insert into @Schedule values (4, '16:00'),(4,'23:50'), (4,'10:00'), (4,'10:30')
    DECLARE @iCurDayNumber INT
    	DECLARE @dToday DATE
    	
    	DECLARE @dFrom datetime
    	DECLARE @dTo datetime
    	
    	--Initialization
    	SELECT @iCurDayNumber = datepart(weekday, GETDATE())-1
    	SELECT @iCurDayNumber
    	SET @dToday = CAST(GETDATE() as DATE)
    	SET @dFrom = DATEADD(MINUTE, -5, GETDATE()) 
    	SET @dTo = DATEADD(MINUTE, 30, GETDATE()) 
    	
    
    	SELECT *
    	FROM @Schedule
    	WHERE new_recurrence = @iCurDayNumber
    	and cast(convert(varchar(10), @dToday, 120) + ' ' + coalesce(ltrim(New_ExecutionTime),'00:00') + ':00'  as datetime)
    	between @dFrom and @dTo


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Jeppen Thursday, April 26, 2012 3:56 PM
    Thursday, April 26, 2012 3:20 PM
  • Well i found a workaround...

    DECLARE @iCurDayNumber INT
    	DECLARE @dToday DATE
    	
    	DECLARE @dFrom datetime
    	DECLARE @dTo datetime
    	DECLARE @tSchedules TABLE
    	(
    		dt_time datetime,
    		connection_id nvarchar(150)
    	)
    	
    	--Initialization
    	SELECT @iCurDayNumber = datepart(weekday, GETDATE())-1
    	SET @dToday = CAST(GETDATE() as DATE)
    	SET @dFrom = CAST(DATEADD(MINUTE, -5, GETDATE()) as datetime)
    	SET @dTo = CAST(DATEADD(MINUTE, 30, GETDATE()) as datetime)
    	
    	INSERT INTO @tSchedules
    	 (dt_time
    	 ,connection_id)
    
    	SELECT   CAST(cast(@dToday as nvarchar(10))+ ' '+sched_line.new_executiontime+':00' as datetime)
    			,sched_line.new_connectionprofileid
    	FROM MS_CRM.dbo.FilteredNew_scheduleline sched_line
    	WHERE sched_line.new_recurrence = @iCurDayNumber
    	
    	SELECT *
    	from @tSchedules
    	WHERE dt_time between @dFrom and @dTo

    Thanks for you all for your help. But I'm still wonderding why I have to to it his way...

    Best,


    Jeppen Twitter: @DoSTHGreat

    • Marked as answer by Jeppen Thursday, April 26, 2012 3:56 PM
    Thursday, April 26, 2012 3:56 PM

All replies

  • Hi, you need to find out for which values of new_executiontime this happens. This column can have NULL values? If so, try this:

    SELECT *
    FROM MS_CRM.dbo.FilteredNew_scheduleline sched_line
    WHERE new_recurrence = @iCurDayNumber
    AND CAST(cast(@dToday as nvarchar(10))+ ' '+coalesce(new_executiontime, '00:00')+':00' as datetime) BETWEEN @dFrom AND @dTo

    David.

    Thursday, April 26, 2012 2:41 PM
  • Try casting the new_executiontime to nvarchar and try it again.

    SELECT *
    	FROM MS_CRM.dbo.FilteredNew_scheduleline sched_line
    	WHERE new_recurrence = @iCurDayNumber
    	AND CAST(cast(@dToday as nvarchar(10))+ ' '+CAST(new_executiontime as NVARCHAR(10))+':00' as datetime) BETWEEN @dFrom AND @dTo

     

    Please mark as answer if this answers your question. Please mark as helpful if you found this post was helpful.

    Thursday, April 26, 2012 2:43 PM
  • Thanks for your quick response David.

    There is only one avluie in the table. If I execute tis query 

    SELECT CAST(cast(@dToday as nvarchar(10))+ ' '+coalesce(new_executiontime, '00:00')+':00' as datetime)
    	FROM MS_CRM.dbo.FilteredNew_scheduleline sched_line
    	WHERE new_recurrence = @iCurDayNumber
    

    it works but if i but the between the filter it hangs


    Jeppen Twitter: @DoSTHGreat

    Thursday, April 26, 2012 2:47 PM
  • Tried this one already but still fails.

    It's driving me crazy.


    Jeppen Twitter: @DoSTHGreat

    Thursday, April 26, 2012 2:47 PM
  • It hangs? Can you post sample data?

    David.

    Thursday, April 26, 2012 2:50 PM
  • Hangs: "Conversion failed when converting date and/or time from character string."

    There is nothing more in the table (at the moment) for the filed new_executiontime then 16:30. I have 1 record. (type nvarchar(50))

    Best,

    Jeppen



    Jeppen Twitter: @DoSTHGreat

    Thursday, April 26, 2012 2:55 PM
  • What is the type of the New_ExecutionTime? Assuming it's time and you want to get all rows in the time interval, how about

    AND cast(New_ExecutionTime as time) between cast(@dFrom as time) and cast(@dTo as Time)

    Otherwise I suggest

    cast (convert(varchar(10),@dToday,120) + ' ' + coalesce(new_executiontime, '00:00')+':00' as datetime) 

    
    

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    Thursday, April 26, 2012 3:03 PM
  • Naomi,

    New_ExecutionTime is stored as nvarchar(50).

    I tried your line but still same issue.

    Best,


    Jeppen Twitter: @DoSTHGreat

    Thursday, April 26, 2012 3:05 PM
  • If you are storing time part, like hh:mm, in column [new_executiontime] then be sure all hour part are between 0 and 23, and minutes between 0 and 59.

    Also, instead manipulating the column, try adjusting or converting the variables in the predicate.

    SELECT
    	*
    FROM
    	MS_CRM.dbo.FilteredNew_scheduleline sched_line
    WHERE
    	new_recurrence = @iCurDayNumber
    	AND new_executiontime BETWEEN LEFT(CAST(@dFrom AS time), 5) AND LEFT(CAST(@dTo AS time), 5);

    or casting [new_executiontime] to time.

    SELECT
    	*
    FROM
    	MS_CRM.dbo.FilteredNew_scheduleline sched_line
    WHERE
    	new_recurrence = @iCurDayNumber
    	AND CAST(new_executiontime AS time) BETWEEN CAST(@dFrom AS time) AND CAST(@dTo AS time);

    I would suggest you to post table schema and sample data in the form of "insert" statements, so we can reproduce your problem.


    AMB

    Some guidelines for posting questions...

    Thursday, April 26, 2012 3:08 PM
  • Try this:

    SELECT *
    FROM MS_CRM.dbo.FilteredNew_scheduleline sched_line
    WHERE new_recurrence = @iCurDayNumber
    AND CAST(@dToday as datetime) + CAST(new_executiontime+':00' as time) BETWEEN @dFrom AND @dTo

    David.

    Thursday, April 26, 2012 3:08 PM
  • Did you try my first suggestion of casting New_ExecutionTime as time. If this is a time column, then why do you store it as nvarchar(50)? Do you plan to store something else there besides time? If not, I suggest to change the structure of the table and use correct type.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thursday, April 26, 2012 3:10 PM
  • SELECT
    	*
    FROM
    	MS_CRM.dbo.FilteredNew_scheduleline sched_line
    WHERE
    	new_recurrence = @iCurDayNumber
    	AND new_executiontime BETWEEN LEFT(CAST(@dFrom AS time), 5) AND LEFT(CAST(@dTo AS time), 5);

    This works. But if i have a schedule for 23:50, is it taken into account when i lime compare hours?

    Best,


    Jeppen Twitter: @DoSTHGreat

    Thursday, April 26, 2012 3:11 PM
  • I still want to use datetime to prevent any issue when the time is execution time 23:50.

    Jeppen Twitter: @DoSTHGreat

    Thursday, April 26, 2012 3:12 PM
  • > This works. But if i have a schedule for 23:50, is it taken into account when i lime compare hours?

    I do not understand your question.

    If all values are well formatted as hh:mm, then it will work with no problem. I would change the data type of that column to "time" if possible.

    Example:

    SELECT 1 AS c1
    WHERE '23:50' BETWEEN LEFT(CAST('20:15' AS time), 5) AND LEFT(CAST('23:55' AS time), 5);
    GO


    AMB

    Some guidelines for posting questions...

    Thursday, April 26, 2012 3:14 PM
  • Just tested it
    SET @dFrom = CAST('2012-01-01 23:55:00' as datetime)
    	SET @dTo = CAST('2012-01-02 00:30:00' as datetime)
    
    	SELECT *
    	FROM MS_CRM.dbo.FilteredNew_scheduleline sched_line
    	WHERE '23:56' BETWEEN LEFT(CAST(@dFrom AS time), 5) AND LEFT(CAST(@dTo AS time), 5);
    Unfortunately it does not work

    Jeppen Twitter: @DoSTHGreat

    Thursday, April 26, 2012 3:18 PM
  • No, I believe it may not work.

    This works fine for me:

    declare @Schedule table (New_Recurrence tinyint, New_ExecutionTime nvarchar(50))
    
    insert into @Schedule values (4, '16:00'),(4,'23:50'), (4,'10:00'), (4,'10:30')
    DECLARE @iCurDayNumber INT
    	DECLARE @dToday DATE
    	
    	DECLARE @dFrom datetime
    	DECLARE @dTo datetime
    	
    	--Initialization
    	SELECT @iCurDayNumber = datepart(weekday, GETDATE())-1
    	SELECT @iCurDayNumber
    	SET @dToday = CAST(GETDATE() as DATE)
    	SET @dFrom = DATEADD(MINUTE, -5, GETDATE()) 
    	SET @dTo = DATEADD(MINUTE, 30, GETDATE()) 
    	
    
    	SELECT *
    	FROM @Schedule
    	WHERE new_recurrence = @iCurDayNumber
    	and cast(convert(varchar(10), @dToday, 120) + ' ' + coalesce(ltrim(New_ExecutionTime),'00:00') + ':00'  as datetime)
    	between @dFrom and @dTo


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by Jeppen Thursday, April 26, 2012 3:56 PM
    Thursday, April 26, 2012 3:20 PM
  • > Unfortunately it does not work

    Because the first operand in the BETWEEN predicate should be lower than the second one.

    Example:

    -- this will not select any row
    select 1 as c1
    where 6 between 17 and 4;

    Same is happening in your example. The value of @dFrom should be lower than or equal to @dTo, which is not the case due to the conversion to time.

    If you are going to work with datetime, then be sure to use a format that is language independent, like style 126 (yyyy-mm-ddThh:mm:ss).


    AMB

    Some guidelines for posting questions...


    Thursday, April 26, 2012 3:22 PM
  • This works too, but manipulating the column will confuse the optimizer to use an existing index by that column (statistics for that column).

    ...
    SET @dToday = CAST(GETDATE() as DATE)
    SET @dFrom = DATEADD(MINUTE, -5, CAST(GETDATE() AS time)) 
    SET @dTo = DATEADD(MINUTE, 30, CAST(GETDATE() AS time)) 
    
    SELECT *
    FROM @Schedule
    WHERE new_recurrence = @iCurDayNumber
    and CAST(New_ExecutionTime AS datetime) between @dFrom and @dTo;
    GO


    AMB

    Some guidelines for posting questions...

    Thursday, April 26, 2012 3:43 PM
  • Well i found a workaround...

    DECLARE @iCurDayNumber INT
    	DECLARE @dToday DATE
    	
    	DECLARE @dFrom datetime
    	DECLARE @dTo datetime
    	DECLARE @tSchedules TABLE
    	(
    		dt_time datetime,
    		connection_id nvarchar(150)
    	)
    	
    	--Initialization
    	SELECT @iCurDayNumber = datepart(weekday, GETDATE())-1
    	SET @dToday = CAST(GETDATE() as DATE)
    	SET @dFrom = CAST(DATEADD(MINUTE, -5, GETDATE()) as datetime)
    	SET @dTo = CAST(DATEADD(MINUTE, 30, GETDATE()) as datetime)
    	
    	INSERT INTO @tSchedules
    	 (dt_time
    	 ,connection_id)
    
    	SELECT   CAST(cast(@dToday as nvarchar(10))+ ' '+sched_line.new_executiontime+':00' as datetime)
    			,sched_line.new_connectionprofileid
    	FROM MS_CRM.dbo.FilteredNew_scheduleline sched_line
    	WHERE sched_line.new_recurrence = @iCurDayNumber
    	
    	SELECT *
    	from @tSchedules
    	WHERE dt_time between @dFrom and @dTo

    Thanks for you all for your help. But I'm still wonderding why I have to to it his way...

    Best,


    Jeppen Twitter: @DoSTHGreat

    • Marked as answer by Jeppen Thursday, April 26, 2012 3:56 PM
    Thursday, April 26, 2012 3:56 PM