Time conversions

[Anonymous]

I have a datetime field Startime which is in GMT and has to be converted to PST ,

Here is my Load statement , how do i convert to PST in load statement ?

Load

starttime

,

Date

,

Week

(starttime) AS [Week #]

,

WeekStart

(starttime) as Week

,

Year

(starttime) AS Year

,

Month

(starttime) AS [Month #]

,

MonthStart

(starttime) as Month

,

Day

(starttime) AS [Day]

,

Weekday

(starttime) AS WeekDay

,

Date

(monthstart(starttime), 'MMM-YYYY')

AS

MonthYear

,

Week

(starttime)&'-'&Year(starttime) AS WeekYear

,

hour

(starttime) as BuildHour

[#2]

Use this function:

ConvertToLocalTime(starttime, "GMT-08:00")

[Anonymous]

I am getting error saying coverttolocaltime is not recognizable

Here is my code could any one please let me know what is wrong ...

[ code ]

Load

ConvertToLocalTime

Date

Week

WeekStart

Year

,

Month

,

MonthStart

,

Day

,

Weekday

,

Date

(monthstart(ConvertToLocalTime(starttime,"GMT-07:00")), 'MMM-YYYY') (ConvertToLocalTime(starttime,"GMT-07:00")) AS WeekDay (ConvertToLocalTime(starttime,"GMT-07:00")) AS [Day] (ConvertToLocalTime(starttime,"GMT-07:00")) as Month (ConvertToLocalTime(starttime,"GMT-07:00")) AS [Month #] (ConvertToLocalTime(starttime,"GMT-07:00")) AS Year (ConvertToLocalTime(starttime,"GMT-07:00")) as Week ,(ConvertToLocalTime(starttime,"GMT-07:00")) AS [Week #] ,,(starttime,"GMT-07:00") as starttime ,

AS

MonthYear,

Week

,

hour

,

BuildSizeMB

,BuildID ,Action,Duration,FileCount,ProductLine,Duration_Min ,UserName, JobName ;

SQLsele

Date,BuildID,

((bytecount)/1024/1024)

as 'BuildSizeMB',(DURATION/60)AS Duration_Min,

Action,Duration,FileCount,ProductLine,SubmittedBy

as 'UserName',JobName FROM

ASWTRANS.DBO.CRMBUILDMETRICS [ /code ]

ct starttime,CONVERT(VARCHAR(10), ConvertToLocalTime(starttime,"GMT-07:00"), 101) AS (ConvertToLocalTime(starttime,"GMT-07:00")) as BuildHour (ConvertToLocalTime(starttime,"GMT-07:00"))&'-'&Year(ConvertToLocalTime(starttime,"GMT-07:00")) AS WeekYear

[rwunderlich]

It's very difficult to read your script as posted. Can you repost as plain text?

1. In Settings -> User Preferences -> Editor tab, uncheck "Copy as RTF".
2. Post without the [ code ] tags.

-Rob

[Anonymous]

Where do i find Setting?

[johnw]

At the top of the QlikView screen. It's one of the menu items.

[Anonymous]

Here is my script,I want to convert starttime to PDT which is in GMT

Load
starttime,
Date,
Week(starttime) AS [Week #],
WeekStart(starttime) as Week,
Year(starttime) AS Year,
Month(starttime) AS [Month #],
MonthStart(starttime) as Month,
Day(starttime) AS [Day],
Weekday(starttime) AS WeekDay,
Date(monthstart(starttime), 'MMM-YYYY') AS
MonthYear,
Week(starttime)&'-'&Year(starttime) AS WeekYear,
hour(starttime) as BuildHour,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName
;

SQL select starttime,CONVERT(VARCHAR(10), starttime, 101) AS Date,BuildID,
((bytecount)/1024/1024) as 'BuildSizeMB',(DURATION/60)AS Duration_Min,
Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName
FROM ASWTRANS.DBO.CRMBUILDMETRICS

[johnw]

I think you just need "GMT-08:00" in single instead of double quotes, though since you aren't showing what you got the error on, I could easily be wrong. For that matter, it looks like you can very explicitly refer to pacific time. Perhaps like this:

LOAD
Week(starttime) AS [Week #],
WeekStart(starttime) as Week,
Year(starttime) AS Year,
Month(starttime) AS [Month #],
MonthStart(starttime) as Month,
Day(starttime) AS [Day],
Weekday(starttime) AS WeekDay,
Date(monthstart(starttime), 'MMM-YYYY') AS MonthYear,
Week(starttime)&'-'&Year(starttime) AS WeekYear,
hour(starttime) as BuildHour,
;
Load
converttolocaltime(starttime,'Pacific Time (US & Canada)') as starttime
Date,
BuildSizeMB,BuildID,
Action,Duration,FileCount,ProductLine,Duration_Min,
UserName,JobName
;

SQL select starttime,CONVERT(VARCHAR(10), starttime, 101) AS Date,BuildID,
((bytecount)/1024/1024) as 'BuildSizeMB',(DURATION/60)AS Duration_Min,
Action,Duration,FileCount,ProductLine,SubmittedBy as 'UserName',JobName
FROM ASWTRANS.DBO.CRMBUILDMETRICS

[Anonymous]

So , does 'Pacific Time (US & Canada)' consider day light savings too?

You are right , single quotes work...

converttolocaltime(starttime,'GMT-07:00') works without any errors, i have given 7 as this is day light saving period....

And also , do i have to change this wherever i had startime ? like for Month , Year,day convertions and also select statement ? Or qlikview automatically take the conversion if given in one load statement ?

[johnw]

According to the help text, "The resulting time is adjusted for daylight savings time, unless the third parameter is set to 1 or true()."

The way I rearranged your load, you don't have to do it anywhere else but that one spot. As I set it up, that happens first, THEN it takes a second pass, using your adjusted starttime as input for all the other functions. The approach is called a preceeding load. It has this basic structure:

LOAD *
,these fields are loaded last, and can use any fields from the below two loads
;
LOAD *
,these fields are loaded second, and can use any fields from the below load
;
LOAD
these fields are loaded first
FROM my data source
;