August 19, 2011 at 9:39 am
The "FlightDate" (e.g. CONVERT(VARCHAR(10), f.FlightDate, 101)) in the T-SQL below and needs to be revised to local Hawaiiain time zone and I'm not sure how to do this. PLEASE HELP!!! 😀
DECLARE @DATStart DATETIME
DECLARE @DATEnd DATETIME
DECLARE @DROMerchantID INT
-- For Crystal Report - Begin
SET @DATStart = {?DATStart}
SET @DATEnd = {?DATEnd}
SET @DROMerchantID = {?DROMerchantID}
-- For Crystal Report - END
-- For Debugging - Begin
--SET @DATStart = '06/01/2011'
--SET @DATEnd = '06/30/2011'
--SET @DROMerchantID = 1
-- For Debugging - END
IF OBJECT_ID('tempdb..#T1') IS NOT NULL
begin
drop table #T1
end
IF OBJECT_ID('tempdb..#T2') IS NOT NULL
begin
drop table #T1
end
IF OBJECT_ID('tempdb..#T3`') IS NOT NULL
begin
drop table #T1
end
create table #T1(FlightID bigint,[Card] varchar(50),Cat_Amount decimal(18,2),CategoryID int,Category nvarchar(100))
create table #T2(FlightID bigint,[Card] varchar(50),Cat_Amount decimal(18,2),CategoryID int,Category nvarchar(100))
create table #T3(FlightID bigint,FlightNum nvarchar(50),OriginatingAirport nvarchar(10),FlightDate varchar(10),
[Card] varchar(50),[Bundle] decimal(18,2),[Game] decimal(18,2),[Audio] decimal(18,2),[Video] decimal(18,2),Total decimal(18,2),CompanyName nvarchar(100))
INSERT INTO #T1
SELECT
t.FlightID,
ct.CardTypeNameAS Card,
SUM(ti.Price * ti.Qty) AS Cat_Amount,
c.CategoryID,
c.Name AS Category
FROM [Transaction] t WITH(nolock)
JOIN TransactionItem ti WITH(nolock) ON ti.TransactionID = t.TransactionID
JOIN TransactionType tt WITH (nolock) ON tt.TypeID = t.TypeID
and tt.TypeName IN ('Charge')
JOIN TransactionCreditCard tcc WITH(nolock) ON tcc.TransactionID = t.TransactionID
JOIN CardType ct WITH (nolock) ON ct.CardTypeID = tcc.CardTypeID
JOIN Item i WITH (nolock) ON i.ItemID = ti.ItemID
JOIN Category c WITH(nolock) ON c.CategoryID = i.CategoryID
WHERE t.Voided = 0
and c.Name IN ('Bundle', 'Game', 'Audio', 'Video')
GROUP BY ct.CardTypeName, t.FlightID, c.Name, c.CategoryID
INSERT INTO #T2
SELECT
#T1.FlightID,
#T1.Card,
SUM(#T1.Cat_Amount)AS Cat_Amount,
#T1.CategoryID,
#T1.Category
FROM #T1
GROUP BY #T1.FlightID, #T1.Card, #T1.CategoryID, #T1.Category
INSERT INTO #T3
SELECT
f.FlightID,
f.FlightNum,
f.DepartureAirport AS OriginatingAirport,
CONVERT(VARCHAR(10), f.FlightDate, 101) AS FlightDate,
#T2.Card,
SUM(CASE WHEN #T2.Category = 'Bundle' THEN Cat_Amount END ) AS [Bundle],
SUM(CASE WHEN #T2.Category = 'Game' THEN Cat_Amount END )AS [Game],
SUM(CASE WHEN #T2.Category = 'Audio' THEN Cat_Amount END )AS [Audio],
SUM(CASE WHEN #T2.Category = 'Video' THEN Cat_Amount END )AS [Video],
SUM(#T2.Cat_Amount) AS Total,
c.CompanyName
FROM Flight f WITH (nolock)
JOIN #T2 ON #T2.FlightID = f.FlightID
JOIN Company c WITH (nolock) ON c.CompanyID = f.AirlineID
WHERE f.FlightDate >= @DATStart
and f.FlightDate < @DATEnd + 1
and c.CompanyID = @DROMerchantID
GROUP BY f.FlightID, f.FlightNum, f.DepartureAirport, CONVERT(VARCHAR(10), f.FlightDate, 101), T2.Card, c.CompanyName
SELECT
FlightID,
FlightNum,
OriginatingAirport,
FlightDate,
[Card],
CASE WHEN [Bundle]is null THEN 0 ELSE [Bundle] END AS [Bundle],
CASE WHEN [Game] is null THEN 0 ELSE [Game] END AS [Game],
CASE WHEN [Audio] is null THEN 0 ELSE [Audio] END AS [Audio],
CASE WHEN [Video] is null THEN 0 ELSE [Video] END AS [Video],
[Total],
CompanyName
FROM #T3
ORDER BY FlightDate, FlightNum, OriginatingAirport, Card
drop table #T1
drop table #T2
drop table #T3
August 19, 2011 at 9:50 am
Usually localisation should be performed on the client and not on the server.
Do you store your datetime in UTC? You will need to use DATEADD function and apply required time difference, you may need to introduce "time saving" logic if Hawaii do have it.
August 19, 2011 at 9:58 am
The Crystal Report server and the Database server are both in (Central Standard Time), but I keep hearing the developers and manager talk about the data we receive in the xml files being in UTC format so I'm confused a little bit. The xml's are in UTC but when the data is saved to the db I'm believed it's committed in CST time zone. The SQL in the Crystal Report is using a CONVERT on the"FlightDate" and "101" which U.S. format which is confusing me more. I just need to have the logic of the report back on the FlightDate for Hawaiian time zone. Does that help or not? :w00t:
August 19, 2011 at 10:16 am
davidsalazar01 (8/19/2011)
The Crystal Report server and the Database server are both in (Central Standard Time), but I keep hearing the developers and manager talk about the data we receive in the xml files being in UTC format so I'm confused a little bit. The xml's are in UTC but when the data is saved to the db I'm believed it's committed in CST time zone. The SQL in the Crystal Report is using a CONVERT on the"FlightDate" and "101" which U.S. format which is confusing me more. I just need to have the logic of the report back on the FlightDate for Hawaiian time zone. Does that help or not? :w00t:
CONVERT on the"FlightDate" and "101" - is used to convert DATETIME to VARCHAR (string)
It doesn't change the time itself and if the data is used for reporting, it's better to be done in the report itself).
If you are sure that UTC is converted to CST before saving it to database (which is strange), you will need to apply time difference between CST and Hawaii using DATEADD function and, if required, implement "time savings" logic. There is nothing in SQL Server which can help you in doing this.
However, you can convert UTC datetime to local (and may be to the requested one as possible in .NET using Globalization) in CrystalReport, just remember to take DATETIME as DATETIME, without any conversion to string.
In SQLServer 2008 there is new data type datetimeoffset and new function sitchoffset(), they would help a bit more, but still you would need to figure out the time difference to apply yourself.
August 19, 2011 at 10:21 am
You're probably right that the data isn't converted from UTC to CST when the data is saved to the db. So how would I just report the "FlightDate" on Hawaiian local time zone only? I just need that logic and I'm good. Pleas help this is kinda urgent!!! Thanks in advance! 🙂
August 19, 2011 at 10:27 am
I think I've gave you everything you need to code it, but obviously you want solution ha?
After seconds of googling I've found http://wwp.greenwichmeantime.com/time-zone/usa/hawaii/time/
Where you can read that Hawaii Time Standard Time is 10 hours behind Greenwich Mean Time (GMT-10) and that Hawaii Time does not operate Daylight-Saving Time!
So what you need is simple:
SELECT DATEADD(hour,-10,ColumnWithUtcDateTime) AS DateTimeInHawaii
FROM ...
I'm off. It's Friday at the end.
August 19, 2011 at 10:32 am
Eugene, you are the best! I will try this code and see if this works for us. I also verified that the data stored for "FlightDate" is indeed stored in UTC format not CST. The UTC datetime is taken from the filename of the xml and that is stored in datetime type for the "FlightDate" field.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply