Format date/time column to Year, Month, Day, Hour, Minutes

[Anonymous]

I have a SQL server data load that imports a column in the format

It is very difficult to manipulate this raw data for creating charts.  The time is too granular.  How can i format the time/date so that it is useable.  Here is now it loads from my SQL database.  I would like to parse the data out so that I have Year, Month, Day, Hour, and Minute Columns..

[s_achraphe]

Hello,

you can try something like this in your script qlikview/sense

Month(TimeSent,'MM') as MonthSent

Year(TimeSent) as YearSent

Or parse it in your SQL query with something like this :

EXTRACT(TimeSent, MONTH)  as MonthSent

EXTRACT(TimeSent, YEAR)  as YearSent

Hope it helps you

A.

[sunny_talwar]

Use Preceding Load on top of your SQL load and then use date and time functions‌‌ to get date, month, year, monthyear... etc.... or you can also Creating A Master Calendar‌‌ to create this stuff

[sibin_jacob]

You can use a preceding load on top of the Sql Load,

You can use Date function to format the data or you can use Month, Year Function respectively.

Load *, date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'YYYY') as Year,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'MM') as Month,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'DD') as Day,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'hh') as Hour,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'mm') as Minute,

date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'),'ss') as Second

;

SQL Select Top 2000

"ID_Action"

[MarioCenteno]

Try

Master Calendar ir AutoCalendar

[BalaBhaskar_Qlik]

Try like this:

DailyTime:

Load

    date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt')) as TimeSent,

SQL

Select top 2000

    "ID_ACTION",

    "Time_Created",

    "Time_Sent"

From Emp;

Calendar:

Load

    Date(TimeSent) as TimeSent,

    Day(Timesent) as Day,

    Month(TimeSent) as Month,

    Year(TimeSent) as Year,

    Hour(Timesent) as Hour,

    Minute(Timesent) as Minute;

Load

    Date(MinDate + IterNo()-1) as TimeSent

    while (MinDate + IterNo() - 1) <= Num(MaxDate);

Load    Min(date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'))) as MinDate,

    Max(date(date#(TimeSent,'MM/DD/YYYY hh:mm:ss tt'))) as MaxDate

Resident DailyTime;

[Anonymous]

This code runs..  But the new formatted data does not come thru properly.  I do have some SET commands (company standard template) before my SQL code.

Here is the end result:

Here are my SET commands:

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

[Anonymous]

This code doesn't run in my Script..

[Anonymous]

I noticed I had a pre-built Calendar in my Script Template..  I replaced a generic DateID with my TimeSent field, but it still didn't like the format..

TempCal:
Load
MonthStart(today(),-RowNo()+1) as TimeSent
AutoGenerate(12);

Calendar:
Load
TimeSent,
TimeSent as DateKey,
Year(TimeSent) As Year,
Month(TimeSent) As Month,
'Q' & Ceil(Month(TimeSent)/3) As Quarter,
Ceil(Month(TimeSent)/3) As Qtr,
Date(MonthStart(TimeSent), 'YYYY-MM') As YearMonth
Resident TempCal;

Drop Table TempCal;

Let vToday = Today();

Ranges:
Load
DateRange,
Date(Evaluate(RangeStart)) as RangeStart,
Date(Evaluate(RangeEnd)) as RangeEnd
;
LOAD * INLINE [
DateRange|RangeStart|RangeEnd
Last Month|MonthStart(vToday,-1)|MonthEnd(vToday,-1)
Last Quarter|QuarterStart(vToday,-1)|QuarterEnd(vToday,-1)
Last Year|YearStart(vToday,-1)|YearEnd(vToday,-1)
Last 3 Months|MonthStart(vToday,-3)|MonthEnd(vToday,-1)
Month-To-Date|MonthStart(vToday)|vToday
Year-To-Date|YearStart(vToday)|vToday]
(ansi, txt, delimiter is '|', embedded labels);


Left Join (Ranges)
IntervalMatch (TimeSent)
Load distinct RangeStart, RangeEnd resident Ranges;

[sunny_talwar]

What was the issue when you use the above script? It gave you an error or something else went wrong?