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:
(OrderDateONLY, OrderDayName, OrderMonth, OrderDayOfMonth, OrderWeekofYear)
d, datename (dw, d), datename (mm, d), Year (d), datepart (ww, d)
(select convert (char (8), OrderDate, 112)as d
) as x
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),
Format 120 (or 121 Brett....) are better for date conversion because they are interpreted unambiguously by SQL Server.
set orderdateonly=convert(datetime,convert(char(10),or derdate,120)),
...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.