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: 
oseimuduame
Creator II
Creator II

Converting Dates from DateTime

i have my date to be like this 2016-08-16 16:11:52.207. i need to Format my date to give me something like this 2016-08-16, this is because i want to be able to filter using Year, Month and Day instead of Filtering with the Full datetime.

any help please.

1 Solution

Accepted Solutions
Clever_Anjos
Employee
Employee

//SALES Table

LOAD

  *,

  Date(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_DATE,

  Month(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_MONTH,

  Year(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_YEAR;

SQL SELECT

  o.CreatedDateTime as "SALES_CREATED_DATE",

    o.CreatingUserName as "WAITER",

    o.MenuItemId AS "ITEM_ID",

    o.MenuItemName AS "ITEM_NAME",

    o.Price AS "INVC_PRICE",

    o.Quantity AS "INVC_QTY",

  t.EntityName as "CUST_NAME"

FROM SambaAlata.dbo.Orders o

LEFT JOIN SambaAlata.dbo.TicketEntities t ON o.TicketId = t.Ticket_Id

WHERE EntityTypeId = 1;

View solution in original post

4 Replies
Clever_Anjos
Employee
Employee

//SALES Table

LOAD

  *,

  Date(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_DATE,

  Month(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_MONTH,

  Year(Floor(SALES_CREATED_DATE)) as SALES_CREATED_DATE_YEAR;

SQL SELECT

  o.CreatedDateTime as "SALES_CREATED_DATE",

    o.CreatingUserName as "WAITER",

    o.MenuItemId AS "ITEM_ID",

    o.MenuItemName AS "ITEM_NAME",

    o.Price AS "INVC_PRICE",

    o.Quantity AS "INVC_QTY",

  t.EntityName as "CUST_NAME"

FROM SambaAlata.dbo.Orders o

LEFT JOIN SambaAlata.dbo.TicketEntities t ON o.TicketId = t.Ticket_Id

WHERE EntityTypeId = 1;

oseimuduame
Creator II
Creator II
Author

i actually Want to Format the date in Item Report. i hope this will work also

Clever_Anjos
Employee
Employee

Which format in which field you need?

sasikanth
Master
Master

HI,

Try this,

LOAD *,

DATE(DayStart(SALES_CREATED_DATE))) as SALES_CREATED_DATE,

YEAR( DATE(DayStart(SALES_CREATED_DATE))) ) as YEAR,

MONTH (DATE(DayStart(SALES_CREATED_DATE))) ) as MONTH;

SELECT

  o.CreatedDateTime as "SALES_CREATED_DATE",

    o.CreatingUserName as "WAITER",

    o.MenuItemId AS "ITEM_ID",

    o.MenuItemName AS "ITEM_NAME",

    o.Price AS "INVC_PRICE",

    o.Quantity AS "INVC_QTY",

  t.EntityName as "CUST_NAME"

FROM SambaAlata.dbo.Orders o

LEFT JOIN SambaAlata.dbo.TicketEntities t ON o.TicketId = t.Ticket_Id

WHERE EntityTypeId = 1;