.NET DateTime and SQL Server DateTime problem

.NET DateTime and SQL Server DateTime problem


I have the following if statement in an SQL stored procedure:

 IF (@Image = 1 AND @DateTimeUploaded = (SELECT ImageMainDateTime FROM Images WHERE UserID = @UserID))  

The value of the @DateTimeUpdated variable was taken from the ImageMainDateTime field when the data was requested by a ASP.NET webpage and is passed back here when that page is submitted so I can compare what the DateTime was when the data was requested with what it is now, in other words I’m doing a concurrency check (I can’t use TimeStamp for many reasons, just trust me on that).


Now the problem I’m having is the DateTime comparison always fails even though I know the DateTime hasn’t changed. I’ve stepped through my code meticulously and I know it hasn’t changed, in any case as the system is only being tested I know that no one else can access it so no changes can be made other than by me.


So why is this failing? Is there something I don’t know about DateTime comparisons? Am I missing something here?


Any help much appreciated.




8/12/2008 2:07:22 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

9 Replies

Similar Articles

[PageSpeed] 1

Do you have multiple entries in your Images table for a single user?

8/12/2008 2:20:56 PM
No just one for each user.
8/12/2008 2:28:17 PM

Im wondering if there isn't some kind of type conversion issue... did you try separating the query?


SELECT @blah = ImageMainDateTime FROM Images WHERE UserID = @UserID

Then your conditional statement, but with @blah instead of the query in parenthesis?

I know I'm not being very useful :) But who knows...


8/12/2008 2:41:40 PM

 Thanks Shados, and you are being helpful as I'm all out of ideas!!! I did try breaking up the query in query analyzer and I did notice that the DateTime being returned from the table included milliseconds even though those milliseconds do not appear in the field in the table itself or when the DateTime is being passed to and from the ASP.NET webpage. Still I assumed this was causing the comparison failure so I altered the if statement to do a <= comparison like so:


IF (@Image = 1 AND @DateTimeUploaded <= (SELECT ImageMainDateTime FROM Images WHERE UserID = @UserID))

 And this did work but I'm unsure of what pitfalls there might be doing it this way so I changed it back.




8/12/2008 2:54:22 PM

Its because in the background, datetimes ARE stored in milliseconds... what you see when you browse it isn't how it is. Its normally not an issue, because if you select the value, you'll get back those milliseconds too, and things will match up, but I'm not sure how you're manipulating the date on the .NET side, as long as you're not converting the date to string in any part of the operations.

8/12/2008 3:02:31 PM

Ok, I was converting the DateTime values to objects in my class (I had an issue with null values which is why) but I've just changed them to DateTime values and the problem still occurred, however once the values reach the GridView I am storing them in a hidden field where I pick them up again when passing the data back to sql server, so I assume this is where it is going wrong as I believe in a hidden field the data will be held as a string. Is that correct? If so what should I store them in once they are in the GridView to ensure they stay as DateTime values that the database will be ok with?

I'm starting to think that maybe I should just have an int field instead of a DateTime field in the database table. I can retreive the int field data, and should the user make any changes in the meantime I simply increment it. I can then make a comparison with the int data value I returned with what is there when updating, which will be different if the user made changes in the meantime, I'm sure this would be far less of a headache.

8/12/2008 4:39:48 PM

In the database, the date is -already- a numeric value :) (Its the amount of milliseconds since 1970 or some such).

For null values, you know you can simply declare your date as nullable? In C# its simply DateTime? Blah; (notice the interrogation mark). In VB.NET, you use the Nullable generic type.

For storing the date, why don't you simply store them in the ViewState instead of a custom hidden field? That way you will concerve type information.

If you MUST use a normal hidden field (I still heavily suggest ViewState, so make sure you have a REALLY good reason not to use it), the DateTime type has a "ToBinary" method, which will return a representation as a "long" of the date. You can then later restore it using DateTime.FromBinary. That should make sure you don't have any date format issues.

8/12/2008 4:59:08 PM

Thanks Shados, have managed to sort it out with the ToBinary FromBinary methods you suggested and with a bit of messing about. I tried the nullable DateTime but as the values are being assigned in a class constructor in the data layer I couldn't get that to work. Also wasn't sure about how to use viewstate to hold the value for each row in a GridView, my knowledge isn't on your level I'm afraid, so I was stuck with the hidden fields. Thanks tremendously for your help though, much appreciated.


8/12/2008 10:27:51 PM

