locked
SQL SERVER DATETIME VS C# DATETIME

    Question

  • i have dataset to insert my datas to sqlserver. I create datarow  and add values , save to database. up to now this is ok. But when i want to retrieve datas , it returns null. Here is code

                    thisRow["StockId"] = data[0]; 
                    thisRow["PurPrice"] = System.Convert.ToDouble(data[1]); 
                    thisRow["SellPrice"] = System.Convert.ToDouble(data[2]); 
                    thisRow["Date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); 
                    thisDataSet.Tables["Stocks"].Rows.Add(thisRow); 


    i tried everything but when i send sql query

    SELECT     StockId, PurPrice, SellPrice, Date
    FROM         Stocks
    WHERE     (Date = CONVERT(DATETIME, '07.03.2009', 103))

    it retrieves 1 row which is inserted by my hand , not actual data , inserted just for testing of query working or not.

    Then i give up working with ADO.NET and decide to write a command.

    string sqlQuery = "INSERT INTO Stocks " + 
                      "( StockId, PurPrice, SellPrice,Date ) " + 
                      "VALUES " + 
                      "(@stockid, @purprice,@sellprice, @thedate)"
     
                SqlCommand cmd = new SqlCommand(sqlQuery,thisConnection); 
                cmd.Parameters.Add ("@stockid",     SqlDbType.VarChar); 
                cmd.Parameters.Add ("@purprice",     SqlDbType.Float); 
                cmd.Parameters.Add ("@sellprice",     SqlDbType.Float); 
                cmd.Parameters.Add ("@thedate", SqlDbType.DateTime); 
     
                cmd.Parameters["@stockid"].Value = "melo"
                cmd.Parameters["@purprice"].Value = 2.54f; 
                cmd.Parameters["@sellprice"].Value = 2.66f; 
                cmd.Parameters["@thedate"].Value = DateTime.Now; 
                cmd.ExecuteNonQuery(); 

    then again i retrieved 1 query which is not above ( again test row retrieved) .

    then i tried to change sqlquery , instead of @thedate , i wrote  getdate() , but again 1 row.
    I expect 200 row..  So what should I do ? Help please..
    • Edited by Melih Ozsoy Saturday, March 07, 2009 9:55 PM
    Saturday, March 07, 2009 4:38 PM

Answers

  •  

    Hello,

    As far as I know, there are better methods to query datetime values from SQL Server.  

    We can use such a SQL command to check whether the date of the datetime value is today:

    select DATEDIFF(dd,getdate(),convert(datetime,'2009-3-12'))

    If the query returns 0, then the data value is 2009-3-12. 

    For more detailed information on SQL datetime query skills, please see

    http://www.karaszi.com/SQLServer/info_datetime.asp
    http://www.databasejournal.com/features/mssql/article.php/2191631/Working-with-SQL-Server-DateTime-Variables.htm

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, March 12, 2009 7:24 AM
    Moderator
  •  

    Hello,

     

    Sorry for the delay.

     

    As far as I know, the .NET DateTime can represent a larger scope of date time than the datetime type in SQL Server 2005.  The .NET DateTime can demonstrate the time from 0001/01/01 to 9999/12/31 and the zero time is 0001/01/01.  The SQL Server 2005 datetime can only display the time from 1753/01/01 to 9999/12/31 and the zero time starts from 1900/01/01, the time before the year 1900 is represented by negative values. 

     

    However, the SQL Server 2005 datetime has better accuracy than the .NET DateTime.  The time accuracy of the SQL Server 2005 datetime is 1/300 second while the time accuracy of .NET DateTime is 1/10 second. 

     

    Important to mention, some new datetime types have been introduced into SQL Server 2008.  For detail, please see:  The ultimate guide to the datetime datatypes.

     

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, March 19, 2009 1:05 PM
    Moderator
  •  First the hours are not capitalized. Secondly, does you DateTime column in fact a string type? So, you are saving the DateTime as a string? This is rather irrational. Your string is very long and DateTime is only 8 bytes. You are wasting a lot of space in your Server.

    If you are saving your DateTime values by first converting them to strings and they somehow end you being DateTime which I am not sure is possible then I really have no comment.

    I think you should save DateTime stamp as ticks but truncated to the nearst seconds or perhaps you don't need even seconds, I don't know. Some save just Date. If you save DateTime.Now you will get all microseconds alive at this moment. By a shear accident this time may end up being a round second with all milliseconds, microseconds and ticks being zeros.

    AlexB
    Monday, March 09, 2009 5:57 PM

All replies

  •  (Date = CONVERT(DATETIME, '07.03.2009', 103))

    This query matches midnight of 7/3/2009.

    Be aware when you insert the data that you are inserting a date only.  You can use the .NET DateTime's Date property to remove the time part.

     
     
    cmd.Parameters["@p_birthdate"].Value = DateTime.Now.Date; // or just use DateTime.Today instead  
     
     
    Saturday, March 07, 2009 6:59 PM
  • Yes you are right , when write only date , it assumes 00:00:00, but lets say i have a data row in db that has a date for example '07.03.2009 18:15:00'
    then i send a query that   ..... where Date = convert(datetime,'07.03.2009 18:15:00',103) , so it return nothing..
    Saturday, March 07, 2009 8:55 PM
  •  so it return nothing..

    It should.

    When you store a DateTime.Now value a long or int64 integer is stored which is the number of ticks at the moment. What you see in the SSMS is the roudup of that up to one second. One tick is one hudnred microseconds. Therefore you are unable to see four significant digits. Those digits give you the mismatch.

    This is how youc an handle it. You should always store your value rounded up to 1 sec by dividing it (modularily ) by 10,000,000. This example rounds the time up to 2 seconds since for my purposes this precision is enough:

                    DateTime dt0 = DateTime.Now;  
                    dt0dt0 = dt0.AddTicks ( -(dt0.Ticks % 20000000) ); 
    1 sec:

                    DateTime dt0 = DateTime.Now;  
                    dt0dt0 = dt0.AddTicks ( -(dt0.Ticks % 20000000) ); 



    AlexB
    Sunday, March 08, 2009 4:12 AM
  • Alex,  But i save to Date column in database like DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); so it is supposed to change to that format right ? or are you saying that when i save Date column as DateTime.Now so it saves also micro seconds and it doesnt match with the row beacuse i dont send with including microsecond in my query . Can you explain more please ?
    Sunday, March 08, 2009 6:08 PM
  •  First the hours are not capitalized. Secondly, does you DateTime column in fact a string type? So, you are saving the DateTime as a string? This is rather irrational. Your string is very long and DateTime is only 8 bytes. You are wasting a lot of space in your Server.

    If you are saving your DateTime values by first converting them to strings and they somehow end you being DateTime which I am not sure is possible then I really have no comment.

    I think you should save DateTime stamp as ticks but truncated to the nearst seconds or perhaps you don't need even seconds, I don't know. Some save just Date. If you save DateTime.Now you will get all microseconds alive at this moment. By a shear accident this time may end up being a round second with all milliseconds, microseconds and ticks being zeros.

    AlexB
    Monday, March 09, 2009 5:57 PM
  • AlexBB said:

     First the hours are not capitalized.


    AlexB



    Alex's statement is correct.  Save the data as a date type.  Format shouldn't matter at all.  This seems more like a sql question than anything else.

    As for the hours being capitalized.  "HH" is the 24-hour format, while "hh" is the 12-hour format, so "HH" is valid.
    David Morton - http://blog.davemorton.net/
    Monday, March 09, 2009 6:03 PM
    Moderator
  •  
    "HH" is the 24-hour format, while "hh" is the 12-hour format, so "HH" is valid.

    Nice to know I missed that.

    AlexB
    Monday, March 09, 2009 8:14 PM
  • dear friends , i save  DateTime.Now without ToString function , but then i want to test it and make it ... ToString("yyyy-MM-dd .. 
    but forget about it . In my database , Date column 's type is datetime . So on ado.net , i save my value as

    thisRow["Date"] = DateTime.Now

    but again with 

    SELECT     StockId, PurPrice, SellPrice, Date
    FROM         Stocks
    WHERE     (Date = CONVERT(DATETIME, '07.03.2009', 103))


    it returns nothing but a row that i insert with manually for just testing. But then i tried to send query

    SELECT StockId, PurPrice, SellPrice,Date  FROM Stocks WHERE (Date BETWEEN '20090204 00:00:00' AND '20090311 00:00:00' AND StockId='AVIVA')

    then it works although it gives me an error that "Your entry cannot be converted to a valid date time value." But then i click OK and it retrieves me result. So what's the point of getting error while it is working ? Why does my first query not work ?
    Monday, March 09, 2009 11:10 PM
  •  

    Hello,

    As far as I know, there are better methods to query datetime values from SQL Server.  

    We can use such a SQL command to check whether the date of the datetime value is today:

    select DATEDIFF(dd,getdate(),convert(datetime,'2009-3-12'))

    If the query returns 0, then the data value is 2009-3-12. 

    For more detailed information on SQL datetime query skills, please see

    http://www.karaszi.com/SQLServer/info_datetime.asp
    http://www.databasejournal.com/features/mssql/article.php/2191631/Working-with-SQL-Server-DateTime-Variables.htm

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, March 12, 2009 7:24 AM
    Moderator
  • Thanks Lingzhi , but i dont understand what's difference between sql server datetime and c# ?
    Thursday, March 12, 2009 7:50 AM
  •  

    Hello,

     

    Sorry for the delay.

     

    As far as I know, the .NET DateTime can represent a larger scope of date time than the datetime type in SQL Server 2005.  The .NET DateTime can demonstrate the time from 0001/01/01 to 9999/12/31 and the zero time is 0001/01/01.  The SQL Server 2005 datetime can only display the time from 1753/01/01 to 9999/12/31 and the zero time starts from 1900/01/01, the time before the year 1900 is represented by negative values. 

     

    However, the SQL Server 2005 datetime has better accuracy than the .NET DateTime.  The time accuracy of the SQL Server 2005 datetime is 1/300 second while the time accuracy of .NET DateTime is 1/10 second. 

     

    Important to mention, some new datetime types have been introduced into SQL Server 2008.  For detail, please see:  The ultimate guide to the datetime datatypes.

     

     

    Best Regards,
    Lingzhi


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, March 19, 2009 1:05 PM
    Moderator
  •  the time before the year 1900 is represented by negative values. 

    Time machine? I desperately want to be there:)

    AlexB
    Thursday, March 19, 2009 8:27 PM
  • no one can still solve my problem as i mentioned in my first post
    Saturday, March 21, 2009 3:17 PM
  • VitoBonessi said:

    no one can still solve my problem as i mentioned in my first post


    Wait a second, what is your poblem? 

    thisRow["Date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); 

    Is the type of your column DateTime? If so then why are you inputting the string type value for it? And what is the compile error?

    then i tried to change sqlquery , instead of @thedate , i wrote  getdate() , but again 1 row.
    I expect 200 row.. 

    What does it all mean? Could you clearly post teh code to illustrate this all?

    Provide ALL compile errors with teh corresponding codes in the clearest of fashions.

     



    AlexB
    Saturday, March 21, 2009 10:05 PM
  • SELECT     StockId, PurPrice, SellPrice, Date
    FROM         Stocks
    WHERE     (Date = CONVERT(DATETIME, '07/03/2009', 103))


    and i get 1 row which is manually inserted by me to my database. But i have 200 row similar but i cant get result although date value of other rows is same with my manually inserted row. the difference is 200 row is inserted by c# , and  one by me.

    i cant understand why other rows dont seem in the result. for example both my manually inserted row and other rows have same value " 07.03.2009 00:00:00 " no difference at all but when i send query , i get 1 row of mine

    c# code :  

    thisRow["Date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); 
                    
    Sunday, March 22, 2009 7:31 PM
  • Vito, all this is quite confusing. If your database column is DateTime, that would mean that your DataTable column would also be DateTime. If so, why are you showing code that sets the value of the column equal to DateTime.Now.ToString()? It's not a string.
    ~~Bonnie Berent [C# MVP]
    Sunday, March 22, 2009 9:01 PM
  • VitoBonessi said:

    SELECT     StockId, PurPrice, SellPrice, Date
    FROM         Stocks
    WHERE     (Date = CONVERT(DATETIME, '07/03/2009', 103))


    and i get 1 row which is manually inserted by me to my database. But i have 200 row similar but i cant get result although date value of other rows is same with my manually inserted row. the difference is 200 row is inserted by c# , and  one by me.

    i cant understand why other rows dont seem in the result. for example both my manually inserted row and other rows have same value " 07.03.2009 00:00:00 " no difference at all but when i send query , i get 1 row of mine

    c# code :  

    thisRow["Date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); 
                    



    Oh gosh, you are again playing with that string. If you somehow saving that DateTime with all hours, minutes and seconds you can only retrieve one record apparently. Still it is totally unclear what you do. If you want any help you must post a significant chunk of code and save people from guessing.
    AlexB
    Monday, March 23, 2009 2:05 AM