Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

stored procedure troubles Expand / Collapse
Author
Message
Posted Wednesday, May 8, 2013 8:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
Hello
its my first topic hope i'll get a solution for my troubles

i have some stored procedure and every things its okay when i execute my SP except them who contains conversions from varchar data type to datetime
i try a lot of things like change CONVERT to CAST , DATETIME to DATETIME2 , i add also a style 9 ( because im working with sql server 2012 )

i have 2 errors msg

1 - Conversion failed when converting date and / or time from character string ( this case its a SP with a simple conversion like CONVERT (datetime,column_name ) as column_output )

2 -The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.( in this case i create time dimension with a complex conversions but they dont generate data )



and sorry for my bad english and my low technical knowledge im a newbie in the world of database
Post #1450589
Posted Wednesday, May 8, 2013 8:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 13,193, Visits: 12,677
elmoustabchir (5/8/2013)
Hello
its my first topic hope i'll get a solution for my troubles

i have some stored procedure and every things its okay when i execute my SP except them who contains conversions from varchar data type to datetime
i try a lot of things like change CONVERT to CAST , DATETIME to DATETIME2 , i add also a style 9 ( because im working with sql server 2012 )

i have 2 errors msg

1 - Conversion failed when converting date and / or time from character string ( this case its a SP with a simple conversion like CONVERT (datetime,column_name ) as column_output )

2 -The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.( in this case i create time dimension with a complex conversions but they dont generate data )



and sorry for my bad english and my low technical knowledge im a newbie in the world of database


Hi and welcome to the forums. I would like to help you but you didn't provide any kind of details. It seems that you have some bad data that you are trying to convert to datetime. Can you provide some details about what is happening? ddl (create table scripts), sample data (insert statements) and the code you are running would be a big help.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1450597
Posted Wednesday, May 8, 2013 8:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
thanks for your help
apparently no , i took the DB who contains data as it works on production environemment
but i dont know if they bring me an OLD DB or test DB or thing like this ! im gonna ask them if they verify if its the right DB
i dont have the DB now but once i have it im gonna give you more details

but i have same questions who can appear a little strange

i SP designed on SQL 2005 or 2008 can have compatibilty troubles ?
i have a crazy machine the time a date doesnt move ( pile problems ) and i read somewhere that can be a problem in conversion especially in my case , because i declare some datetime variable

thanks for assistance and im sorry again for my bad english !
Post #1450613
Posted Wednesday, May 8, 2013 8:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:50 AM
Points: 13,991, Visits: 28,373
For error number one, it sounds like you're trying to cast something from a varchar to a date that can't be converted. For example '3 March 2013' can be, but 'Three March 2013' can't. And probably your case is even more extreme. You can try using the ISDATE function to find all the values that are not convertible to datetime. The second error is related. Whatever value is there is outside the accepted range for the data type you're using. Here's some information about the different sizes you can use and how conversions occur.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1450621
Posted Wednesday, May 8, 2013 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
thank you
for my first error , its a column like this : déc 5 2007 12:00AM ( i dont understand why it doesnt work because its pretty simple :s )
for my second im gonna take a look on a script and see what i can do

but i remember you i took the DB source , the SP et DB destination as they works on the production environement !
Post #1450636
Posted Wednesday, May 8, 2013 9:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 13,193, Visits: 12,677
elmoustabchir (5/8/2013)
thank you
for my first error , its a column like this : déc 5 2007 12:00AM ( i dont understand why it doesnt work because its pretty simple :s )
for my second im gonna take a look on a script and see what i can do

but i remember you i took the DB source , the SP et DB destination as they works on the production environement !


Well that won't convert to a datetime because of the "é".

select cast('déc 5 2007 12:00AM' as datetime)

Now if you change that to an "e" it will work just fine.

select cast('dec 5 2007 12:00AM' as datetime)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1450644
Posted Wednesday, May 8, 2013 9:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 5, 2013 4:11 AM
Points: 25, Visits: 105
thank you im gonna try this but infortunaly i can skip this transformation because they dont need it this on reporting
but the table named dimension_time is essential for building a cube & analyse
once i have the scripts im gonna post it here to assist me
thank you to every one
Post #1450653
Posted Wednesday, May 8, 2013 10:30 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
elmoustabchir (5/8/2013)
thank you
for my first error , its a column like this : déc 5 2007 12:00AM ( i dont understand why it doesnt work because its pretty simple :s )
for my second im gonna take a look on a script and see what i can do

but i remember you i took the DB source , the SP et DB destination as they works on the production environement !


Are all your dates in French but default language of database English?
You can using SET LANGUAGE:

SET LANGUAGE French
select cast('déc 5 2007 12:00AM' as datetime)




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1450687
Posted Wednesday, May 8, 2013 10:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:37 AM
Points: 13,193, Visits: 12,677
Eugene Elutin (5/8/2013)
elmoustabchir (5/8/2013)
thank you
for my first error , its a column like this : déc 5 2007 12:00AM ( i dont understand why it doesnt work because its pretty simple :s )
for my second im gonna take a look on a script and see what i can do

but i remember you i took the DB source , the SP et DB destination as they works on the production environement !


Are all your dates in French but default language of database English?
You can using SET LANGUAGE:

SET LANGUAGE French
select cast('déc 5 2007 12:00AM' as datetime)



Thanks Eugene. I was thinking collation. I didn't even think about setting the language.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1450691
Posted Wednesday, May 8, 2013 10:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
Here is a simple example how to deal with case if your date strings do come in different languages. Yes, you will need to use cursor (or loop) in order to process data in different languages separately...

declare @tSrc table (ml_dt varchar(50))
declare @language table (tongue varchar(20))
declare @tDst table (dt datetime)
declare @currentlanguage varchar(50)


INSERT @tSrc VALUES ('déc 5 2007 12:00AM'),('dec 5 2007 12:00AM'),('dez 5 2007 12:00AM')

INSERT @language VALUES ('English'),('French'),('German')

SET @currentlanguage = @@LANGUAGE

DECLARE @tongue VARCHAR(50)

DECLARE lang_c CURSOR FORWARD_ONLY FOR SELECT tongue FROM @language
OPEN lang_c
FETCH NEXT FROM lang_c INTO @tongue
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @tongue
SET LANGUAGE @tongue
INSERT @tDst SELECT CASt(ml_dt AS DATETIME ) FROM @tSrc WHERE ISDATE(ml_dt) =1
SET LANGUAGE @currentlanguage -- otherwise, you will need to specify language in the list in the previous tongue (Deutsch instead of German)
FETCH NEXT FROM lang_c INTO @tongue
END
CLOSE lang_c
DEALLOCATE lang_c

SELECT * FROM @tDst




_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1450697
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse