使用dateparseexact将字符串转换为datetime [英] convering string to datetime with dateparseexact

问题描述

这可能听起来非常基本但我在将字符串转换为日期时遇到问题



我的代码如下：

 SqlCommand com = new SqlCommand（SELECT Date FROM pD WHERE userName = @ UN，con）; 
 com.Parameters.AddWithValue（@ UN，用户名）; 
 DateTime date = DateTime.ParseExact（（com.ExecuteScalar（）。ToString（）），MM / dd / yyyy hh：mm：ss tt，null）; 
 
 int i = DateTime.Compare（date，DateTime.UtcNow.AddYears（-1））; 
 if（1< 0）
 {
 //旧案例代码
} 
其他
 {
 //最近代码case 
} 

当我在visual studio 2012中运行此代码时，我收到错误



字符串未被识别为有效的DateTime。



我运行此代码的单个用户名具有日期值field = 1/23/2013 10; 20:00 AM



我无法弄清楚我的代码在行中出了什么问题

 DateTime date = DateTime.ParseExact（（com.ExecuteScalar（）。ToString（）），MM / dd / yyyy hh：mm：ss tt ，null）;< br /> 

发出此错误



我在网上搜索了答案，但我无法解决这个基本问题

我知道这是一种耻辱但我会无知直到我寻求帮助



非常感谢你的帮助

解决方案

如果值存储为DATETIME（应该是），那么您不需要这样做;只需投出价值：



DateTime date =（DateTime）com.ExecuteScalar（）;



如果值存储为字符串，请重新考虑 - 始终将日期存储为DATETIME（或DATE），而不是作为字符串存储。

< blockquote>在这种情况下，错误可能就像你在ParseExact中使用不正确的格式化字符串一样简单：你有一个冒号，应该有一个冒号，你使用MM查找两位数字月份值，而不是M，可以接受一位或两位数。



比较将编译的两个格式化示例：

 string dString1 =01/23/2013 10:20:00 AM; 
 string dString2 =1/23/2013 10:20:00 AM; 
 
 string sFmt1 = @MM / d / yyyy hh：mm：ss tt; 
 string sFmt2 = @M / d / yyyy hh：mm：ss tt; 
 
 DateTime date1 = DateTime.ParseExact（dString1，sFmt1，null）; 
 DateTime date2 = DateTime.ParseExact（dString2，sFmt2，null）; 
 

我希望你能接受PIEBALDconsult的建议并切换到使用原生SQL DateTime值而不是字符串，因为据我所知，使用字符串会带来恶意软件注入的风险，以及本地化问题，口蹄疫等

如果您的日期列在 pD table的类型为 DateTime 然后你可以关注解决方案1 ​​

如果它是关闭的类型 Varchar ，然后编写以下代码

  string  strDate = com.ExecuteScalar（）。ToString（）; 

现在在此行应用断点检查格式它的显示方式。基于格式，您可以在 ParseExact 方法中构建字符串格式。

It may sound very elementary but I am having problem converting string to date time

My code is as follows:

SqlCommand com = new SqlCommand("SELECT Date FROM pD WHERE userName=@UN", con);
        com.Parameters.AddWithValue("@UN", username);
        DateTime date = DateTime.ParseExact((com.ExecuteScalar().ToString()), "MM/dd/yyyy hh:mm:ss tt", null) ;

        int i = DateTime.Compare(date, DateTime.UtcNow.AddYears(-1));
        if (1 < 0)
        {
            // code for old cases
        }
        else
        {
            //code for recent cases
        }

When I run this code in visual studio 2012, I get the error

"String was not recognized as a valid DateTime".

A single username for which I ran this code has the value in date field = 1/23/2013 10;20:00 AM

I am not able to figure out what has gone wrong with my code in the line

DateTime date = DateTime.ParseExact((com.ExecuteScalar().ToString()), "MM/dd/yyyy hh:mm:ss tt", null) ;<br />

that is giving this error

I have searched the answer on net but I am unable to solve this elementary problem
I know it is a shame but I will be ignorant till I seek help

Many many thanks for your help

解决方案

If the value is stored as a DATETIME (as it should be) then you don't need to do that; just cast the value:

DateTime date = (DateTime) com.ExecuteScalar() ;

If the value is stored as a string, then please reconsider -- always store dates as DATETIME (or DATE), and never as strings.

In this case the error may be as simple as the fact that you are using an incorrect formatting string with ParseExact: you have semi-colon where there should be a colon, and you use "MM" which looks for two digits for the month value, rather than "M" which would accept either one or two digits.

Compare these two formatting examples which will compile:

string dString1 = "01/23/2013 10:20:00 AM";
string dString2 = "1/23/2013 10:20:00 AM";

string sFmt1 = @"MM/d/yyyy hh:mm:ss tt";
string sFmt2 = @"M/d/yyyy hh:mm:ss tt";

DateTime date1 = DateTime.ParseExact(dString1, sFmt1, null);
DateTime date2 = DateTime.ParseExact(dString2, sFmt2, null);

I hope you'll take PIEBALDconsult's advice and switch to using native SQL DateTime values rather than strings since, as I understand it, using strings can present a risk of malware injection, as well as localisation issues, hoof-in-mouth disease, etc.

IF your Date column in the pD table is of Type DateTime then you can follow Solution 1
if it is off type Varchar , then write the following code

string strDate = com.ExecuteScalar().ToString();

Now apply a break point on this line and check the Format how it is displayed. based on the format you can frame the string format in the ParseExact Method..

这篇关于使用dateparseexact将字符串转换为datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！