比较表单 C# Visual Studio 中的两个日期开始日期和数据库 SQL Server 中的日期

Question

我想在"交易"表单中制作基于日期和编号的自动编号。但是我在比较日期"今天"和日期"昨天"时遇到问题。如果日期不同，则将从数字 1 创建一个新的"自动编号"。例如，该日期是 2019-08-08，因此来自"permintaanId"的 ID 是P2019080803(最后两个数字是当天进行的交易数量(。明天是 2019-08-09 将 ID P2019080901(最后两个数字将重置，因为没有一笔交易(

private void auto()
{
long hitung;
string urut;
SqlConnection conn = konn.GetConn();
conn.Open();
cmd = new SqlCommand("select permintaanId from permintaan_data where permintaanId in(select max(permintaanId) from permintaan_data) order by permintaanId DESC", conn);
rd = cmd.ExecuteReader();
rd.Read();
if (rd.HasRows) //<- this condition
{
hitung = Convert.ToInt64(rd[0].ToString().Substring(rd["permintaanId"].ToString().Length - 2, 2)) + 1;
string joinstr = "00" + hitung;
urut = "P" + DateTime.Now.ToString("yyyyMMdd") + joinstr.Substring(joinstr.Length - 2, 2);
}
else
{
urut = "P" + DateTime.Now.ToString("yyyyMMdd") + "01";
}
rd.Close();
txt_noPermintaan.Text = urut;
conn.Close();
}

Answer 1

获取最后一项

cmd = new SqlCommand("select top1 permintaanId from permintaan_data order by DESC", conn);
rd = cmd.ExecuteReader();
if (rd.HasRows)
{
var id = rd["the one with p and 2 numbers at the end"].ToString();
var dateLastCreated = id.ToString().Remove(0,1).Remove(id.Length-2,2);
if (dateLastCreated == DateTime.Now.ToString("yyyyMMdd"))
{
//increment
}
else
{
//create new
}
}

Answer 2

DECLARE @permintaanId INT=0
DECLARE @NewNo VARCHAR(40)=''
select @permintaanId=CAST(ISNULL(MAX(RIGHT(permintaanId,1)),0)+1 AS INT)
FROM permintaan_data
WHERE CAST([tranData] AS DATE) =CAST(GETDATE() AS DATE)
SET @NewNo='P'+CONVERT(VARCHAR(10),GETDATE(),112)+CAST(@permintaanId AS VARCHAR(10))
SELECT @NewNo