August 29, 2007 at 8:22 am
I know this is simple to you who have been around, but it is driving me buggy. I have tried a number of things
Here is my varchar input:
Date_Created Time_Creat
20070606 122056
20070606 122135
20070606 122211
20070606 122249
Here is my code: cast(stuff(stuff(Date_Created,5,0,'/'),8,0,'/') + ' ' + stuff(stuff(Time_Creat,3,0,':'),6,0,':') as datetime)
Here is my error message: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Tom Adams
August 29, 2007 at 8:34 am
Hi there,
Please could you give the full table structure, when I use your code on my talbe with bother fields as varchar(100) this all seems to work 100%
I'm using SQL2005
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 29, 2007 at 8:41 am
CAST(STR(Date_Created) + ' ' + STUFF(STUFF(CONVERT(CHAR(6), Time_Create)), 5, 0, ':'), 3, 0, ':') AS DATETIME)
N 56°04'39.16"
E 12°55'05.25"
August 29, 2007 at 8:45 am
thanks for your prompt replies
Chris - Date_Created varchar 50 and Time_Creat varchar 50
Peter, I get this for an error message when I run your version:
Server: Msg 174, Level 15, State 1, Line 1
The stuff function requires 4 arguments.
Tom Adams
August 29, 2007 at 8:48 am
HI Tom,
I used this code here and everything works fine.
Please could you try it yourself.
CREATE
TABLE Temp
(
Date_Created
VARCHAR(50)
,
Time_Creat VARCHAR(50)
)
INSERT
INTO Temp
SELECT
'20070606','122249'
UNION
SELECT
'20070606 ', '122056'
UNION
SELECT
'20070606', '122135'
UNION
SELECT
'20070606', '122211'
UNION
SELECT
'20070606', '122249'
SELECT
cast(stuff(stuff(Date_Created,5,0,'/'),8,0,'/') + ' ' + stuff(stuff(Time_Creat,3,0,':'),6,0,':') as datetime)
FROM
temp
Thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 29, 2007 at 8:52 am
Chris,
I don't know if I can. I have this in the middle of my query for my DTS. I have imported a BW table directly to an interim table in SQL (version 8) and now I am trying to convert the datatypes into what I need in my Cognos application.
As you can tell I am really new at this stuff (but loving it!!)
thanks
Tom Adams
August 29, 2007 at 8:56 am
Hi Tom,
Great to hear you loving it 🙂
Are you sure the error is at this point in your code?
Perhaps you could send me some more code to read through
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 29, 2007 at 9:00 am
Here is the whole ball of wax, the error messages are from query analyzer where I just segment out the portion that I show you:
SELECT Return_No, Serial_No, Model_No, Combo_No, RTRIM(LTRIM(Cust_Cmt1)) + RTRIM(LTRIM(CustCmt1A)) + RTRIM(LTRIM(Cust_Cmt2)) + RTRIM(LTRIM(Cust_Cmt2A)) AS Cust_Complaint, Use_code, Created_By, RTRIM(LTRIM(Missing_Comp)) + RTRIM(LTRIM(Missing_Comp2)) AS Mis_Comp, Store_No, Defect_Type_No, Date_Return, CONVERT(char(14), Date_Created + Time_Creat, 120) AS DateStamp,
RTRIM(LTRIM(Def_Desc_1)) + RTRIM(LTRIM(Def_Desc_1A)) + RTRIM(LTRIM(Def_Desc_2)) + RTRIM(LTRIM(Def_Desc_2A)) AS Def_Desc, RPM_WOT, RPM_Idle, Build_Date, Loc_Code, Start, Num_Tries, Blade_Cond, RTRIM(LTRIM(Def_Part)) + RTRIM(LTRIM(Def_Part2)) AS Def_Part, Scrapped, Defect_Type, Defect_Detail, RGA_No
FROM [Quality Analysis].dbo.RETURNS_ANALYZED_INTERIM
Thanks for taking this much time with my little problem
Tom Adams
August 29, 2007 at 9:18 am
HI Tom,
I am confused.
This orginal line code is not in the last section of code you sent me...
Thanks
chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 29, 2007 at 9:23 am
Yes,
I highlighted it here for you. This is one of my many and varied attempts to get something to work. I have learned that the 'char(14)' should be datetime to make my convert statement work (in theory).
SELECT Return_No, Serial_No, Model_No, Combo_No, RTRIM(LTRIM(Cust_Cmt1)) + RTRIM(LTRIM(CustCmt1A)) + RTRIM(LTRIM(Cust_Cmt2)) + RTRIM(LTRIM(Cust_Cmt2A)) AS Cust_Complaint, Use_code, Created_By, RTRIM(LTRIM(Missing_Comp)) + RTRIM(LTRIM(Missing_Comp2)) AS Mis_Comp, Store_No, Defect_Type_No, Date_Return, CONVERT(char(14), Date_Created + Time_Creat, 120) AS DateStamp,
RTRIM(LTRIM(Def_Desc_1)) + RTRIM(LTRIM(Def_Desc_1A)) + RTRIM(LTRIM(Def_Desc_2)) + RTRIM(LTRIM(Def_Desc_2A)) AS Def_Desc, RPM_WOT, RPM_Idle, Build_Date, Loc_Code, Start, Num_Tries, Blade_Cond, RTRIM(LTRIM(Def_Part)) + RTRIM(LTRIM(Def_Part2)) AS Def_Part, Scrapped, Defect_Type, Defect_Detail, RGA_No
FROM [Quality Analysis].dbo.RETURNS_ANALYZED_INTERIM
Tom Adams
August 30, 2007 at 12:57 am
HI Tom,
Sorry for the delay.
Could you try replacing the highlighted section with the following.
cast(stuff(stuff(Date_Created,5,0,'/'),8,0,'/') + ' ' + stuff(stuff(Time_Creat,3,0,':'),6,0,':') as datetime)
thanks
Chris
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
August 30, 2007 at 2:06 pm
What do the following statements return for you?
SELECT
Max(Len(Date_Created)) FROM tablenamehere
SELECT
Max(Date_Created) FROM tablenamehere
SELECT
Max(Len(Time_Creat)) FROM tablenamehere
SELECT
Max(Time_Creat) FROM tablenamehere
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply