Nithin Maliackal February 2016

How can we use dateadd function inside a date() function

I want to get values between two dates.Here the Created on is '2016-02-08 10:55:23' i.e with date and time.This query works well.

select  E.Id as StaffId,
        SUM(IC.AmountReceived) as totalAmount,INV.InvoiceType as AccountType
from invoice_collection IC,employee E ,invoice INV
where IC.CollectedBy=E.id and IC.OperatorCode=#operatorCode#
        and INV.InvoiceNo=IC.InvoiceNo and E.Id=#staffId#
        and TxnDate > #CreatedOn#
group by INV.InvoiceType;

Issue When i'm modifying my code as show below ,it does not show up any values.

select  E.Id as StaffId,
        SUM(IC.AmountReceived) as totalAmount,INV.InvoiceType as AccountType
from invoice_collection IC,employee E ,invoice INV
where IC.CollectedBy=E.id and IC.OperatorCode=#operatorCode#
            and INV.InvoiceNo=IC.InvoiceNo and E.Id=#staffId#
            and TxnDate > #CreatedOn# and TxnDate < date(dateadd(day,1,#CreatedOn#))
group by INV.InvoiceType;

Answers


mudassir hasan February 2016

Try this

TxnDate > #CreatedOn# AND TxnDate < DATEADD(d,DATEDIFF(day,0,dateadd(day,1,#CreatedOn#)),0)

Post Status

Asked in February 2016
Viewed 1,643 times
Voted 8
Answered 1 times

Search




Leave an answer