sql problems

Hello, i face 3 problems

1) cannot get the data i have used in enterprise manager in the analysis manager. I have followed by word the directions in the analysis manager but i cannot connect sql server with analysis manager. And the problem is not only with my database. The same goes for the FoodMart 2000 example as well.

2) I cannot execute even the simplest querry. The error message is the same no matter what table i use, for example:
select *
from account
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'account'.



3) The birthdates in a table are in the following form:
yymmdd for men
yy(mm+50)dd for women
How can I convert these values to datetime data types?

Looking forward to your comments!

1>How r u connecting AS in SQL Server or viceversa.
What exact problem u face while browsing Foodmart 2000.

2>Where have u tried the query.
Is it in QA then try databasename..tablename

3>What is the current datatype u have set to store the values

3) the current datatype is varchar
1)although i follow the directions (in the analysis manager there are some directions "Get started" ->odbc -> system dsn etc) by letter and the procedure is completed successfuly, when i open the analysis manager the FoodMart doesn't appear. (In the directions i am supposed to see the Foodmart MS Access file )

try this:
3>to convert to datetime

declare @var1 varchar(6)
set @var1='060121'
select convert(datetime,@var1)

In AS first of all you need to create a new database and then you need to create a new data source.
So by using that data source database connects to your OLTP DB

the birthdate for women is in the following form eg 016223 instead of 011223. Since the data for both male and female are in the same file i cannot process only some of them

quote:Originally posted by efpit

the birthdate for women is in the following form eg 016223 instead of 011223. Since the data for both male and female are in the same file i cannot process only some of them

try this for women

declare @var1 varchar(6)
set @var1='066221'
select @var1= STUFF(@var1, 3, 2, substring(@var1,3,2)-50)
select convert(datetime,@var1)

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Madhivanan</i><br /><br />&gt;&gt;the birthdate for women is in the following form eg 016223 instead of 01<br />Why does 62 mean?<br />Madhivanan<br />Failing to plan is Planning to fail<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Hi Madhi,<br />Actually i guess he is adding 50 months while storing the date value for women.[<img src='/community/emoticons/emotion-2.gif' alt='' />]<br />But No idea why is he doing that[<img src='/community/emoticons/emotion-1.gif' alt='' />]

in the month part of the date the number 50 is added eg. the month May =05 is symbolized with the number 55(50+05) etc. i need a way to read one by one the values of the column that entails the birthdates and then if the 3rd digit is 5 or 6 to subtrack 5 from it. In any other case the value should not be changed.
The result from executing
declare @var1 varchar(6)
set @var1='066221'
select @var1= STUFF(@var1, 3, 2, substring(@var1,3,2)-50)
select convert(datetime,@var1)

was: 'varchar' is not a recognized CURSOR option.

ps I was given the data that way, so i really can't give an answer to why this modification was made.

try this:
declare @var1 varchar(6)
set @var1='066221'

select convert(datetime,(case substring(@var1,3,1) when 6 then STUFF(@var1, 3, 1, substring(@var1,3,1)-5)
when 5 then STUFF(@var1, 3, 1, substring(@var1,3,1)-5)
else @var1 end))


don't use any cursor and replace @var1 with your column name and then do select from tablename

what you suggested works if a leave out the following:
declare @var1 varchar(6)
set @var1='066221'

although it converts the month correctly there seems to be a problem in the year part. eg the date 706213 is correctly converted to 1970 12 13 but
the dates 450204, 406009, 190922... are depicted as
2045 02 04
2040 06 09
2019 09 22
This happens only to some of the dates. What do you think could create this problem?

ps: THANKS!!!

what is desired date it has to be for 450204, 406009, 190922

No, this doesn't work at all, Madhivanan. There seems to be a problem in the first line

if cast(left(birth_number,2) as int) >=left(year(getdate()),2)
select convert(datetime,'19'+(case substring(birth_number,3,1)
when 6 then STUFF(birth_number, 3, 1, substring(birth_number,3,1)-5)
when 5 then STUFF(birth_number, 3, 1, substring(birth_number,3,1)-5)

else birth_number end)) as dates
from client
else
select convert(datetime,(case substring(birth_number,3,1)
when 6 then STUFF(birth_number, 3, 1, substring(birth_number,3,1)-5)
when 5 then STUFF(birth_number, 3, 1, substring(birth_number,3,1)-5)
else birth_number end)) as dates
from client

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'birth_number'.

Plus you'd better not have people aged over 80 in your data ...

since these birthdates respond to people that have bank accounts i don't understand your point

(1) There are still people around who were born before 1900, and who do have bank accounts.

(2) When a date is stored with two digits for the year, it means you're depending on the software and/or the OS to make assumptions. Usually there's a breakoff point: for instance 30 is interpreted as 2030, and 31 as 1931.

(3) There are lots of people around who were born before 1930, and who do have bank accounts.

Remember the Y2K bug?

Make sure you know what assumptions are made for the source data.

mandhivanan, unfortunately, the column name is correct.
if i remove the "from ..." then the error message appears as many times as i have used the column name.

Madhivana's uses a variable there not a column name

quote:Originally posted by Madhivanan

declare @var1 varchar(6)
set @var1='456221'
if cast(left(@var1,2) as int) >=left(year(getdate()),2)

What is your variable name ? @birth_number ?

if cast(left(@birth_number,2) as int) >=left(year(getdate()),2)


------
> KH <

i don't use a variable name at all. if i use a variable name then other error messages occur. Madhivana's proposal:

select convert(datetime,(case substring(@var1,3,1) when 6 then STUFF(@var1, 3, 1, substring(@var1,3,1)-5)
when 5 then STUFF(@var1, 3, 1, substring(@var1,3,1)-5)
else @var1 end))

works only if i leave out the following:
declare @var1 varchar(6)
set @var1='066221'