asp.NET和SQL Server日期时间问题

I am having hard time to store date information into the datetime column of SQL Server.

我很难将日期信息存储到SQL Server的datetime列中。

I get the input from the user for three columns:

我从用户那里获得了三列的输入：

Creation Date
创建日期
Preparation Date
准备日期
Next Preparation Date
下一个准备日期

I use calendarextender and format the date as "yyyy/MM/dd". When all the fields have date, they are stored in the DB as for instance, 16-10-2016 (dd-MM-yyyy).

我使用calendarextender并将日期格式化为“yyyy / MM / dd”。当所有字段都有日期时，它们存储在DB中，例如16-10-2016（dd-MM-yyyy）。

At this point I have two issues:

此时我有两个问题：

These columns are optional, when some of them are empty my code does not work (I assume because datetime cannot be null). To overcome this, I am using the following code snippet but still does not work.

这些列是可选的，当其中一些列为空时，我的代码不起作用（我假设因为datetime不能为null）。为了解决这个问题，我使用以下代码片段，但仍然无法正常工作。
```
DateTime? creationDate= null;

if (creationDateTextbox.Text != null && creationDateTextbox.Text != "")
{
    creationDate= Convert.ToDateTime(creationDateTextbox.Text);
}
```
When I fetch the dates from DB, they are shown as 10/16/2016 (MM-dd-yyyy) which is different how I formatted it. I would like to show it in the format user enters them.

当我从DB中获取日期时，它们显示为2016年10月16日（MM-dd-yyyy），这与我格式化它的方式不同。我想以用户输入的格式显示它。

3 个解决方案

#1

Dates do not have a format while stored in a database. It is actually usually just a very large long that counts the number of milliseconds from a set starting date.

日期存储在数据库中时没有格式。它实际上通常只是一个非常大的长度，它计算从设定的开始日期开始的毫秒数。

If you want to store the format you need to stop storing it as dates and instead just treat the text as text in the database, however if you do this you won't get the advantage of sorting or filtering by a date range because it will just be seen as text.

如果你想存储你需要的格式，你需要将它作为日期存储，而只是将文本视为数据库中的文本，但是如果你这样做，你将无法获得按日期范围排序或过滤的优势，因为它会只是被视为文本。

#2

Date time doesn't have any format You can format is as a string, suppose your DateTime type database field dt which contain date as 10/16/2016 (MM-dd-yyyy) then you can convert it

日期时间没有任何格式您可以格式化为字符串，假设您的DateTime类型数据库字段dt包含日期为10/16/2016（MM-dd-yyyy）然后您可以转换它

string s = dt.ToString("yyyy/MM/dd");

#3

The answer to one of your questions is here: MSDN You can use data annotations to format the dates that you get from your SQL DB. I'm assuming that you're using EF6; if not, you can change the field to a varchar in SSMS, and store the date as a String.

您的一个问题的答案如下：MSDN您可以使用数据注释来格式化从SQL DB获得的日期。我假设你正在使用EF6;如果没有，您可以将字段更改为SSMS中的varchar，并将日期存储为String。

And the second, I'm unclear about, but if what you want is for your SQL DB column to be optional, you can use the Optional data annotation for that.

第二，我不清楚，但如果你想要的是你的SQL DB列是可选的，你可以使用Optional数据注释。

#1