
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Convert a datetime to date
Hey everyone,
I have got a datetime field eg. 2016-09-14 18:11:05. I would like to turn it into 14/09/2016 (DD/MM/YYYY).
I used the date function like :
Date(MyDate, 'DD/MM/YYYY') as MyDate_2
At first, I thought it worked since it did display the values as DD/MM/YYYY. But then I realised while doing a graph with MyDate_2 as dimension, that it didn't aggregate the values as I expected :
MyDate_2 sum(OtherValue)
11/09/2016 2
11/09/2016 4
And of course, I wanted to get :
MyDate_2 sum(OtherValue)
11/09/2016 6
I guess the Date function only change the way the value is displayed but not the value itself...
How can I do to get a real date ?
Thanks for yor help
Have a good day
Laura
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Laura,
try
Date(Floor(TimeStamp#(MyDate,'YYYY-MM-DD hh:mm')), 'DD/MM/YYYY') as MyDate_2
Regards,
Antonio

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Laura,
try
Date(Floor(TimeStamp#(MyDate,'YYYY-MM-DD hh:mm')), 'DD/MM/YYYY') as MyDate_2
Regards,
Antonio


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Laura,
try
Date(Floor(TimeStamp#(MyDate,'YYYY-MM-DD hh:mm')), 'DD/MM/YYYY') as MyDate_2


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
maybe this is sufficient to implement your requirement:
DayName(MyDate) as MyDate_2
hope this helps
regards
Marco

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I have the same issue than Laura but once i have my new date format, i would like to split it into month and year field.
Date(Floor(TimeStamp#(CreatedOn,'DD/MM/YYYY hh:mm:ss')),'DD/MM/YYYY') as [Dateopp],
Month([Dateopp]) as Month,
Year([Dateopp]) as Year,
But when I do so, it tells me that the field [Dateopp] does not exist..
Any solutions?
thank you for your help.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
try
Month(TimeStamp#(CreatedOn,'DD/MM/YYYY hh:mm:ss')) as Month,
Year(TimeStamp#(CreatedOn,'DD/MM/YYYY hh:mm:ss')) as Year,
Regards,
Antonio


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
or witrh preceding load:
LOAD *,
Month(Dateopp) as Month,
Year(Dateopp) as Year;
LOAD Date(Floor(TimeStamp#(CreatedOn,'DD/MM/YYYY hh:mm:ss')),'DD/MM/YYYY') as Dateopp,
someotherfields
FROM YourSource;
regards
Marco

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
add in the below code to your data load script. in the DERIVE FIELDS FROM FIELDS at the bottom of your script add in your date fields. now, when you use these as a dimension you will have the field name option with several other date format option etc. MonYr. select the Date option and this will give you the date without the timestamp
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
If(WeekDay($1) = 'Sat', 'Weekend', If(WeekDay($1) = 'Sun', 'Weekend', 'Working Day')) As [DayType],
Dual('WC'&WeekStart($1), WeekStart($1)) AS [WeekCommencing] Tagged ('$weekcommence', '$cyclic'),
Weekday($1) As [DayName],
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo],
Num($1) As [Num],
Num(monthstart($1)) AS [NumYM] Tagged ('$axis', '$yearmonth', '$qualified');
DERIVE FIELDS FROM FIELDS SUBMISSION_START_DATE, INC_DATE USING [autoCalendar] ;

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This gives error: 'TimeStamp#' is not a recognized built-in function name.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
This seem to be a good alternative without having to faff around with too much. Worked like a charm to me though.
