SQL Server Performance

sql problems

Discussion in 'General DBA Questions' started by efpit, Jan 17, 2006.

  1. efpit New Member

    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!
  2. ranjitjain New Member

    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. efpit New Member

    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 )
  4. FrankKalis Moderator

    Can you give an example of how the data looks like?
    If you're within the configured "two digit year support" something like this might work


    DECLARE @d VARCHAR(6)
    SET @d = '060101'
    SELECT CAST(@d AS DATETIME)

    At least for men. I don't understand how the data for women should look like.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  5. ranjitjain New Member

    try this:
    3>to convert to datetime

    declare @var1 varchar(6)
    set @var1='060121'
    select convert(datetime,@var1)
  6. ranjitjain New Member

    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
  7. efpit New Member

    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
  8. ranjitjain New Member

    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)
  9. Madhivanan Moderator

    >>the birthdate for women is in the following form eg 016223 instead of 01

    Why does 62 mean?

    Madhivanan

    Failing to plan is Planning to fail
  10. ranjitjain New Member

    <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=':D' />]<br />But No idea why is he doing that[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  11. efpit New Member

    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.
  12. ranjitjain New Member

    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
  13. efpit New Member

    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!!!
  14. ranjitjain New Member

    what is desired date it has to be for 450204, 406009, 190922
  15. Madhivanan Moderator

    I think he wants to get 1945,1940 and 1919



    declare @var1 varchar(6)
    set @var1='456221'
    if cast(left(@var1,2) as int) >=left(year(getdate()),2)
    select convert(datetime,'19'+(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)) as dates

    else
    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)) as dates

    Madhivanan

    Failing to plan is Planning to fail
  16. efpit New Member

    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'.
  17. Adriaan New Member

    Plus you'd better not have people aged over 80 in your data ...
  18. efpit New Member

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

    >>Invalid column name 'birth_number'.

    Make sure you have given correct column name

    Madhivanan

    Failing to plan is Planning to fail
  20. Adriaan New Member

    (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.
  21. efpit New Member

    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.
  22. khtan New Member


    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 <
  23. efpit New Member

    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'

Share This Page