0

I want to fetch up rows of two tables from 30 days ago in sql but my date column is nvarchar and I cant convert it to date I tried several things but But did not receive any result and And always got an error

this is my query and I send TodayTime parameter from program by @Date to sql

[dbo].[Report30Days]
@Date nvarchar(10)
as

select
    coalesce(S.Date,B.Date) Date,
    coalesce(S.TAccount,0) sTAccount,
    coalesce(S.Remaining,0) sRemaining,
    coalesce(B.TAccount,0) bTAccount,
    coalesce(B.Remaining,0) bRemaining
from
    (select
        Date,sum(TAccount) TAccount, sum(Remaining) Remaining
    from SaleInvoices
    where
        DateDiff(day,convert(datetime,@Date,110),convert(datetime,Date,110))<=30
    group by Date) S
    Full Outer Join
    (select
         Date,sum(TAccount) TAccount, sum(Remaining) Remaining
    from BuyInvoices
    where
        DateDiff(day,convert(datetime,@Date,110),convert(datetime,Date,110))<=30
    group by Date ) B
    on
        S.Date=B.Date

my problem is here

where
        DateDiff(day,convert(datetime,@Date,110),convert(datetime,Date,110))<=30

Date Column in Tables and @Date Format => 2017/02/02

After execute this procedure , This error will be displayed :

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

Please Guide Me

Thank you very much

MPERSIA
  • 187
  • 1
  • 15
  • What is the exact error you are getting? Also, what is the data type of `Date`? – SS_DBA Feb 24 '17 at 20:48
  • Why do you need to pass today's date as parameter to the stored procedure? You can just get the today's date in your SQL script. Also, why do you have to define the parameter type as nvarchar? – Sparrow Feb 24 '17 at 20:54
  • Can you use a less ambiguous date example? like december 31st ? – SqlZim Feb 24 '17 at 20:54
  • 1
    try passing the string as '2017-02-02' – Sparrow Feb 24 '17 at 20:55
  • 1
    [Bad habits to kick : mis-handling date / range queries - Aaron Bertrand](https://sqlblog.org/2009/10/16/bad-habits-to-kick-mis-handling-date-range-queries) – SqlZim Feb 24 '17 at 20:56
  • DateTime Column in Tables and @Date Format => 2017/02/02 excuse me I'm Beginner and I use from nvarchar becuase that is easier for solar(Shamsi) calender – MPERSIA Feb 24 '17 at 20:59
  • `2017/02/02` doesn't tell me which one is the month and which one is the day. Would it be `2017/12/31` or `2017/31/12` ? Or you could just tell us `YYYY/MM/DD` or `YYYY/DD/MM` – SqlZim Feb 24 '17 at 21:01
  • yyyy/mm/dd 2017/02/02 – MPERSIA Feb 24 '17 at 21:03
  • The whole root of your problem is right here "my date column is nvarchar". When you use the wrong datatype your queries become a LOT more difficult. – Sean Lange Feb 24 '17 at 21:40

1 Answers1

1

@date should really be a date data type instead (or datetime, or datetime2). Using n/varchar for dates is a bad decision. 

/* lets make a proper date parameter */
/* @date nvarchar format is  YYYY/MM/DD, replacing / gives us YYYYMMDD 
   which sql server can easily convert to a date    */
declare @ActualDate date;
set @ActualDate = dateadd(day,-30,convert(date,replace(@date,'/','')));

select
    coalesce(S.Date,B.Date) Date,
    coalesce(S.TAccount,0) sTAccount,
    coalesce(S.Remaining,0) sRemaining,
    coalesce(B.TAccount,0) bTAccount,
    coalesce(B.Remaining,0) bRemaining
from
    (select
        Date,sum(TAccount) TAccount, sum(Remaining) Remaining
    from SaleInvoices
    where convert(date,replace(Date,'/','')) => @ActualDate
    group by Date
    ) S
    Full Outer Join
    (select
         Date,sum(TAccount) TAccount, sum(Remaining) Remaining
    from BuyInvoices
    where convert(date,replace(Date,'/','')) => @ActualDate
    group by Date 
    ) B
    on S.Date=B.Date
SqlZim
  • 37,248
  • 6
  • 41
  • 59