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

Viewing 15 posts - 1 through 15 (of 24 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: 150468 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
All articles available on SQL.kiwi
@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: 95461 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

Luzi62 Ten Centuries Points: 1218 More actions · Answer 11

the data fits not in the column. so you have to increase the size of the filed to at least 17 characters

e.g nvarchar(17) bevor the update

timotech SSCrazy Points: 2698 More actions · Answer 12

ALTER TABLE myPoorlyDesignedTable ADD CONSTRAINT

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

Hi Luci, i don't understand the DF_Table_1_Upper,

should i do this:

ALTER TABLE tbl_TVRates ADD CONSTRAINT

DF_tbl_TVRates_1_Upper_Time DEFAULT N'19000101 00:00:00' FOR Upper_Time

because my table name is tbl_TVRates and the field is Upper_Time

Thanks

Luzi62 Ten Centuries Points: 1218 More actions · Answer 13

timotech (4/14/2010)
ALTER TABLE myPoorlyDesignedTable ADD CONSTRAINT
DF_Table_1_Upper DEFAULT N'19000101 00:00:00' FOR Upper
Hi Luci, i don't understand the DF_Table_1_Upper,
should i do this:
ALTER TABLE tbl_TVRates ADD CONSTRAINT
DF_tbl_TVRates_1_Upper_Time DEFAULT N'19000101 00:00:00' FOR Upper_Time
because my table name is tbl_TVRates and the field is Upper_Time
Thanks

Yes if this Constraint Name is not used allready. so you see on wich table and column your constraint is working

timotech SSCrazy Points: 2698 More actions · Answer 14

thanks so much everybody, u've been so helpful, the little problem now is that, after i ran this command successfully:

ALTER TABLE tbl_TVRates ADD CONSTRAINT

DF_tbl_TVRates_1_Lower_Time DEFAULT N'19000101 00:00:00' FOR Lower_Time

then i tried to covert the field to datetime using this command:

ALTER TABLE tbl_TVRates ALTER COLUMN Lower_Time DATETIME

it brought this error:

Msg 5074, Level 16, State 1, Line 1

The object 'DF_tbl_TVRates_1_Lower_Time' is dependent on column 'Lower_Time'.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN Lower_Time failed because one or more objects access this column.

what can i do again, i feel that we are almost solving the problem because the first column Upper_Time coverted to datetime successfully.

Timotech.