DateTime??? Sql Server changed long word style???

Question

i am using SQL Server 2008. and using C#.

DateTime in SQL Server displays 2008/08/03 01:10:25.860.

but C# DateTime is 2008/08/03 01:10:25.

so, i cant find the row in these.

sorted DataView dView in the following...

        private struct dayFields
        {
            public int theCode;
            public string Code;

}

dayFields day = new dayFields();

object[] vals = new object[2];
                vals[0] =day.Date;

                vals[1] = day.InputTime; // "2012/08/12 01:25:43.529"
                iDay = dView.Find(vals);

i cant find the row in Sql Server, how to do?

Answer 1

See here

"The SQL Server datetime type does not include any time zone information. Because of this, the SQL Server client libraries assume that you will always want to store local times in it. If you have set your .NET DateTime value to something with the UTC time zone, then the SQL Server client will helpfully convert it to local time for you before storing it.

Possible solutions:

• Change your .NET DateTime value to specify DateTimeKind.Unspecified or DateTimeKind.Local.
• Change the table column and sproc parameter data types to datetimeoffset (SQL 2008 or above)."

Answer 2

One way to accomplish this is to normalize the date time string into a common format.

There are countless possibilities to accomplish this, but one way is as follows:

            string dateTime1 = DateTime.Parse(dateTimeString1).ToString("yyyy-MM-ddTHH:mm:ss zzz");
            string dateTime2 = DateTime.Parse(dateTimeString2).ToString("yyyy-MM-ddTHH:mm:ss zzz");

            if (dateTime1 == dateTime2)
            {
                // You found it... 
            }

John brings an excellent point; if you have control over the storing of the DateTime data, use (C#) .ToUniversalTime() or .ToLocalTime() methods.  If you are accessing data from different timezones use the .ToUniversalTime().

---

David Downing... If this answers your question, please Mark as the Answer. If this post is helpful, please vote as helpful.

Answer 3

If the row you want contains milliseconds, you need to have the milliseconds in the value you use for searching it.

Answer 4

what to make 'DateTime.Now' into milliseconds?

day.InputTime = DateTime.Now;

best regards, thank you.

Answer 5

I updated the sample code above to include "zzz", which includes the miliseconds.

---

David Downing... If this answers your question, please Mark as the Answer. If this post is helpful, please vote as helpful.

Answer 6

DateTime.Now already contains the milliseconds. But I don't see why you're asking. You're expecting to find in your base a record with the current date and time, to the millisecond?

Answer 7

pls answer the following...

public struct dayFields
{
public string remarks;
public DateTime inputTime;
}

then...

dailyFields day = new dailyFields();
day.remarks = "something";
day.InputTime = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-ddTHH:mm:ss zzz"));

but i can't get 'zzz'...

day.InputTime = {2012/08/12 11:47:05}

what is wrong with this code?

Answer 8

Hello，

U should initialize the DateTime by using its constructor, and then use ToString：

public class Example
    {
        static void Main(string[] args)
        {
            DateTime dt = new DateTime(2010, 6, 6, 12, 12, 12);
            Console.WriteLine(dt.ToString("yyyy-MM-dd HH:mm:ss zzz"));
        }
     }    

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 9

excuse me all...
i was able to get the row in sql server in this way...

object[] vals = new object[2];
vals[0] = "something";
vals[1] = DateTime.Parse(day.InputTime.Year + "/" + day.InputTime.Month + "/" + day.InputTime.Day + " " + day.InputTime.Hour + ":" + day.InputTime.Minute + ":" + day.InputTime.Second + "." + day.InputTime.Millisecond);

and pls answer me this is correct or incorrect???

Answer 10

I've tried to use a string, and this can convert well——

string time = "2012/6/6 12:12:12.122";
DateTime dt = DateTime.Parse(time);
Console.WriteLine(dt.ToString("yyyy-MM-dd HH:mm:ss zzz"));

Plz check ur datetime string is valid, just like that formation of my sample……

---

下载MSDN桌面工具(Vista,Win7)
我的博客园
慈善点击，点击此处

Answer 11

DataView dvDaily;
int iRow;
                object[] findVals = new object[2];
                findVals[0] = day.Date;
// Case 1
                findVals[1] = DateTime.Parse(day.InputTime.ToString("yyyy/MM/dd HH:mm:ss zzz"));
// Case 2
                findVals[1] = DateTime.Parse(day.InputTime.Year + "/" + day.InputTime.Month + "/" + day.InputTime.Day + " " + day.InputTime.Hour + ":" + day.InputTime.Minute + ":" + day.InputTime.Second + "." + day.InputTime.Millisecond);
                iRow = dvDaily.Find(findVals);

i can't find the row in Case 1 in my environment.
Case 1 makes {2012/08/12 11:47:05}, and Case 2 makes {2012/08/12 11:47:05 393}
i can find it in Case 2.

possibly it may be special environment???

Answer 12

DataView dvDaily;
int iRow;
                object[] findVals = new object[2];
                findVals[0] = day.Date;
// Case 1
                findVals[1] = DateTime.Parse(day.InputTime.ToString("yyyy/MM/dd HH:mm:ss zzz"));
// Case 2
                findVals[1] = DateTime.Parse(day.InputTime.Year + "/" + day.InputTime.Month + "/" + day.InputTime.Day + " " + day.InputTime.Hour + ":" + day.InputTime.Minute + ":" + day.InputTime.Second + "." + day.InputTime.Millisecond);
                iRow = dvDaily.Find(findVals);

i can't find the row in Case 1 in my environment.
Case 1 makes {2012/08/12 11:47:05}, and Case 2 makes {2012/08/12 11:47:05 393}
i can find it in Case 2.

possibly it may be special environment???

z is for time zone

f is for fractions of seconds

If you need to output the milliseconds, you can use "yyyy-MM-ddTHH:mm:ss zzz".

Now, if what you want is to create a DateTime rounded to the millisecond, you shouldn't use ToString and Parse. Parsing a string is a lengthy operation. You should create a new DateTime directly:

findVals[1] = new DateTime(day.InputTime.Year, day.InputTime.Month, day.InputTime.Day,
 day.InputTime.Hour, day.InputTime.Minute, day.InputTime.Second, day.InputTime.Millisecond);