none
SQL Server 2008 .. problem whit convert datetime on production server. RRS feed

  • Question

  • Hi to all,

    i've this problem:
    on my pc: i've installed windows 7 ita, whit sql server 2008 express ita. I've done a windows form application whit visual studio 2010. It works very nice. 

    inside these application i use much these type of convertion of date:

    CONVERT(DATETIME, '" & var & "', 102)

    where the type of var is in these format AAAA-MM-DD HH:MM:SSS. if i work on local i don't have problem.
    on production i've windows server 2003 r2 eng, whit sql server 2008 eng standard edition, the user that connect to database was Windows Authentication on all two case. 

    If i run the program on production server it works and no give me any error, but all the record inside the table on database have NULL value when these value needed to take from a dateoperation .. i think it's a conversion problem of datetime. 

    can any help me to know how i can set the right conversion form test server to production server?

    thanks very much,

    Friday, June 18, 2010 10:37 AM

Answers

  • Double-check the data on the production server. For me it is not clear whether you have NULLs in the datefields of your database or if you only get NULLs when you do your query.

    Also, start SQL Server Monitor and take a look at the SQL statement generated by the following code:

    Imports System.Data.SqlClient
    
    Module Module1
      Sub Main()
        Dim ticket_id As String = "101" ' change to values present in the DB
        Dim inizio As String = "2010-06-17 00:00:00" 
        Dim fine As String = "2010-06-18 00:00:00"
    
        Using conn As New SqlConnection("Data source=[YourDatasource];Initial Catalog=[YourCatalog];Integrated Security=true")
    
          Dim sql As String = String.Format("SELECT MIN(data_ora_stato) AS data " + _
                    "FROM tbl_ticket " + _
                    "WHERE (ticket_id = {0}) AND (stato = N'0') " + _
                    "GROUP BY stato, ticket_id " + _
                    "HAVING (MIN(data_ora_stato) " + _
                    "BETWEEN CONVERT(DATETIME, '{1}', 21) " + _
                    "AND CONVERT(DATETIME, '{2}', 21))", _
                    ticket_id, _
                    inizio, _
                    fine)
    
          conn.Open()
    
          Dim data_apertura As New SqlCommand(sql, conn)
    
          Using reader As SqlDataReader = data_apertura.ExecuteReader()
            While (reader.Read())
              Console.WriteLine(reader.GetDateTime(0))
            End While
          End Using
        End Using
    
        Console.Write(ControlChars.Lf + "Premi un tasto per finire...")
        Console.ReadKey(True)
      End Sub
    
    End Module

    Does this work with both servers?

    Marcel

    Friday, June 18, 2010 8:25 PM

