Convert string to datetime error

Question

Post reply

Convert string to datetime error

April 14, 2010 at 11:33 pm

This was removed by the editor as SPAM

Viewing 14 posts - 16 through 28 (of 28 total)

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

Luzi62 Ten Centuries Points: 1218 More actions · Answer 1

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 2

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 3

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 4

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.

Luzi62 Ten Centuries Points: 1218 More actions · Answer 5

Hi,

try this:

drop the constraint first

ALTER TABLE [dbo].[Table_1] DROP CONSTRAINT [DF_Table_1_T1]

then convert and add teh constraint again

timotech SSCrazy Points: 2698 More actions · Answer 6

Hi Luzi, thanks for your replies, i tried what u said, but i think the problem is comming from the table, because i checked the field for invalid data and got some. after this i tried to convert again after dropping the constraint, it complained it cant convert string to datetime, is there any way i can get out the record that does not conform to the format, so i can know where the error lies?

Thanks

Timotech

Luzi62 Ten Centuries Points: 1218 More actions · Answer 7

timotech (4/14/2010)
Hi Luzi, thanks for your replies, i tried what u said, but i think the problem is comming from the table, because i checked the field for invalid data and got some. after this i tried to convert again after dropping the constraint, it complained it cant convert string to datetime, is there any way i can get out the record that does not conform to the format, so i can know where the error lies?
Thanks
Timotech

Hi,

what is your Field now look like?

to find chars you can try serach with

where Field not like '[0-9]%' (for rows without digit at the begin)

or Field is null (for empty rows)

take a look at the field and get a proper pattern to search for. e.g (not like '[0-9][0-9]:%`) for 2 digits and a ':'

timotech SSCrazy Points: 2698 More actions · Answer 8

Hi Luzi, Thanks so much for your help, it has finally worked, i had to go through the records one at a time, then later discovered that some time entries were entered in the following format 00:00;00, then i had to run a query such as :

select lower_time from tbl_tvrates where lower_time like '%;%' so it brought be the culprits, i edited them and ran my former query again and everything worked exactly as i wanted it.

I'm very greatful thank u so much and the other guys that contributed.

God bless u all.

Thanks

Timotech.

You can reach me on timotech@yahoo.com

Luzi62 Ten Centuries Points: 1218 More actions · Answer 9

your welcome,

maybe in this case your next step is to create a check constraint?

like

ALTER TABLE [dbo].[Table_1] WITH NOCHECK ADD CONSTRAINT [CK_Table_1_T1_2] CHECK NOT FOR REPLICATION (([T1] like '[0-9][0-9]:[0-9][0-9]:[0-9][0-9]'))

ALTER TABLE [dbo].[Table_1] CHECK CONSTRAINT [CK_Table_1_T1_2]

timotech SSCrazy Points: 2698 More actions · Answer 10

Hi Luzi, why do i still need to do a constraint, since i have what i need?

WayneS SSC Guru Points: 95460 More actions · Answer 11

Did you get the column converted to a datetime data type? If so, that constraint won't help you.

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

timotech SSCrazy Points: 2698 More actions · Answer 12

Thanks wayne, i'll stick to your advice. Thanks so much for your help.

Timotech

timotech SSCrazy Points: 2698 More actions · Answer 13

Hi thanks Stewart, some nice tsql there, you guys are gurus.