Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am bringing in some data from a sql database.
I have a date time field as follows "2017-01-01 00:00:00.000" I am trying to create a MonthYear field than would convert this example to "Jan 2017".
Note I am looking to do this within SQL and not Qlikview at this stage.
May be this
Date(MonthStart(Date#(SubField(Fieldname, ' ', 1), 'YYYY-MM-DD')), 'MMM YYYY') as MonthYear
May be this
Date(MonthStart(Date#(SubField(Fieldname, ' ', 1), 'YYYY-MM-DD')), 'MMM YYYY') as MonthYear
which sql db are you using oracle or sql server??
regards,
You could use TO_CAHR
something like
to_char('2017-01-01 00:00:00.000','MON YYYY')
Thanks,
I'm using SQL Server.
I've tried that by replacing my date field where it says Fieldname but I'm getting the following error.
'SubField' is not a recognized function name.
Do I need to edit another part of the code?
Date(MonthStart(Date#(SubField(PostingDate, ' ', 1), 'YYYY-MM-DD')), 'MMM YYYY') as MonthYear
Sunny's code is for QV, so it wont run in SQL. Frankly, its a long time since I used SQL date formatting as the QV commands are so much more powerful.
try with Format(getdate(),'MMM') to get month
and Year() to get year
try below
Format(PostingDate,'MMM') & ' ' & Year(PostingDate) as MonthYear
Regards,
Hi,
Try like this.
=DATE(DATE#(left('2017-01-01 00:00:00.000',10),'YYYY-MM-DD'),'MMM YYYY')
=DATE(DATE#(left(DateFiled,10),'YYYY-MM-DD'),'MMM YYYY')
Try
CONVERT(VARCHAR(9), YourDATEFIELD, 6) AS [DD MON YY]
LOL
Is this work in SQL?