Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
Hi,
Date#() should not be used in SQL query, it should be used in Qlikview Load statement.
Regards,
Jagan.
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
Hi,
Try this
=date(date#('1002171051', 'YYMMDDhhmm'))
Hope this helps you.
Regards,
Jagan.
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?
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
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.
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.