Convert string to datetime error

Question

Post reply

Convert string to datetime error

timotech

SSCrazy

Points: 2698
More actions
April 13, 2010 at 4:40 pm

#223163

Hi all, please can anyone help me with this tsql, its bringing this error,
Conversion failed when converting datetime from character string.
what can i do,
Lower_time and Upper_time are nvarchar(8) fields containing data such as 12:00:00 or like.
this is my tsql:
declare @GivenTime datetime
declare @WeekDayCriteria int
declare @Duration int
declare @StationId nvarchar(6)
declare @StateId nvarchar(2)
declare @Backdrop nvarchar(1)
set @GivenTime = '04:59:59'
set @WeekDayCriteria = 0
set @Duration = 60
set @StationId = 'A1'
set @StateId = 'la'
set @Backdrop = 'F'
SELECT TOP (100) PERCENT UPPER_TIME, LOWER_TIME
FROM dbo.tbl_TVRates INNER JOIN
dbo.tbl_Stations ON dbo.tbl_TVRates.FK_StationId = SUBSTRING(dbo.tbl_Stations.StationId, 2, 4)
WHERE (dbo.tbl_Stations.StationId = @StationId) AND (dbo.tbl_Stations.FK_StateId = @StateId) AND (dbo.tbl_Stations.StationId LIKE 'A%') AND
(dbo.tbl_TVRates.DURATION = @Duration) AND (dbo.tbl_TVRates.BACKD = @Backdrop) AND (dbo.tbl_TVRates.WKDAY = @WeekDayCriteria)
AND ((select convert(datetime, @GivenTime) as Thevalue)
Between convert(datetime,Lower_Time) and convert(datetime,Upper_Time))
ORDER BY dbo.tbl_Stations.FK_StateId
Any help will be appreciated
April 14, 2010 at 1:18 am

This was removed by the editor as SPAM
April 14, 2010 at 2:06 am

This was removed by the editor as SPAM
April 14, 2010 at 2:09 am

This was removed by the editor as SPAM
April 14, 2010 at 3:52 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

timotech SSCrazy Points: 2698 More actions · Answer 1

Hi, thanks for your reply, i tried your code, but it brought this error:

Parameter or variable '@GivenTime' has an invalid data type.

what else do u think i should do?

Timotech.

timotech SSCrazy Points: 2698 More actions · Answer 2

Thanks for your reply, i'm using sql server 2005

Timotech

timotech SSCrazy Points: 2698 More actions · Answer 3

Thanks,

I tried using '1900-01-01 04:01:00' but it still said cannot convert to datetime.

The problem is that the Lower_time field and Upper_time fields are of type nvarchar(8) containing values such as 04:01:00, so i think that is why its complaining.

I wasn't the creator of the database, i'm a new programmer in the company, the former programmer used nvarchar(8) for the fields. how do u think i can do a time comparison between lower_time and Upper_time to get out my values.

Thanks for your replies.

Paul White SSC Guru Points: 150457 More actions · Answer 4

One other thing, timotech:

TOP 100 Percent ORDER BY Considered Harmful:

http://blogs.msdn.com/queryoptteam/archive/2006/03/24/560396.aspx

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

timotech SSCrazy Points: 2698 More actions · Answer 5

Hi Stewart, thanks for your contributions, i just tried it, it seems to give the same problem. i don't know what else to do.

Luzi62 Ten Centuries Points: 1218 More actions · Answer 6

If the Script above does not work, you have to check the Upper and Lower fields for invalid data.

e.g. empty (null) or not valid time data

timotech SSCrazy Points: 2698 More actions · Answer 7

Hi thanks for your replies. unfortunately the fields has a lot of Null values. there are about 4000 records, how do i fill the nulls and probably convert the fields to datetime datatype. thanks

WayneS SSC Guru Points: 95405 More actions · Answer 8

timotech (4/14/2010)
Hi thanks for your replies. unfortunately the fields has a lot of Null values. there are about 4000 records, how do i fill the nulls

UPDATE myPoorlyDesignedTable

SET TimeInCharField = '00:00:00'

WHERE TimeInCharField IS NULL

and probably convert the fields to datetime datatype. thanks

First you need to replace all data to include a date first:

UPDATE myPoorlyDesignedTable

SET TimeInCharField = '19000101 ' + TimeInCharField

WHERE TimeInCharField IS NOT NULL

Then you change the column's datatype after all fields are either null or proper time fields.

ALTER TABLE myPoorlyDesignedTable ALTER COLUMN TimeInCharField DATETIME

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Luzi62 Ten Centuries Points: 1218 More actions · Answer 9

and maybe set a default value and not null for the column 😉

ALTER TABLE myPoorlyDesignedTable ADD CONSTRAINT

DF_Table_1_Upper DEFAULT N'19000101 00:00:00' FOR Upper

timotech SSCrazy Points: 2698 More actions · Answer 10

Hi Wayne, thanks for your post it was very helpful, but the second update did not run. i did this:

UPDATE tbl_TVRates

SET Upper_Time = '19000101 ' + Upper_Time

WHERE Upper_Time IS NOT NULL

but it brought this error:

Msg 8152, Level 16, State 13, Line 1

String or binary data would be truncated.

The statement has been terminated.

what can i do to correct it?

Thanks

Timotech