Master calendar

[Anonymous]

Hello,

I have some data with the date/time column, but the format is 12/02/2012 1:15AM... If I want to determine which day of the week is, is it possible with a master calendar or something like that?

Thanks!!!

[swuehl · Accepted solution]

Just take care that the format is correctly read in in your load:

SET TimestampFormat='DD/MM/YYYY h:mmTT';

LOAD

...

DateTimeColumn,

daystart(DateTimeColumn) as Date,

Time#(Time(DateTimeColumn) as Time,

weekday(DateTimeColumn) as Weekday,

...

from Table;

weekday() will get you the day of the week for each timestamp. Check out also the other date / time functions.

Regards,

Stefan

edit: removed line

[Anonymous]

Ok, and if I have some historic tables, the function weekday would take the exact day of the year?

[swuehl]

What do you mean with the exact day of the year? Maybe according

DayNumberOfYear(date[,firstmonth])

Returns the day number of the year according to a timestamp with the first millisecond of the first day of the year containing date. The function always uses years based on 366 days.

By specifying a firstmonth between 1 and 12 (1 if omitted), the beginning of the year may be moved forward to the first day of any month. If you e.g. want to work with a fiscal year starting March 1, specify firstmonth = 3.

Examples:

DayNumberOfYear(date) returns the day number counted from the first of the year.

DayNumberOfYear(date,3) returns the number of the day as counted from the first of March.

function?

[Anonymous]

If I want to format the weekday()... how can I do that... I'm getting "jan" and I want "january". The same issue with Month().

Thanks

[swuehl]

Not sure if I am missing an easier solution but you can use this

LOAD

..

DateField,

dual(date(DateField,'MMMM'),num(month(DateField))) as LongMonths,

dual(date(DateField,'WWWW'),num(weekday(DateField))) as LongWeekDays,

...

from Table;

to substitute the weekday() resp. month() function. Using the format codes 'MMMM' resp. 'WWWW' will return the long names set in the standard format settings or retrieved from the OS.

Regards,

Stefan

[#7]

In your load script

SET MonthNames = 'january;february....';

SET DayNames = 'monday; tuesday.... ';