Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2003
    Posts
    163

    Arrow Unanswered: Want to Concatenate Year and Month to get a Date

    Hi,

    My table contains only the PLAN Year and the PLAN MONTH. What I want is to create a view based on this table which will display a Date as well (despite the fact that date is not stored in the underlying table). The date can be the 1st of the month. I hope the example below will clearly explain my request (I want the 'Derived Date' using the Year and Month)

    I'll appreciate your help.

    Year Month Derived Date
    ---- ------ ------------
    2004 1 01-Jan-2004
    2004 2 01-Feb-2004
    2004 3 01-Mar-2004
    and so on ....


    Many thanks in advance. I'll appreciate your help
    P.S.
    Can someone also help me how to insert TABS in a post. I have tried many spaces but the end result is still not what I wanted... as you can see the 3 columns of my example are kind of overlapping whereas I wanted to clearly separate them

  2. #2
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    try this


    CREATE TABLE [dbo].[tablex] (
    [year] [int] NULL ,
    [month] [int] NULL ,
    [Derived Date] AS ('01' + (case when ([month] = 1) then '-Jan-'
    when ([month] = 2) then '-Feb-'
    when ([month] = 3) then '-Mar-' else 'error'
    end + convert(char(4),[year])))

    )

  3. #3
    Join Date
    Mar 2004
    Location
    West London
    Posts
    34

    Question

    How about something like:

    create view vwtest
    as
    SELECT [year], [month], CONVERT(datetime, '01-' + [month] + '-' + [year], 103) AS Expr1
    FROM dbo.test

  4. #4
    Join Date
    Nov 2002
    Posts
    19

    How to prevent displaying all names of databases

    I get an issue, that is: Once i expand databases from SQL Enterprise Manager, it takes a long time to wait displaying all names of databases. How should i do to prevent displaying all names of databases?

  5. #5
    Join Date
    Oct 2003
    Posts
    163

    Unhappy

    Thanks for your replies.

    SQLSurfer: Both my YEAR and MONTH fields are smallint and when I try to execute your sugestion I get an error message saying

    "Syntax error converting the varchar value '01-' to a column of datatype smallint"

    Any clues?

  6. #6
    Join Date
    Mar 2004
    Location
    West London
    Posts
    34
    You can change the column data types to char(4),char(2) respectively. Is that an option?

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    select dateadd(mm,month,dateadd(yy,(year-1900),'19000101') from yourtable
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Oct 2003
    Posts
    163

    Thumbs up

    Thanks all for your replies.

    Unfortunately I am getting errors/incorrect results with all of the above.

    However with help, the problem has been solved and just wanted to share with you. Many sincere thanks for your time and help.

    CONVERT(datetime, CONVERT(varchar(4), [Year]) + RIGHT('0' + CONVERT(varchar(2), [Month]), 2) + '01', 112) AS DerivedDate

    However whenever and IFF I get time, I do need to get a GOOD hang of all the flavours that I may run into while using the DATE function. I tell you, at least I find this one of the trickest areas

    Sincere Regards abd thanks again for your help and time.

    - J

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •