datetime field - SQL's problem or C#? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

datetime field - SQL's problem or C#?

Author

Topic

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 12:01:29

Hello

I have a column called *dDateTime*
I insert data in to this column from my C# code.
For example I pass in the value: 2008/03/30 10:27:49.183

However, when I open the SQL Server table it will display the value as : 3/30/2008 10:27:49 AM

I’m using the convert.todatatime(X) in C# - and it looks like it changes the data to 3/30/2008 10:27:49 AM

SQL Server itself puts some kind of ms behind it – doesn’t seem to be related at all :S (but it doesn't use the ms in the uniqueness of the column, it stops at seconds)

Now here is the kicker.
The dDateTime is a unique field, thus when I insert another record with let’s say 2008/03/30 10:27:49.356 IT SHOULD BE OK!!!!!
HOWEVER, it will throw an error: Cannot insert duplicate key in object 'X'. – it ignores the milliseconds when enforcing the uniqueness.
Once the data hits the next second (2008/03/30 10:27:50.632), it will insert fine again, but will miss all data in between.

Is there a setup somewhere that I’m missing?

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 12:23:48

Whats the datatype of SQL date column? Is it smalldatetime or datetime?

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 12:26:20

It's a datetime column.

Thanks

quote:
Originally posted by visakh16

Whats the datatype of SQL date column? Is it smalldatetime or datetime?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 12:34:27

from BOL:

quote:

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.

select convert(datetime, '2008-03-30 18:31:13.997')
select convert(datetime, '2008-03-30 18:31:13.998')
both will return
2008-03-30 18:31:13.997

how data is shown in your SSMS depends on your regional settings.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-30 : 12:37:27

Then i guess it may be problem with format info. Can you try providng appropriate format info for convert to datetime function?

http://msdn2.microsoft.com/en-us/library/system.globalization.datetimeformatinfo.aspx

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 12:46:44

That's interesting.
Cause I just tested that myself.

Strangely when performing:

string thedatetime = "2008/03/30 08:43:58.392"
DateTime x = Convert.ToDateTime(thedatetime);
// x becomes {3/30/2008 8:43:58 AM}
string ms = x.Millisecond.ToString();
// ms is null

How would I go about getting the correct settings?

quote:
Originally posted by spirit1

from BOL:
quote:

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.

select convert(datetime, '2008-03-30 18:31:13.997')
select convert(datetime, '2008-03-30 18:31:13.998')
both will return
2008-03-30 18:31:13.997

how data is shown in your SSMS depends on your regional settings.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 12:56:25

well i get Convert.ToDateTime(thedatetime).Millisecond.ToString() = "392"

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 13:00:06

sorry, you are correct.
It does return 392....wasn't looking quite right in my debugger.

quote:
Originally posted by spirit1

well i get Convert.ToDateTime(thedatetime).Millisecond.ToString() = "392"

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 13:03:23

Well, i guess what it comes down to are two issues:
The value I pass on to SQL Server aka 392 does not get populated 'exactly' as I pass it......I see a ms number, but it's not anything I'm actually passing.

Second issue, there is indeed a ms value, how come it throws an error: Cannot insert duplicate key in object 'X'. - SQL Server doesn't use ms to define uniqueness of a datetime field?

Thanks!

quote:
Originally posted by spirit1

well i get Convert.ToDateTime(thedatetime).Millisecond.ToString() = "392"

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 13:08:00

yes it does but because of the rounding explained above you you'll get that error if you insert 2 rows in that short period (33 ms) of time.

if you're passing a datetime value (and not string) to a datetime parameter (and not to varchar) then all values get transfered correctly.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 13:32:11

Well, there must be something wrong in my C# code then if I'm understanding you correctly.

I'm not sure how I can accomplish this, since the value I get is from a hardware device (flat text file) thus my transferring from string to datetime....

string thedatetime = "2008/03/30 08:43:58.392"
DateTime x1 = Convert.ToDateTime(thedatetime);

string thedatetime2 = "2008/03/30 08:43:58.412"
DateTime x2 = Convert.ToDateTime(thedatetime);

Thanks for your help!

quote:
Originally posted by spirit1

yes it does but because of the rounding explained above you you'll get that error if you insert 2 rows in that short period (33 ms) of time.

if you're passing a datetime value (and not string) to a datetime parameter (and not to varchar) then all values get transfered correctly.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 13:54:11

is the interval between your inserts less than 33 ms?
how are you inserting the data?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 14:04:21

Thanks for the response.
- yes it can happen that inserts are less than 33ms apart from one another (is this an issue?)

I'm inserting my data throuhg a dataset.

aka:
taTable.Insert(
id,
Convert.ToDateTime(xdatetime1.ToString()),
Convert.ToDateTime(xdatetime2.ToString())
);

quote:
Originally posted by spirit1

is the interval between your inserts less than 33 ms?
how are you inserting the data?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 14:07:41

yes it's an issue

look at my first response in this thread on why it's an issue.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 14:12:35

AH!, I see......so my way to get around it is to insert it as a varchar then :S

quote:
Originally posted by spirit1

yes it's an issue
look at my first response in this thread on why it's an issue.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 14:13:37

no. inserting datetime as varchar will mess everything up.
don't make it unique and you'll be fine.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

raisor
Starting Member

28 Posts

Posted - 2008-03-30 : 14:15:29

Thanks for your help!

quote:
Originally posted by spirit1

yes it's an issue
look at my first response in this thread on why it's an issue.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-03-30 : 14:18:12

just don't make it varchar

it'll bite you in the behind if you do. seriously!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-03-30 : 20:57:26

Maybe you should look at
a) converting your timestamp into a count of milliseconds and storing/constraining that (possibly as well as the date/time version).
b) Knocking off the ms and having another column just to store the MS and including that in your constraint.

Just getting rid of the unique constraint sounds dodgy. Either uniqueness is a requirement or it is not. If it is not then you have nothing to worry about but you stated it was required to be unique, so you really should fix it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources