如何在SQL Server和VB.NET中生成用户定义的日期代码？ [英] How do I generate user defined date code in SQL server and VB.NET?

问题描述

我正在研究一个项目，我需要日期代码，例如，如果它是2017/10/11那么代码应该出现7XB（7是17年，X是月oct，B是11天）和如果零件号相同，则与零件号相关联，然后7XB应像7XB1一样递增1。我有所有的月和日代码，但不知道如何使用它在SQL服务器中生成这些代码和序列，我也可以保存在数据库中。是否有任何方法可以在sql中编写查询，如果零件号与给定日期匹配，可以解码日期。



我需要在VB中做同样的事情.net创建一个Windows应用程序。当我给出部件号和日期时，它应该解码日期并打印标签并将解码的日期和序列保存在数据库中。



请帮帮我，我是sql和VB.net的新手



提前致谢！



我尝试了什么：



我试图在SQL server和vb.net中生成用户定义的日期代码但是没有知道怎么做。

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.

推荐答案

主要说明：没有意义！

由于多种原因，您应该使用日期数据类型，而不是字符串数据类型。其中之一是，你将无法进行计算。无论何时你决定进行计算，你都需要编码/解码日期。

但是（！）......如果你想生成用户定义的日期字段，请检查：

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:

'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

现在您知道如何编码/解码日期。您现在需要做的就是编写代码来保存/从数据库中检索数据（SQL）。

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).

这篇关于如何在SQL Server和VB.NET中生成用户定义的日期代码？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！