GetDate conversion 1

[Naith]

Hey there,

I'm sure this shouldn't be tough, but I think I'm a bit too close to see the wood for the trees today.

I'm populating Table1 from Table2. There's a date column in 2 but not in 1, so the end of the statement looks like this:

...
convert(int,replace("intran ttl adjd",',','')),
"fcst stk si qty",
"fcst cs si qty",
"fcst wk si qty", --last Table1 column
convert(varchar, GETDATE(), 103)
FROM Table1

I'm getting:
Server: Msg 242, Level 16, State 3, Line 15
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

I can't think why this would occur, other than it's baulking because there's no corresponding column in the source table. But I'd rather avoid tacking a job at the end of this one to go through all the inserted records and updating them with the sysdate separately.

Any ideas?

 

[Naith]

Sorry, the introduction isn't clear. Table1 is the source, and Table2 is the target.

 

[mreigler]

My guess is you haven't qualified the size of the varchar in your convert function.

Mike Reigler
Melange Computer Services, Inc

 

[druer]

I'm guessing that your table 2 structure doesn't match what you are inputting, and that one of the "fcst.." values is going into the date column and not your convert(getdate())

Hope it helps,
Dalton

 

[Naith]

Thanks for your responses.

Not sure I get what you mean mreigler, as size qualification isn't necessary for this implicit conversion. Maybe I'm misunderstanding what you meant.

Druer, without the getdate() functionality, the insert works fine.

The only thing that's changed is:

...
fcst_cs_si_qty,
fcst_wk_si_qty,
mdate) --adding the date column at the end of the insert
SELECT
cast("sap sales org id" as smallint)...

and adding the convert at the end of the select clause.

So, as far as I can see, the ordering of the data is ok. The source table has 1 less columns than the target, so, realistically, as I'm not padding the insert out, it shouldn't be possible for a source value to mistakenly populate the last column of the target table.

 

[mreigler]

That will teach me not to check my answer.

If your column in table2 is a datetime then why are you converting to a vachar. The conversion back to a datetime is when you get the error because of the 103 format.

For example:
works
print convert(datetime,convert(varchar, GETDATE(), 103), 103)

Doesn't work (out of range error)
print convert(datetime,convert(varchar, GETDATE(), 103))

But again, why convert to a varchar in the first place.

Mike Reigler
Melange Computer Services, Inc

 

[jegaby]

Take a close look at the error message. It says you are trying to insert a charter string into a datetime column. What you want to do is insert a datetime into the datetime column. So simply remove the conversion:

...
convert(int,replace("intran ttl adjd",',','')),
"fcst stk si qty",
"fcst cs si qty",
"fcst wk si qty", --last Table1 column
GETDATE()
FROM Table1



Note: The code you have would have worked if you had omitted the "103" format specification. The problem is that specification formatsthe date string to be day/month/year, but apparently your server tried to interpret the date as month/day/year and so found an illegal date. However the conversion is just extra unneeded work.

 

[Naith]

Of course you're right, guys. I guess I needed to be more specific in setting the scene. The reason I have the conversion in there is because I only want to capture the date portion of the current date. The time needs to be disregarded.

The 103 was to force dd/mm/yy, as that's how we read dates here in the UK. As jegaby rightly pointed out, the system was expecting mm/dd/yy, so 101 gets by without any problem.

Is it possible to override this to obtain a dd/mm/yy format though?

Cheers.

 

[vongrunt]

Use 112 instead - this in language-neutral format.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Naith]

I guess that's the workaround to use. Thanks.