convert date time field to month year-

[robmarr789]

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.

[sunny_talwar · Accepted solution]

May be this

Date(MonthStart(Date#(SubField(Fieldname, ' ', 1), 'YYYY-MM-DD')), 'MMM YYYY') as MonthYear

[PrashantSangle]

which sql db are you using oracle or sql server??

regards,

[sasiparupudi1]

You could use TO_CAHR

something like

to_char('2017-01-01 00:00:00.000','MON YYYY')

[robmarr789]

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

[jonathandienst]

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.

[PrashantSangle]

try with Format(getdate(),'MMM') to get month

and Year() to get year

try below

Format(PostingDate,'MMM') & ' ' & Year(PostingDate) as MonthYear

Regards, 

[qlikviewwizard]

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')

[sasiparupudi1]

Try

CONVERT(VARCHAR(9), YourDATEFIELD, 6) AS [DD MON YY]

[PrashantSangle]

LOL 

Is this work in SQL?