How to reset invoice number at the beginning of financial year

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

This is my query

How to reset invoice number at the beginning of the financial year using SQL server 2008

This is my query 

select isnull(max(billnu),0) +1 from sales where saldate between CAST('04/01/'+ CAST(YEAR(GETDATE())as varchar) as DateTime) and CAST('03/31/'+ CAST((YEAR(GETDATE())+ 1) as varchar)as DateTime)

but the invoice number displays 1 last year I am done more than 100 bills. Please any one solves my problems.

What I have tried:

How to reset invoice number at the beginning of the financial year using SQL server 2008

Posted 5-Jan-23 2:13am

Boopalslm

Updated 5-Jan-23 4:14am

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2023-01-05T02:41:00

Ermmm ... are you sure? Most accountants and tax men frown on reusing invoice numbers as it leaves space for error or fraud: two identical invoice numbers with different companies and values on them raise some real red flags, as do "unexplained gaps" in invoice numbers. If you send invoice number 93 and the next one that goes out is invoice 01 then there is a suspicion that you don't want use to see invoices 94, 95, and 096.

Remember, tax is payable on all invoiceable sales, so any oddities make people assume fraud and that can get very heavy. You don't want to go there!

Invoice numbers are normally sequential, and cannot be "retracted", "reissued", or "modified" instead, you issue a credit note against the invoice (a negative value invoice in effect) to zero or revise the amount owed.

CHill60 · Answer 2 · 2023-01-05T04:14:00

Assuming you are going to include the Year as part of the "final" invoice number and the number you are trying to reset is just the "number of invoices so far this year" then have a look at the solution at Invoice no should starting with 1 for every finacial year[^] - please, please, please read ALL of the comments before using the suggested solution.

Some other points about your SQL.
Don't use strings to construct dates. If you want the 1st April in the current year, add 3 months to the first date of the current year thus:

SQL

dateadd(mm, 3, dateadd(yy, datediff(yy, 0, GETDATE()), 0))

You can get 31st March of the following year in a similar way with

SQL

dateadd(dd, -1, dateadd(mm, 3, dateadd(yy, datediff(yy, 0, GETDATE()) + 1, 0)))

I adapted these from my go-to reference for dates from Lynn Pettis at Some Common Date Routines – SQLServerCentral[^]

Secondly - use an unambiguous date format - see Date formatting - Globalization | Microsoft Learn[^], use ISO 8601 standards
In other words - is 04/01/2022 4th January or 1st April? 2022-04-01 makes it clear which one you mean. As does 20220401.
2022-APR-01 is also clearer - but assumes English speaking environment. If you are extracting dates to be displayed then worry about the "format" in the Display layer and no earlier.

How to reset invoice number at the beginning of financial year

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to reset invoice number at the beginning of financial year

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us