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

    Unhappy Unanswered: Please help me populate my date fields

    Hi,

    I have the following table:

    CREATE TABLE [Orders] (
    [OrdID] [int] ,
    [OrderDate] [datetime] ,
    [OrderDateONLY] [datetime],
    [OrderDayName] [nvarchar] (15),
    [OrderMonth] [nvarchar] (25),
    [OrderDayOfMonth] [smallint] NULL ,
    [OrderWeekofYear] [smallint] NULL

    )
    GO


    The field OrderDate contains BOTH Date and Time information. What I want to do is populate the fields (in bold above) from the OrderDate field... I have tried all that I could think of but nothing works. The last and the best try that I gave was the following but that too does not work. Can you PLEASE help. Many thanks in advance:


    Insert ORDERS
    (OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
    select
    d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
    from
    (select convert (char (8), OrderDate, 112)as d
    from ORDERS
    ) as x

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    update orders set orderdateonly=convert(varchar,orderdate,105),order Dayname=datename(dw,orderdate),orderMonth=datename (m,orderdate),
    orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)

  3. #3
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    populate only orderDate in this



    CREATE TABLE [dbo].[Orders] (
    [OrdID] [int] NULL ,
    [OrderDate] [datetime] NULL ,
    [OrderDateONLY] AS ([orderdate]) ,
    [OrderDayName] AS (datename(weekday,[orderdate])) ,
    [OrderMonth] AS (datename(month,[orderdate]))
    ......
    )

  4. #4
    Join Date
    Oct 2003
    Posts
    163

    Unhappy Almost there but still problem

    harsal_in,

    Thanks for the reply...i think i'm very close now except for one problem...when I run the statement i get an error message saying:

    "The conversion of a char datatype to datetime datatype resulted in an out of range datetime value"

    The problem seems to be with the "orderdateonly=convert(varchar,orderdate,105)" but I can't figure out

  5. #5
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    it works very well on my machine.
    anyways try this:
    update orders set orderdateonly=convert(datetime,convert(varchar,ord erdate,105)),orderDayname=datename(dw,orderdate),o rderMonth=datename (m,orderdate),
    orderDayOfMonth=day(orderdate),orderweekofyear=dat ename(wk,orderdate)

  6. #6
    Join Date
    Oct 2003
    Posts
    163
    harshal_in,

    I wonder what's wrong at my end becuase I am still getting the same error. I have SP1 and could this be the problem? I'll update to SP3 but for now is there a solution.

    Thanks.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Format 120 (or 121 Brett....) are better for date conversion because they are interpreted unambiguously by SQL Server.

    update orders
    set orderdateonly=convert(datetime,convert(char(10),or derdate,120)),
    orderDayname=datename(dw,orderdate),
    orderMonth=datename (m,orderdate),
    orderDayOfMonth=day(orderdate),
    orderweekofyear=datename(wk,orderdate)


    ...but the problem with your design here is that you will need to ensure that anytime the orderdate is modified that all the other columns are updated as well. I recommend that instead of having columns to store these values directly you should create them as calculated fields using the above formulas. This way, they will automatically be synchronized with the orderdate field.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Unless it's a warehouse....or for performance reasons....which I can't see it...

    NEVER store derived data.....

    What's the reason..

    If you store derived data, then you get caught in the trap of making sure the derived data is TRUE to the source...all the time...

    Which means an additional process..

    This is OLTP, right?

    in OLAP, it done once and never changes, so it is TRUE at the time of the derivation

    OLTP is fluid, and alway in a state of flux...(like the capacitor )

    So why?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

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

Posting Permissions

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