SQL Server Performance

sql server date conversion

Discussion in 'T-SQL Performance Tuning for Developers' started by mntaneja, Aug 8, 2005.

  1. mntaneja New Member

    Hi All,

    Is there a way in sql server to combine two columns of a table, month and year and get "date" so that the resultant date can be compared in where clause.[:I]

    Thanks

    M N taneja
  2. FrankKalis Moderator

    See if this help:<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a><br />If not, please post more information about what you're trying to do. An example then would be fine. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  3. mntaneja New Member

    Thanks for such prompt reply,
    And sorry , i cudnt get any help from the topic referred.
    what I want is, that in my table I have month column and year column, But now I want an (earlier) month(/date) less than the current month for which data is there in the table.
    eg if data exist for the June 2005, then in the entry form of August, June's data shud come, which I can further update.

    M N Taneja
  4. ranjitjain New Member

    Hi,
    Do you want something:
    Select monthCol+YearCol as monthyear from table
  5. ranjitjain New Member

    or try this:

    declare @table1 table(monthcol varchar(2),yearcol varchar(4))
    insert @table1 values('8','2005')
    insert @table1 values('7','2005')
    insert @table1 values('6','2005')
    select monthcol+yearcol reqdate from @table1
    where monthcol+yearcol=cast(datepart(m,getdate())as varchar(2))+cast(datepart(yy,getdate())as varchar(4))
  6. FrankKalis Moderator

  7. Madhivanan Moderator

    >>Is there a way in sql server to combine two columns of a table, month and year and get "date"

    Cant you use this?

    Select columns from yourtable
    where month(datecol)=monthvalue and year(datecol)=yearvalue


    Madhivanan

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

    quote:Originally posted by mntaneja

    Hi All,

    Is there a way in sql server to combine two columns of a table, month and year and get "date" so that the resultant date can be compared in where clause.[:I]

    Thanks

    M N taneja
    The big question here is - so that the resultant date can be compared in where clause - ... well, can be compared to WHAT?
  9. mntaneja New Member

    Thank u all,

    -Frank - I tried urs one as
    "SELECT max(month_code) FROM storagecap WHERE [year]*10000+month_code*100+1<'20051001'"

    but I m afraid to say that its not giving the desired or say right result.

    well here my client asks me to fill the entry screen with the previous available data lying in table so that he just make few changes in the data and insert it as new set of data for the current month, eg if data for say June 2005 is there,and i have to input data for Aug 2005, then data for month June 2005 shud come and not June 2004.Structure of table is
    -MonthCode
    -Year
    -Amount

    -Adriaan ,Comapre it with date
    -Madhivanan, there is month column and year column and not date column
    - ranjitjain, no, its giving simple sum of two


    M N Taneja
  10. Madhivanan Moderator

    I meant

    Select columns from yourtable
    where month('20050601')=monthvalue and year('20050601')=yearvalue



    Madhivanan

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

    Okay, so you basically need the most recent month for which there is data in your table (but not the current month).

    Assuming your MonthCode and Year columns are numeric, this is the query:

    SELECT TOP 1 t1.*
    FROM MyTable t1
    WHERE ((t1.Year * 100) + t1.MonthCode) < ((YEAR(GETDATE()) * 100) + MONTH(GETDATE()))
    ORDER BY t1.Year DESC, t1.MonthCode DESC
  12. FrankKalis Moderator

    Why are you insistently refusing to give some sample data and the required output? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />You say you want June data to be inserted as August data. What about July? Can we assume that it always the penultimate month or as Adriaan said should that be the most recent month for which data exists? Next you say you want to compare with date. I see no DATETIME column in your table structure.<br /><br />I'm sure, your problem could have been solved hours ago if only you had provided the necessary information.<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  13. mntaneja New Member

    Madhivanan, first I have to find the "last" month , here in this case "June 2005"
  14. Madhivanan Moderator

    Post some sample data with expected result
    That would be really helpful


    Madhivanan

    Failing to plan is Planning to fail
  15. mntaneja New Member

    say I have entry screen with month , year and amount1, amount2, amount3......amount100.
    Now user wants that entry screen shud come up with last available data. My query is to find that "last month" for which data is there in the table.
  16. Madhivanan Moderator

    Not sure whether this is correct until you post some sample data


    Select t1.* from yourtable t1 inner
    join (
    select Top 1 max(monthcode) as mon,yearcode from yourtable group by yearcode
    order by yearcode desc ) T2
    on t1.monthcode =t2.mon and t1.yearcode =t2.yearcode




    Madhivanan

    Failing to plan is Planning to fail
  17. mntaneja New Member

    Hi All, [<img src='/community/emoticons/emotion-6.gif' alt=':(' />] <br />I dont know how to upload my sample data.Let me try this way.<br /><br />The table design: <br />ST_CODE int41<br />WareHousesmallint 21<br />Month_Codesmallint21<br />[Year]smallint21<br />NoOfWHS float81<br />CovOwn float81<br />CovHirfloat81<br />CapOwn float81<br />CapHirfloat81<br />PerUtilfloat81<br /><br />(yes Frank there is no datetime column, otherwise I had simply compared the date with the current date)<br /><br />Now say Data is there for the month <br />5,2004<br />8,2004<br />10,2004<br />12,2004<br />1,2005<br />5,2005<br /><br />And now the want to user to insert data for June 2005(although the month these days is Aug 2005), and he want that data of month May2005(5,2005) shud appear in entry screen which he can update and later we insert it as new record set for June 2005.So My query is to find the max month in table but less than the user inputed month (i.e June in this case)<br /><br />M N taneja
  18. Madhivanan Moderator

    Try this



    Select t1.* from yourtable t1 inner
    join (
    select Top 1 max(monthcode) as mon,yearcode from yourtable
    where monthcode<yourmonthcode group by yearcode
    order by yearcode desc ) T2
    on t1.monthcode =t2.mon and t1.yearcode =t2.yearcode


    Madhivanan

    Failing to plan is Planning to fail
  19. mntaneja New Member

    Madhivanan
    what about the year!!!
    It shud be month+Year I mean for June and 2005 it shud be "June,2005"
    and month and year cannot be compared separately

    and care also needed for month of January where month december(12) wud be greater than January.
  20. Madhivanan Moderator

    Then use

    where monthcode<yourmonthcode and yearcode=youryearcode in the above query


    Madhivanan

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

    quote:Originally posted by mntaneja

    Madhivanan
    what about the year!!!
    It shud be month+Year I mean for June and 2005 it shud be "June,2005"
    and month and year cannot be compared separately

    and care also needed for month of January where month december(12) wud be greater than January.

    mntaneja,

    Seems like you did not even bother to test my query, which does take into account both the year and the month.[xx(]

    Here we go again:

    SELECT TOP 1 t1.*
    FROM MyTable t1
    WHERE ((t1.Year * 100) + t1.MonthCode) < ((YEAR(GETDATE()) * 100) + MONTH(GETDATE()))
    ORDER BY t1.Year DESC, t1.MonthCode DESC
  22. mntaneja New Member

    No Adriaan, I have tried ur query as well but sorry
  23. Madhivanan Moderator

    What about my query?

    You are not giving proper reply whenever you reply

    If that query doesnot work, you should give the reason
    Did you get any error or did you get no results?


    Madhivanan

    Failing to plan is Planning to fail
  24. Adriaan 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 mntaneja</i><br /><br />No Adriaan, I have tried ur query as well but sorry<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />If you want us to help you then you must be clear about exactly what is wrong with the results from the solutions we're offering.[<img src='/community/emoticons/emotion-6.gif' alt=':(' />!][<img src='/community/emoticons/emotion-6.gif' alt=':(' />][xx(]<br /><br />Give us some data that is in your table (all relevant columns) then tell us which part of that data you want returned by the query.
  25. mntaneja New Member

    Adiraan

    “
    SELECT TOP 1 t1.*
    FROM MyTable t1
    WHERE ((t1.Year * 100) + t1.MonthCode) < ((YEAR(GETDATE()) * 100) + MONTH(GETDATE()))
    ORDER BY t1.Year DESC, t1.MonthCode DESC”

    In this query we are not able to compare it with the user given month and year

    Madhivanan

    “where monthcode<yourmonthcode and yearcode=youryearcode” ---------

    Say my user ask for January in that case year which wud be dec of last year wud not be equal to userYear,eg he want jto input data for jan 2005 then I will need Dec 2004 data .
  26. Adriaan New Member

    quote:Originally posted by mntaneja

    Adiraan

    “
    SELECT TOP 1 t1.*
    FROM MyTable t1
    WHERE ((t1.Year * 100) + t1.MonthCode) < ((YEAR(GETDATE()) * 100) + MONTH(GETDATE()))
    ORDER BY t1.Year DESC, t1.MonthCode DESC”

    In this query we are not able to compare it with the user given month and year

    Madhivanan

    “where monthcode<yourmonthcode and yearcode=youryearcode” ---------

    Say my user ask for January in that case year which wud be dec of last year wud not be equal to userYear,eg he want jto input data for jan 2005 then I will need Dec 2004 data .

    Okay, now I see your point. Here's a simple stored procedure that would do that for you:


    CREATE PROCEDURE dbo.MyProc
    (@Month SMALLINT, @Year SMALLINT)
    SELECT TOP 1 t1.*
    FROM MyTable t1
    WHERE ((t1.Year * 100) + t1.MonthCode) < ((@Year * 100) + @Month))
    ORDER BY t1.Year DESC, t1.MonthCode DESC
  27. mntaneja New Member

    yes Adriaan<br /> its working<br /> Thanks a lot [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />And does anyone know any site where i can find little advanced sample sql queries to be solved to improve my tsql.<br />

Share This Page