-1

I'd like to get the total amount of purchases according to the current month, but in my SQL Server database I set the date field to write nvarchar(50). When the code is executed, I get an error:

Conversion failed when converting date and/or time from character string.

Is there a way to change the code to make it accept nvarchar(50) field type but I don't want to change it from database to date type I want it to work on nvarchar(50) type.

Code:

int month = Convert.ToInt32(DateTime.Now.Month.ToString());
DataTable tblItems_40 = new DataTable();
tblItems_40 = db8.readData("SELECT  SUM(Total) FROM Buy_Detalis WHERE MONTH(Date) 
         ='" + month + "' ", "");
tblItems8 = db8.readData("SELECT * From Buy_Detalis WHERE Date ='" + 
         month.ToString() + "'", "");
if (tblItems8.Rows.Count <= 0)
{
    labelControl6.Text = "0.ج.م";
}
else if (tblItems8.Rows.Count >= 1)
{
    labelControl6.Text = (tblItems_40.Rows[0][0].ToString)();
    labelControl6.Text = Decimal.Parse(labelControl6.Text).ToString("C");
}
jps
  • 20,041
  • 15
  • 75
  • 79
Technical Quality-TQ
  • 1
  • 2
  • 2
    Why does your `Date` column have an `nvarchar` type in the first place? And why are you converting `DateTime.Now.Month` to string, then right back to `int`? – 41686d6564 stands w. Palestine Jan 29 '22 at 15:29
  • I worked in this format on the program completely date type nvarchar not date – Technical Quality-TQ Jan 29 '22 at 15:31
  • The code works for me correctly, but when the date type in the database is date. I only want the code to work when the date type is nvarchar – Technical Quality-TQ Jan 29 '22 at 15:33
  • 2
    You _can_ [convert nvarchar into date](https://stackoverflow.com/q/19218982/8967612) if you're certain they're date values. But again, why would you use `nvarchar` to store dates? That doesn't make any sense. – 41686d6564 stands w. Palestine Jan 29 '22 at 15:47
  • I use the nvarchar date so that I can store it in a special format that works for me. Is it possible to modify my code to suit? Thank you very much – Technical Quality-TQ Jan 29 '22 at 15:52
  • 3
    "I use the nvarchar date so that I can store it in a special format that works for me." That still sounds like a really bad idea - why use a "special format" when you can use the database's built-in handling of dates? I'd also *strongly* recommend you learn about parameterized SQL and SQL Injection Attacks, and stop including the values directly in your SQL. – Jon Skeet Jan 29 '22 at 15:56
  • 1
    If you really, **really** have to, then click on the link in my previous comment to see how you can convert nvarchar date values into Date type. However, I would **strongly** advise against that. Store your date values as date and you can still [format them however you like](https://learn.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings) once you read the values by the client. This will make your life much, much easier. – 41686d6564 stands w. Palestine Jan 29 '22 at 15:56
  • Well, thank you for the advice. Can you modify the code attached above? – Technical Quality-TQ Jan 29 '22 at 16:03
  • Has that code been run through an obfuscator? – Caius Jard Jan 29 '22 at 17:16
  • `WHERE MONTH(Date) =` is also inefficient, you should use a begin/end date range to filter the date. Also you are open to injection and syntax errors, you should parameterize properly. If you don't think that `.ToString()` is a risk, consider [The BobbyTables culture](https://codeblog.jonskeet.uk/2014/08/08/the-bobbytables-culture/) – Charlieface Jan 29 '22 at 19:20

1 Answers1

-2

I came up with this solution and wanted to share it with yous and it worked great for me

DataTable tblItems_40 = new DataTable();
        string d5 = DateTime.Now.Month.ToString();
        string d6 = DateTime.Now.Year.ToString();
        tblItems_40 = db4.readData("SELECT YEAR(CONVERT(date,[Buy_Detalis].[Date], 103)) as SalesYear,MONTH(CONVERT(date,[Buy_Detalis].[Date], 103)) as SalesMonth,sum(Buy_Detalis.Total) AS TotalSales FROM Buy_Detalis where YEAR(CONVERT(date,[Buy_Detalis].[Date], 103)) ='" + d6 + "' AND MONTH(CONVERT(date,[Buy_Detalis].[Date], 103)) ='" + d5+ "'  GROUP BY YEAR(CONVERT(date,[Buy_Detalis].[Date], 103)), MONTH(CONVERT(date,[Buy_Detalis].[Date], 103))  ", "");
        tblItems8 = db4.readData("SELECT sum(Buy_Detalis.Total) AS TotalSales FROM Buy_Detalis GROUP BY YEAR(CONVERT(date,[Buy_Detalis].[Date], 103)), MONTH(CONVERT(date,[Buy_Detalis].[Date], 103)) ORDER BY YEAR(CONVERT(date,[Buy_Detalis].[Date], 103)), MONTH(CONVERT(date,[Buy_Detalis].[Date], 103))", "");
        if (tblItems8.Rows.Count <= 0)
        {
            labelControl6.Text = "0.ج.م";
        }
        if (tblItems8.Rows.Count >= 1)
        {
            labelControl6.Text = (tblItems_40.Rows[0][2].ToString)();
            labelControl6.Text = Decimal.Parse(labelControl6.Text).ToString("C");
        }
Technical Quality-TQ
  • 1
  • 2