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

difference between date type values

hello,

i have date-time data coming in from sql server in the form of integar

ex:  one column 'abc' contains values such as :  1002171551 (date value in: yymmddhhmm)

2nd column 'def' contains values such as : 100217 or 1002170000 (date value in: yymmdd)

now i need to calculate difference between these two columns and i want sql server to treat these as date columns and not integar ones.

right now when i do the difference i get result purely as in subtraction of 2 integar values. but i want the result to be calculated as a difference between dates.s

any suggestions? 

10 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi Tushar,

First in QV scripting convert the date values in the form of numbers to date by using Date# function, so now the values are in date format now you can calculate the difference using Interval() easily.

Example:

Date#( DateField, 'YYMMDD' )

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

hi jagan, thanks for the answer.

but the problem is that these number values are actually dates (1002171551 - '10' as year, '02' as month, '17' as date, '15' as hour, '51' as minutes) in sql. so if i use date# on these, result will be wrong data. hope it makes sense.

-tushar

jagan
Luminary Alumni
Luminary Alumni

Hi,

Date#() should not be used in SQL query, it should be used in Qlikview Load statement.

Regards,

Jagan.

Not applicable
Author

hi,

i have used it in QV scripts and like i said before, it does not treat it differently.

here's an example:  date#('1002171551', 'YYMMDD') gives output as 1002171551.

and if i calculate difference between two columns containing this type of data, i dont get the output at all

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this

=date(date#('1002171051', 'YYMMDDhhmm'))

Hope this helps you.

Regards,

Jagan.

Not applicable
Author

this partially helps. but what i need to find is the difference between two columns that contain data like this.

say col abc = 1002171551

col def = 1002171051

now 'abc' - 'def' should be calculated as difference between calendar dates and the result should be displayed in calendar date format.

any ideas?

Not applicable
Author

hi ,

thanks for your suggestion, and i mixed interval() with timestamp#() to get the desired result, just not in the desired format. still working on it.. thanks Jagan

jagan
Luminary Alumni
Luminary Alumni

Hi,

You will get difference between two dates in terms of days/months/years, I cannot understand what you are asking "difference between calendar dates and the result should be displayed in calendar date format.".  Please clarify.

Regards,

Jagan.

whiteline
Master II
Master II

Hi.

date#('1002171551', 'YYMMDD') gives output as 1002171551.

and if i calculate difference between two columns containing this type of data

To use date#() here you should truncate your string (it's a known issue):

date#(left('1002171551', 6), 'YYMMDD')

To check that your string dates was converted into numerical dates (so that you can add and subtract them), you can just try to change its text format. For example, using num() functiont that return numerical representation.

You can easily try it in a text box:

=num(date#(left('1002171551', 6), 'YYMMDD')) << this returns 40226

=num(date#('1002171551', 'YYMMDD')) << this doesn't work and returns null.