All replies

  • Hi Maurizio,

    Your string's format seems not to match the format number 102. Take a look at the list of valid formats here:
    http://databases.aspfaq.com/database/what-are-the-valid-styles-for-converting-datetime-to-string.html

    Maybe you need to use 20, 21, 25, 120 or 121?

    CAST and CONVERT (Transact-SQL):
    http://msdn.microsoft.com/en-us/library/ms187928.aspx

     

    Marcel

    Friday, June 18, 2010 11:03 AM
  • i've tryed to change the code of convert and nothing was appear:

    see these example:

    ticket_id protocollo data_apertura data_chiusura cliente_id sito_id descrizione_tipo_ticket descrizione_soluzione_ticket mese anno

    112646 2009018268 2009-01-02 00:00:00.000 2009-01-02 09:15:27.000 30 23621 Ripristino linea TD 2 2009

    this's the record on my test server .. see the date.. i was selected code 21 .. so is the 1th of Feb 2009. and it's ok.

    on the production test:

    112646 2009018268 NULL NULL 30 23621 NULL NULL 2 2009

    the first 2 value NULL are the data and the other two NULL are string.
    this' are the code for take the first two date:

     ' Cerco risultati ticket .. per quanto riguarda data, ora, competenza, apertura e chiusura

     Dim data_apertura As New SqlCommand("SELECT MIN(data_ora_stato) AS data FROM tbl_ticket WHERE (ticket_id = " & ticket_id & ") AND (stato = N'0') GROUP BY stato, ticket_id HAVING (MIN(data_ora_stato) BETWEEN CONVERT(DATETIME, '" & dtp_inizio_osservazione.Text & "', 21) AND CONVERT(DATETIME, '" & dtp_fine_osservazione.Text & "', 21))", conn)

    Dim data_chiusura As New SqlCommand("SELECT MIN(data_ora_stato) AS data FROM tbl_ticket WHERE (ticket_id = " & ticket_id & ") AND (stato = N'99') GROUP BY stato, ticket_id HAVING (MIN(data_ora_stato) BETWEEN CONVERT(DATETIME, '" & dtp_inizio_osservazione.Text & "', 21) AND CONVERT(DATETIME, '" & dtp_fine_osservazione.Text & "', 21))", conn)

    and for the other 2 i have these:

                Dim descrizione_tipo_ticket As New SqlCommand("SELECT descrizione_tipo_ticket FROM tbl_ticket WHERE (ticket_id = " & ticket_id & ") AND (stato = N'0') GROUP BY descrizione_tipo_ticket HAVING (MIN(data_ora_stato) BETWEEN CONVERT(DATETIME, '" & dtp_inizio_osservazione.Text & "', 21) AND CONVERT(DATETIME, '" & dtp_fine_osservazione.Text & "', 21))", conn)
                Dim descrizione_soluzione As New SqlCommand("SELECT descrizione_soluzione_ticket FROM tbl_ticket WHERE (ticket_id = " & ticket_id & ") AND (stato = N'99') GROUP BY descrizione_soluzione_ticket HAVING (MIN(data_ora_stato) BETWEEN CONVERT(DATETIME, '" & dtp_inizio_osservazione.Text & "', 21) AND CONVERT(DATETIME, '" & dtp_fine_osservazione.Text & "', 21))", conn)

    the input parameter from datepick time was on format yyyy-MM-dd 00:00:00 for the first and yyyy-MM-dd 23:59:59 for the second.

    but don't work.
    can help me?

    thanks,

    Friday, June 18, 2010 12:06 PM
  • Double-check the data on the production server. For me it is not clear whether you have NULLs in the datefields of your database or if you only get NULLs when you do your query.

    Also, start SQL Server Monitor and take a look at the SQL statement generated by the following code:

    Imports System.Data.SqlClient
    
    Module Module1
      Sub Main()
        Dim ticket_id As String = "101" ' change to values present in the DB
        Dim inizio As String = "2010-06-17 00:00:00" 
        Dim fine As String = "2010-06-18 00:00:00"
    
        Using conn As New SqlConnection("Data source=[YourDatasource];Initial Catalog=[YourCatalog];Integrated Security=true")
    
          Dim sql As String = String.Format("SELECT MIN(data_ora_stato) AS data " + _
                    "FROM tbl_ticket " + _
                    "WHERE (ticket_id = {0}) AND (stato = N'0') " + _
                    "GROUP BY stato, ticket_id " + _
                    "HAVING (MIN(data_ora_stato) " + _
                    "BETWEEN CONVERT(DATETIME, '{1}', 21) " + _
                    "AND CONVERT(DATETIME, '{2}', 21))", _
                    ticket_id, _
                    inizio, _
                    fine)
    
          conn.Open()
    
          Dim data_apertura As New SqlCommand(sql, conn)
    
          Using reader As SqlDataReader = data_apertura.ExecuteReader()
            While (reader.Read())
              Console.WriteLine(reader.GetDateTime(0))
            End While
          End Using
        End Using
    
        Console.Write(ControlChars.Lf + "Premi un tasto per finire...")
        Console.ReadKey(True)
      End Sub
    
    End Module

    Does this work with both servers?

    Marcel

    Friday, June 18, 2010 8:25 PM
  • Hi,

     

    I am writing to check the status of the issue on your side.  Would you mind letting us know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun

    MSDN Subscriber Support in Forum

    If you have any feedback on our support, please contact msdnmg@microsoft.com


    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.
    Wednesday, June 23, 2010 2:00 AM
    Moderator