error to convert a string to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

error to convert a string to datetime

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-15 : 15:53:55

Hi,
I have a table variable defined as follows. When I tried to insert data for the row, especially column "ProductMonth", I got error like
Server: Msg 241, Level 16, State 1, Line 67
Syntax error converting datetime from character string.

----- sample codes
declare @t1 table(cust varchar(50), product int, productMonth datetime)

insert @t1
select 'ABC', 1234, 'Jul-07'
union all select 'ABC', 1234, 'Sep-07'
union all select 'XYZ', 567 , 'Sep-07'
union all select 'XYZ', 567 , 'Oct-07'
union all select 'ABC', 567 , 'Aug-07'
union all select 'PQRS', 897 , 'Nov-07'

Please tell me any idea to get rid of the probem when inserting rows for table @t1.
Thanks in advance.
johnsql

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-15 : 15:57:22

Well you are trying to insert bad data into a datetime data type. You'll need to convert those dates into something that datetime can accept. Check out the various datetime styles in the CONVERT function article in SQL Server Books Online to see what is valid.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Kristen
Test

22859 Posts

Posted - 2007-10-15 : 23:08:27

If you just want Month + Year you'll need to use the 1st of the month to store the value so that SQL Server has a "complete" date

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 02:15:03

Express dates as '01-Jul-07' or 'jul 2007' (not sure if this will work for all date settings)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 02:23:19

If you have a non-English locale on the server you need the month abbreviations for that language.

but that apart dd-Mmm-yyyy is a useful unambiguous date which is a bit more readable than yyyymmdd I suppose.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-16 : 03:15:20

quote:
Originally posted by Kristen

but that apart dd-Mmm-yyyy is a useful unambiguous date which is a bit more readable than yyyymmdd I suppose.

Kristen

Yes it is. Whenever I fill an application form, I always use dd-MMM-yyyy format for more readability

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 04:06:36

In the "I'm better than you stakes"

our web application re-displays dates in dd-Mmm-yyyy format - thus if the site is configured for European default (allows dd/mmm/yyyy or dd-Mmm-yyyy style) someone entering a date in mm/dd/yyyy will see it redisplayed with the wrong month name (or an error for dd > 12, obviously)

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-16 : 08:59:54

quote:
Originally posted by tkizer

Well you are trying to insert bad data into a datetime data type. You'll need to convert those dates into something that datetime can accept. Check out the various datetime styles in the CONVERT function article in SQL Server Books Online to see what is valid.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

The raw data I receive for that "datetime" column has been stored as varchar(50) (so you can see 'Aug-2007', 'Feb-2007', etc). But for my new table that column must be defined as datetime type. So, please tell the way to insert a valid value for the datetime column of the new table?

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 10:12:50

SELECT CONVERT(datetime, '01-' + 'Aug-2007')

will make the conversion, provided that your server's Locale language is English

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-17 : 02:43:46

or omit hyphen and do

SELECT CONVERT(datetime, 'Aug 2007')

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 03:54:58

Didn't know that ... thanks.

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-23 : 08:45:55

Buddies,
I still have a question.

According to BOL of SQL Server 2000, its image, type conversion from VARCHAR to DATETIME is implicit. That means "...Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST (CONVERT) function to be specified..."

By following that statement, I do not need to use CONVERT() function, then SQL Server thros error. It is weird. And I still wonder, why I must use explicit CONVERT() function.

The error looks like:
"Server: Msg 241, Level 16, State 1, Line 3
Syntax error converting datetime from character string."

-- the following scripts throws the error if I follow what BOL says ------

declare @t1 table(cust varchar(50), product int, productMonth datetime)

insert @t1
select 'ABC', 1234, 'Jul 07'
union all select 'ABC', 1234, 'Sep 2007'
union all select 'XYZ', 567 , 'Sep 2007'
union all select 'XYZ', 567 , 'Oct 2007'
union all select 'ABC', 567 , 'Aug 2007'
union all select 'PQRS', 897 , 'Nov 2007'

-- the following scripts throws the error even I use explicit CONVERT() function by your suggestions --------------

declare @t1 table(cust varchar(50), product int, productMonth datetime)

insert @t1
select 'ABC', 1234, CONVERT(datetime, 'Jul 07')
union all select 'ABC', 1234, CONVERT(datetime, 'Sep 2007')
union all select 'XYZ', 567 , CONVERT(datetime, 'Sep 2007')
union all select 'XYZ', 567 , CONVERT(datetime, 'Oct 2007')
union all select 'ABC', 567 , CONVERT(datetime, 'Aug 2007')
union all select 'PQRS', 897 , CONVERT(datetime, 'Nov 2007')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 08:52:13

Becuase you used 'Jul 07' instead of 'Jul 2007'

Madhivanan

Failing to plan is Planning to fail

johnsql
Posting Yak Master

161 Posts

Posted - 2007-10-23 : 09:18:17

quote:
Originally posted by madhivanan

Becuase you used 'Jul 07' instead of 'Jul 2007'

Madhivanan

Failing to plan is Planning to fail

Oop, I forgot about it.
Thanks so much.
So, If I use valid format for input string, then I do not use explicit CONVERT or CAST function when I insert raw data to my table, right?

Second question, I read BOL (CAST and CONVERT topic), I do not see input format like 'Mon yyyy', how do you know that is a valid input to implictly convert varchar to datetime?

Thanks.
johnsql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-23 : 09:25:20

1 yes
2 'Mon yyyy' is always considered as '01-mon-yyyy'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources