SQL and Date maniplutation

  • Thread starter Thread starter Stephen
  • Start date Start date
S

Stephen

I have a SQL query in MS Project. One of the fields is the time of day
(11:00 AM) of an order. Another field is the time shift that I need to
show.

I am trying to determine the syntax in SQL that will give me the effective
"time shifted" time of day.

Example: 11:00 AM - 1 = 10:00 AM.

I have tried: dbo.stblOrders.DeliveryTime -
dbo.stblDefaults.DefaultPreparationLead, but this gives me 12/31/1899
11:00:00 AM


Help?
 
Date/Time fields actually contain "special" floating point numbers, in
which the whole number part (probably 0 in this case) encodes the date
in days after midnight on 12/31/1899 and the fractional part encodes
the time in fractions of a day after midnight. If you want to subtract
N hours from a time contained in a date/time field or variable, you
have to subtract N/24 from it, or use DateAdd with a negative
argument, e.g.:

dtNewTime = DateAdd("h", -N, dtOldTime)

I have a SQL query in MS Project. One of the fields is the time of day
(11:00 AM) of an order. Another field is the time shift that I need to
show.

I am trying to determine the syntax in SQL that will give me the effective
"time shifted" time of day.

Example: 11:00 AM - 1 = 10:00 AM.

I have tried: dbo.stblOrders.DeliveryTime -
dbo.stblDefaults.DefaultPreparationLead, but this gives me 12/31/1899
11:00:00 AM


Help?
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top