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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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