May 8, 2013 at 8:09 am
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
May 8, 2013 at 8:17 am
elmoustabchir (5/8/2013)
Helloits 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2013 at 8:35 am
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 !
May 8, 2013 at 8:52 am
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2013 at 9:10 am
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 !
May 8, 2013 at 9:18 am
elmoustabchir (5/8/2013)
thank youfor 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2013 at 9:34 am
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
May 8, 2013 at 10:30 am
elmoustabchir (5/8/2013)
thank youfor 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)
May 8, 2013 at 10:33 am
Eugene Elutin (5/8/2013)
elmoustabchir (5/8/2013)
thank youfor 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 8, 2013 at 10:56 am
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
May 9, 2013 at 2:51 am
Hello im back and hope i will be comprehensible ( i dont know it exist in english 😀 )
so im gonna post all related script to my troubles ( table and column sources , PS
and table and column destination )
for my first trouble THIS IS THE RELATED SCRIPT
- Table(column)source :
USE [DB_SOURCE]
CREATE TABLE [AGE].[MATERIEL]
([Datach] [nvarchar](255) NULL)
ON [PRIMARY]
GO
- Stored procedure :
CREATE PROCEDURE [dbo].[PS_DIM_MATERIEL]
AS
BEGIN
Truncate table DWHPROD.dbo.DIM_MATERIEL
INSERT INTO DWPROD.dbo.DIM_MATERIEL
(DATE_ACHAT_MATERIEL)
SELECT
CONVERT (datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,
FROM
DB_SOURCE.AGE.MATERIEL
END
- Table (column)destination:
CREATE TABLE [dbo].[DIM_MATERIEL]([DATE_ACHAT_MATERIEL] [datetime] NULL,)
ON [PRIMARY]
GO
I HAVE SOME ROWS WHO HAVE A null values in column Datach
so it can generate an error due a conversion from nvarchar null to datetime null ?
May 9, 2013 at 2:58 am
Have you had a chance to read all responses made to your thread so far?
What exactly problem you are facing now?
The reason why some dates can not be converted from character representation to datetime is use of foreign languages, eg. your example of date which failed to be converted is in French. In order to convert this date SQL should be switched to use French language.
If you have more than one language used, you will need to convert dates for each language separately, check my last post, shows exactly how it can be achieved.
And the last one: NULL string will be converted to NULL datetime without any problems.
May 9, 2013 at 3:30 am
thanks eugene no i have just one language
first im gonna back up my DW because i made a lot of modifications especially to ignore conversion to datetime because its the only type who stop normal execution of my SP
and im gonna try to set language to french like you teach me now
then i return to you if it doesnt works
May 9, 2013 at 4:14 am
i typed back up my DW and i do it , but i meant restore my DW :w00t:
the coffee machine is out service this week and i cant concentrate on my works adding my low technical skills i think i can do a big disaster here if they give acces and authorizations to production environement !
so i set the language on the SP exact ?
like this :
SET LANGUAGE French
SELECT CONVERT (datetime,MATERIEL.Datach) AS DATE_ACHAT_MATERIEL,
when i do it they give me this message :
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 35
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Procedure PS_DIM_MATERIEL, Line 36
Incorrect syntax near the keyword 'CONVERT'.
May 9, 2013 at 4:56 am
Please post complete query, as it is impossible to see what is wrong just from two lines.
BTW, your second line finishes with comma. If nothing follows it, it will generate an error.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply