Convert string to datetime error

Question

Post reply

Convert string to datetime error

Viewing 9 posts - 16 through 24 (of 24 total)

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

Luzi62 Ten Centuries Points: 1218 More actions · Answer 1

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 2

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 3

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 4

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 5

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 6

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

WayneS SSC Guru Points: 95461 More actions · Answer 7

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 8

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

Timotech

timotech SSCrazy Points: 2698 More actions · Answer 9

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