Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi Laura,

try

Date(Floor(TimeStamp#(MyDate,'YYYY-MM-DD hh:mm')), 'DD/MM/YYYY') as MyDate_2

Regards,

Antonio

View solution in original post

8 Replies
antoniotiman
Master III
Master III

Hi Laura,

try

Date(Floor(TimeStamp#(MyDate,'YYYY-MM-DD hh:mm')), 'DD/MM/YYYY') as MyDate_2

Regards,

Antonio

hemachandran
Partner - Creator
Partner - Creator

Hi Laura,

try

Date(Floor(TimeStamp#(MyDate,'YYYY-MM-DD hh:mm')), 'DD/MM/YYYY') as MyDate_2

MarcoWedel

Hi,

maybe this is sufficient to implement your requirement:

DayName(MyDate) as MyDate_2


hope this helps


regards


Marco

Not applicable
Author

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.

antoniotiman
Master III
Master III

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

MarcoWedel

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

david_pearson
Contributor III
Contributor III

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] ;

new_bie
Contributor
Contributor

This gives error: 'TimeStamp#' is not a recognized built-in function name.