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

Thursday, April 26, 2012 2:36 PM

Dear all,

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

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

All Replies

Thursday, April 26, 2012 2:41 PM

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:43 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:47 PM

0
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
- Reply
- Quote
Thursday, April 26, 2012 2:47 PM

0

Tried this one already but still fails.

It's driving me crazy.
Jeppen Twitter: @DoSTHGreat
- Reply
- Quote
Thursday, April 26, 2012 2:50 PM

0

It hangs? Can you post sample data?

David.
- Reply
- Quote
Thursday, April 26, 2012 2:55 PM

0

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
- Reply
- Quote
Thursday, April 26, 2012 3:03 PM

Moderator

0
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
- Edited by Naomi NMicrosoft Community Contributor, Moderator Thursday, April 26, 2012 3:04 PM
- Reply
- Quote
Thursday, April 26, 2012 3:05 PM

0

Naomi,

New_ExecutionTime is stored as nvarchar(50).

I tried your line but still same issue.

Best,
Jeppen Twitter: @DoSTHGreat
- Reply
- Quote
Thursday, April 26, 2012 3:08 PM

Moderator

0
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...
- Reply
- Quote

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:10 PM

Moderator

0

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
- Reply
- Quote

Thursday, April 26, 2012 3:11 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:12 PM

0

I still want to use datetime to prevent any issue when the time is execution time 23:50.
Jeppen Twitter: @DoSTHGreat
- Reply
- Quote
Thursday, April 26, 2012 3:14 PM

Moderator

0
> 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...
- Edited by HunchbackMVP, Moderator Thursday, April 26, 2012 3:17 PM
- Reply
- Quote

Thursday, April 26, 2012 3:18 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:20 PM

Moderator

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:22 PM

Moderator

0
> 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...
- Edited by HunchbackMVP, Moderator Thursday, April 26, 2012 3:25 PM
- Reply
- Quote

Thursday, April 26, 2012 3:43 PM

Moderator

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:56 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

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

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?