Cast , Convert

[Kendel]

Hi all,

Can you help me with this query?

In the table, they have Date_YY, Date_MM, Date_DD as 3 seperate fields. When I do the query, I want to combine these 3 fields as 1 date. So I use:

Select a.field1, a.field2, ...., a.Date_YY + a.Date_MM + a.Date_DD as MyDate
from theTable a

But Date_YY, Date_MM, Date_DD are float data type so instead of 20040725, I will get 2036 (2004+07+25=2036)

So I change my query to

Select a.field1, a.field2, ...., (Cast(a.Date_YY as char(4))+ Cast(a.Date_MM as char(2)) + Cast(a.Date_DD as char(2))) as MyDate
from theTable a

OR

Select a.field1, a.field2, ...., (Convert(char(4),a.Date_YY)+ Convert(char(2),a.Date_MM) + Convert(char(2),a.Date_DD)) as MyDate
from theTable a

But they both get syxtax error.

 

[micha123]

Try without ( ):

Select a.field1, a.field2, ...., 
Convert(char(4),a.Date_YY)+ Convert(char(2),a.Date_MM) + Convert(char(2),a.Date_DD) as MyDate
from theTable a

If not - what kind of error are you getting?

"A long life is when each day is full and every hour wide!"
(A Jewish Mentor)
M.

 

[Kendel]

Thanks. I got same errors:

With Cast:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[CA][Advantage EDBC ODBC Driver][Advantage EDBC]Syntax Error: Found a keyword or token other than that which was expected

With Convert:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[CA][Advantage EDBC ODBC Driver][Advantage EDBC]DCOM ERROR -20 SYNTAX-LAST TOKEN(S): <A.field3, CONVERT(C> DBEXT = , DBINT = 0

 

[SQLBill]

Are you using Microsoft SQL Server?

-SQLBill

Posting advice: FAQ481-4875

 

[Kendel]

No Bill, I'm using DataCom. There is no activity in DataCom forum. I post 1 question there awhile agao and no body answer.

 

[Kendel]

Looks like there is a bug in DataCom CAST function.

Is there a work around? The reason I want to combine 3 field as one is beacuse in my where clause I have a condition as "where MyDate between a date range"

Thanks.

 

[SQLBill]

The only problem with posting your question here is that not all SQL is the same.

The script you posted should work, it's good TSQL syntax. So, 'is there a work around?' has to be answered NO. It works fine, so why would there be a work around.

Does DataCom have a CAST function? Are you using it correctly?

I really suggest Google'ing the web and trying to see if there is another forum for DataCom since the TEK-TIPs one isn't helping you.

-SQLBill

Posting advice: FAQ481-4875

 

[Kendel]

Yes I did google the web and found quite a few articles talking about the CAST problem in DataCom but I didn't see any solution for it yet.

Thanks for you help.

 

[DotNetGnat]

Kendel,

Try using Cast Without Conversion

here is an example:

CAST(CREDITINFO AS NUMERIC(6,0) WITHOUT CONVERSION)

I would also sugges you to post your Datacom related questions in the Datacom forums...may be you have a good chance of getting replies in that forum...

-DNG

 

[Kendel]

Oh, it's you again DNG. Thanks again. OK, let me try that forum again and see if I will get any luck.

I found 1 DataCom forum in UK. After filling out all the info, I got the error "page not found". Tried to contact them and get the "page not found" error again.

 

[Kendel]

Instead of CAST or Convert, can we use Format,CDate, or Date function these fields? What is the syntax for these functions? Thanks.