Ahh, if you're needing one per row, you could have stuck an array in viewstate, but thats awkward... The solution you used is just fine. Thats why I made sure to give you several options, so that at least one would suit your need. Doesn't have anything to do with a lower level of knowledge... what you did is just fine :)

8/13/2008 6:52:38 PM

Similar Artilces:

Convert .NET DateTime format to SQL DateTime format
Hi!!I'd like to know if there is a function to convert the DateTime format used in C# (VS2005) : dd/mm/yyyy HH:mm:ss to the format used in SQL Databases: yyyymmdd HH:mm:ssThanks folks!...

DateTime conversion error between SQL Server and .NET?
Not sure where best to post this because I am not sure where the error is occuring.... but....  I'm taking a DateTime in C#, and saving it to SQL Server 2005 as a DateTime.  When it is reloaded out of the DB back into C# (as a DateTime), I'm seeing between a few microseconds and so far up to 2 milliseconds in difference.  The process I'm using, takes a DateTime and saves it to SQL Server.  This DateTime is then converted ToBinary().ToString() and saved temporarily in in C#. Then I re-load the DateTime that was saved in SQL ...

convert an sql datetime column to .net datetime data type in front end...?
I have a field (ldate) in a table that typically displays data in the following format:2007-11-12 20:30:47.000In the front end I want the time and the date separately. But as I try to cast it to a datetime object it is giving me a System.NullReferenceException. What I tried to do was just this: Protected Sub gvTopics_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvTopics.RowDataBound Dim img As Image = gvTopics.FindControl("imgIcon") Dim lbDate, lbTime As Label lbDate = gvTopics.FindControl(...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Problem datetime when transferring local SQL Server to Web Hosting SQL Server using XML Webservice
Dear all, We have recently integrated our internal timetracking (TT) system (Windows forms based )so that it can retrieve info from our (web based) bug tracking (BT) system. The BTS SQL Server is hosted in the USA on a shared server, the TT SQL server is stationed at our office in Phnom Penh, Cambodia. During development we tested the XML webservice that handles the actual datatransfer between the two systems and everything works fine. After deployment of the XML webservice to the live environment (hosted service in USA) problems showed up: DateTime values arrive malformed. Examples:...

.NET Datetime object
I want to know everything about the DateTime Class ,I mean where can I find the information about this class . You may find some help from here.This posting is provided "AS IS" with no warranties, and confers no rights. Do below link helps you for your need! It will have all the info what you want! http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstopic.asp Hope it helps!Sreedharhttp://www.w3coder.orgweblog http://weblogs.asp.net/skoganti...

Issue when converting XML dateTime to .NET DateTime
Hi, I am experiencing an issue when deserializing XML dateTime nodes in ASP.NET in some timezones. The construct 0001-01-01T00:00:00.0000000+ZZ:ZZ where ZZ:ZZ is the timezone offset for the timezone on the current system should produce a DateTime value equalling the constant DateTime.MinValue when converted. This works some of the time, such as for the Paris time offset of +1: (CDate("0001-01-01T00:00:00.0000000+01:00") = DateTime.MinValue) = True But does not work for Sydney time offset of +10: (CDate("0001-01-01T00:00:00.0000000+10:00") = DateTime....

Datetime problem in vb.net
Hi all, I want to increment a datetime in vb.net. how can i do that. for example: i want to increment the day by 1 Dim MyDate as Date = some date MyDate = MyDate + 1 Thanks, HenryToronto Trade show displays | Toronto Printing Solutions | Toronto Printing How about MyDate.AddDays(1) hope this helps, sivilian My Weblogs Thanks sivilian, It worked!!!! I had another question...how do i subtract the days HenryToronto Trade show displays | Toronto Printing Solutions | Toronto Printing MyDate.AddDays(-1) David WierMCP/ASPInsiderASPNet...

Compating DateTime field from sql table to DateTime Object
Hi. I have a SQL query that suppose to compare a given DateTime object to a datetime type coloumn : SELECT COUNT(*) FROM [QuestionnairesData] WHERE [SQClientUsername] = '" + Profile.UserName + "' AND [FillingDate] > " + Report.GetLastProductionReportDate(sqClient.SQCLIENTID)  the problem i get is on the last part of the select query. the DateTime object Report.GetLastProductionReportDate(sqClient.SQCLIENTID) gives me : 2/14/2007 15:49:04 PM and when i delete the time part it works well but i need to check for both date and time in this case. there is some issues with DateTime o...

Insert datetime, using ADO.net VB.net
Hello there,I got a little code there are inserting a record into my msSQL database..But i cant insert the datetime, for one reason?The problem is in line 7 () where i want to insert DateTime.Now 1 Protected Sub SendPmTilAfviste(ByVal modtager As String, ByVal festID As String) 2 ' Connection 3 Dim conn As SqlConnection = New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True") 4 conn.Open() 5 6 ' SQL-kommandoen 7 Dim cm...

C# DateTime and SQL Server "DateTime" column type
If I have a property in a class that will be set to a date returned from a SQL Server DateTime column... Should my property be of type "string" or type "DateTime". Hi Son Volt,You would have to use DateTime type in your class.Good Coding!Javier Luna http://guydotnetxmlwebservices.blogspot.com/from Villa El Salvador for world!...

problem with datetime datatype in sql server 2000
Hi all, i  am trying to retrieve  records on a  particular datei wrote the following query,but no records were found  select fname from regdetails where  insertdate=' 07/08/2008' here  insertdate  is  datetime  field in my table  if i write the query along with time its working fine ,but i dont want to consider time  can anybody help me out in solving this problem thanks in advance ,madhavi  hi try this link http://aspadvice.com/blogs/ssmith/archive/2006/03/24/15952.aspx thanks and regardsPrahlad Kumar Sharma ...

problems connecting to my SQL server database in .NET
I am able to connect and display data in classic asp just fine, but I cannot connect to my database using ASP.NET. I have tried 2 tutorials on here and neither has worked. They both give me the same error: "Login failed for user 'NASHIE\ASPNET'." I am currently working on this tutorial: http://asp.net/webmatrix/tour/section3/binddatagrid.aspx "Display Data with MxDataGrid" [wmx:SqlDataSourceControl id="SqlDataSourceControl1" runat="server" UpdateCommand="" SelectCommand="SELECT * FROM [Orders]" ConnectionString="...

Running .NET 2.0 and .NET 1.1 apps on same server. Problem???
I have .NET 1.1 running on remote (dedicated) server.  I have just installed Net 2.0 and created a Hello-World App to test it.  It worked fine yesterday, but today when I try it again I get "Server Application Unavailable" My 1.1 web apps seem to be running ok.  The following message was in the event log.  "It is not possible to run two different versions of ASP.NET in the same IIS process. Please use the IIS Administration Tool to reconfigure your server to run the application in a separate process"Can someone please advise me how to run 1.1 and 2.0 web apps on the sam...

Web resources about - .NET DateTime and SQL Server DateTime problem - asp.net.object-datasource

Is there a JQuery plugin to convert UTC datetimes to local user timezone?
If I have a tag: &lt;span class="utctime"&gt;2010-01-01 11:30 PM&lt;/span&gt; I would like a jquery script or plug in to convert every utctime ...

Targetprocess 3.7.3: Epic unit for Bug/Story cards, new DateTime functions in Webhook plugin
Webhook plugin supports new DateTime functions AddDays(DateTime date, int days) – Returns a new date with the number of days added. The number ...

Common Pitfalls when working with DateTime’s
In .NET, the DateTime structure provides us wonderful functionality, but this seemingly simple structure can cause a lot of headaches if you ...

Parsing Twitter Date format to .Net DateTime
Recently when I needed to parse the twitter datetime format into a .Net DateTime I found this post on the web showing how to do it. However, ...

C Sharp (programming language) - Wikipedia, the free encyclopedia
(pronounced see sharp ) is a multi-paradigm programming language encompassing strong typing , imperative , declarative , functional , generic ...

1753 - Wikipedia, the free encyclopedia
Language: English Afrikaans አማርኛ العربية Aragonés Asturianu Aymar aru Azərbaycanca বাংলা Bân-lâm-gú Basa Banyumasan Беларуская ‪Беларуская (тарашкевіца)‬ ...

Coded Smorgasbord: Schizophrenic Haiku Comments and More
... writes Malcom StandardResponse UnSubscribeNewsletterUserAccount( string opTinGUID,string email, string sellingRegion, string source, DateTime ...

Limburg.be - Welkom op de site van de provincie Limburg - Welkom_op_de_site_van_de_provincie_Limburg
visit wonen werken leven ontspannen infopunt home op trefwoord contactpersonen wegwijs sitemap print deze pagina Je bent hier: limburg.be Contact ...

Simples Ideias. Por Nando Vieira.
Simples Ideias Siga-me no Twitter Junho/2012 Usando o Vagrant como ambiente de desenvolvimento no Windows http://simplesideias.com.br/usando ...

Resources last updated: 1/22/2016 10:10:43 PM