Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

FORMATING DATE - HELP

Hi

I'm new to qlikview, having used this over a few days I am getting slightly frustrating with the script language. I am trying to format the month to return a Text rather than a number.

With my script below, it returns a number, not text. Can someone help????

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Month (se_DATEIMPORT)as TMONTH

FROM `dbo_SwapExposure`;

22 Replies
Not applicable
Author

Month() returns the numeric month of the date. MonthName() should return the month abbreviation plus the year.

Left(MonthName(se_DATEIMPORT), 3) as TMONTH should give you just the month abbreviation.

Not applicable
Author

Hi

Thanks for getting back to me

Left(MonthName(se_DATEIMPORT),3) as TMONTH

I entered this in and I get an error

Not applicable
Author

What is the error? What is the format of se_DATEIMPORT?

Try entering this:

Left(MonthName(Now()), 3) As TodayMonth


I did that in a test and it worked. It could be that it is not recognizing se_DATEIMPORT as a Date. You may have to use Date() or Date#() to convert it first.

Not applicable
Author

Hi

This is the format of the date field = 22/09/2010

Im new to script writing on qlikview

How would I edit this query so it returns the abbreivation name of the month rather than number

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(MonthName(se_DATEIMPORT),3) as TMONTH

FROM `dbo_SwapExposure`;

Not applicable
Author

Hi,

You can use following script :

Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

If this doesn't work, remove # after Date in above script.

Still if it gives error, Plz share sample data if you can.

Happy Thoughts

Not applicable
Author

Hi

Still no luck. I must be doing something wrong. This is what I am trying to run now

SET DateFormat='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DateFormat='DD/MM/YYYY';
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

FROM `dbo_SwapExposure`;

Not applicable
Author

This is my error:

SQL Error:[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3)'.
SQL Scriptline:
SQL State:37000
SQL SELECT `se_AP`,
`se_DATEIMPORT`,
`se_FUND`,
`se_FundNAV`,
`se_ID`,
`se_IMPORTID`,
`se_SwapMTM`,
`se_SwapMTMFundNAVPer`,
`se_SwapMTMNotionalPer`,
`se_SwapNotional`,
Year(se_DATEIMPORT)as TYEAR,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH

FROM `dbo_SwapExposure`

Not applicable
Author

Hi

MonthName is a Qlikview function, not SQL

you have to load first in a table T1 from SQL query and then you can use T2 : LOad *, left(monthname(....) as Tmonth resident T1

you can make it in one load using

Load *, left(monthname(....) as Tmonth;

SQL ....FROM dbo_SwapExposure

regards

christian

Not applicable
Author

Hi

This is what I wrote and tried to load butit didn't work

ODBC CONNECT TO [MS Access Database;DBQ=G:\ETP_TRADING AND OPERATIONS\Daily internal reports\Reporting DB\IM Access Database\ASSENAGON.mdb];
LOAD `se_ID`,
`se_FUND`,
`se_AP`,
`se_SwapNotional`,
`se_FundNAV`,
`se_SwapMTM`,
`se_SwapMTMNotionalPer`,
`se_SwapMTMFundNAVPer`,
Left(Monthname(Date#(se_DATEIMPORT,'DD-MMM-YY')),3) as TMONTH,
`se_IMPORTID`;

SQL SELECT `se_ID`,
`se_FUND`,
`se_AP`,
`se_SwapNotional`,
`se_FundNAV`,
`se_SwapMTM`,
`se_SwapMTMNotionalPer`,
`se_SwapMTMFundNAVPer`,
`se_DATEIMPORT`,
`se_IMPORTID`
Year(TMONTH)as YEAR,
MONTH(TMONTH)as REPORTING_MONTH
FROM `dbo_SwapExposure`;