How do I generate user defined date code in SQL server and VB.NET?

Question

0.00/5 (No votes)

See more:

Hi, I am working on a project and there I need date code for example if it is 2017/10/11 then the code should appear 7XB(7 is year 17 and X is month oct and B is day 11) and this is connected with part number if part number is same then 7XB should increment by 1 like 7XB1. I have all the months and day code but don’t know how to use it to generate these code and sequence in SQL server and also I can save in database. is there any way to write a query in sql which can decode the date if part number match with the given date.

And the same thing I need to do in VB.net creating a windows application.In that when I give the part number and date it should decode the date and print the label and save the decoded date and sequence in the database.

Please help me I am new to sql and VB.net

Thanks in Advance!

What I have tried:

I am trying to generate user-defined date code in SQL server and vb.net but don't know how to do it.

Posted 11-Oct-17 4:42am

Member 10754595

Updated 11-Oct-17 8:31am

Add a Solution

Comments

Atlapure Ambrish 11-Oct-17 13:43pm

For every month and day there is different code? As you mentioned X is Oct and B is day 11, these will change for other month and day??

Member 10754595 11-Oct-17 13:46pm

yes

1 solution

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)

Maciej Los · Accepted Answer · 2017-10-11T08:31:00

Main note: it doesn't make sense!
You should use date data type, not string data type, because of several reasons. One of them is that, that you won't be able to make calculations. Anytime you decide to make calculations, you'll be in need to encode/decode date.
BUT(!)... if you want to generate user defined date field, check this:

VB

'create dictionary object
'assign numbers to chars 
Dim oDic = New Dictionary(Of Integer, String)
oDic.Add(0, "A")
oDic.Add(1, "B")
oDic.Add(2, "C")
oDic.Add(3, "D")
oDic.Add(4, "E")
oDic.Add(5, "F")
oDic.Add(6, "G")
oDic.Add(7, "H")
oDic.Add(8, "I")
oDic.Add(9, "J")

'example date
Dim sDate = "2017/10/11"
'replace "/"
sDate = sDate.Replace("/", String.Empty)
'encode date
Dim encodedDate = String.Join(String.Empty, sDate.Select(Function(x) oDic(Int32.Parse(x))))
Console.WriteLine("encoded date: {0}", encodedDate)

'decoded date
Dim cu = New Globalization.CultureInfo("en-US")
Dim decodedDate = DateTime.ParseExact( _
	String.Join(String.Empty, encodedDate.Select(Function(x) oDic.Where(Function(r) r.Value=x).Select(Function(k) k.Key).First())), _
	"yyyyMMdd", cu)
Console.WriteLine("decoded date: {0}", decodedDate)

Result:

encoded date: CABHBABB
decoded date: 2017-10-11 00:00:00

Now you know how do encode/decode date. All you need to do right now is to write code to save/retrieve data to/from database (SQL).