none
Issue with dates RRS feed

  • Question

  • Hi all of you,

    I have got a problem with Dates.

    Using the same values, whether I launch my stored procedure directly I get three rows, however from my .NET front I get two of them.

    I can't work out. I've decided define the fields implied in this problem as smalldatetime but everything remains the same. Help!

    From my .NET app (I am currently using datetimepicker stuff)

    Dim meuDs as DataSet
    newFra.Fecha_fac = Me.dtpDesde.Value
    newFra.Fecha_fin = Me.dtpHasta.Value
    
    meuDS = newFra.SeleccionarTodas()
    ...
    
    Public Function SeleccionarTodas() As DataSet
    
        Dim dtsFacturas As New DataSet
        Dim sdaFacturas As New SqlDataAdapter
    
        accesoBaseDatos.abrirCon()
        cmdFacturas = New SqlCommand("OR_Seleccionar_Facturas", accesoBaseDatos.connection)
    
         cmdFacturas.Parameters.Add("@finicio", SqlDbType.SmallDateTime)
         cmdFacturas.Parameters("@finicio").Value = Fecha_fac
    
         cmdFacturas.Parameters.Add("@ffin", SqlDbType.SmallDateTime)
         cmdFacturas.Parameters("@ffin").Value = Fecha_fin
    
         cmdFacturas.CommandType = CommandType.StoredProcedure
    
         sdaFacturas.SelectCommand = cmdFacturas
         sdaFacturas.Fill(dtsFacturas, "OR_Seleccionar_Facturas")
    
    Return dtsFacturas
    End Function
    
    

    And then my stored procedure:

    PROCEDURE [dbo].[OR_Seleccionar_Facturas]
        @finicio as smalldatetime,
        @ffin as smalldatetime
    AS
    BEGIN

    SET NOCOUNT ON;
    select
    *
    from talleres_facturas_or
    WHERE CONVERT(CHAR(8), talleres_facturas_or.fecha_fac , 112) >= @finicio  
    and CONVERT(CHAR(8), talleres_facturas_or.fecha_fac , 112) <= @ffin  

        

    Enric

    Tuesday, August 7, 2012 12:57 PM

Answers

  • // Set the Format type and the CustomFormat string.
       dateTimePicker1.Format = DateTimePickerFormat.Custom;
       dateTimePicker1.CustomFormat = "MMMM dd, yyyy - dddd";

    http://msdn.microsoft.com/en-us/library/system.windows.forms.datetimepicker.customformat.aspx
    • Marked as answer by Enric Vives Tuesday, August 7, 2012 3:29 PM
    Tuesday, August 7, 2012 3:22 PM
  • I am not sure about what could be the problem unless I dig more into it. But, I think the issue is related to SqlType only. Without worrying about the types, try below code

    Public Function SeleccionarTodas() As DataSet Dim dtsFacturas As New DataSet Dim sdaFacturas As New SqlDataAdapter accesoBaseDatos.abrirCon() cmdFacturas = New SqlCommand("OR_Seleccionar_Facturas", accesoBaseDatos.connection) cmdFacturas.Parameters.AddWithValue("@finicio", Fecha_fac) cmdFacturas.Parameters.AddWithValue("@ffin", Fecha_fin) cmdFacturas.CommandType = CommandType.StoredProcedure

    sdaFacturas.Fill(dtsFacturas, "OR_Seleccionar_Facturas") Return dtsFacturas End Function

    Let me know if you still get problems.

    Please mark this post as answer if it solved your problem. Happy Programming!

    • Marked as answer by Enric Vives Tuesday, August 7, 2012 3:06 PM
    Tuesday, August 7, 2012 2:57 PM

All replies

  • There is a probability that data would be treated differently while conversion.

    There is a good MSDN article showing DateTime conversion for SQL @ http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Tuesday, August 7, 2012 1:51 PM
  • I am not sure about what could be the problem unless I dig more into it. But, I think the issue is related to SqlType only. Without worrying about the types, try below code

    Public Function SeleccionarTodas() As DataSet Dim dtsFacturas As New DataSet Dim sdaFacturas As New SqlDataAdapter accesoBaseDatos.abrirCon() cmdFacturas = New SqlCommand("OR_Seleccionar_Facturas", accesoBaseDatos.connection) cmdFacturas.Parameters.AddWithValue("@finicio", Fecha_fac) cmdFacturas.Parameters.AddWithValue("@ffin", Fecha_fin) cmdFacturas.CommandType = CommandType.StoredProcedure

    sdaFacturas.Fill(dtsFacturas, "OR_Seleccionar_Facturas") Return dtsFacturas End Function

    Let me know if you still get problems.

    Please mark this post as answer if it solved your problem. Happy Programming!

    • Marked as answer by Enric Vives Tuesday, August 7, 2012 3:06 PM
    Tuesday, August 7, 2012 2:57 PM
  • Hi Adavesh,

    Have done but I still have problems, thanks for your time.

    Something related to sqltype? My searched field is defined as Smalldatetime


    Enric

    Tuesday, August 7, 2012 3:06 PM
  • Jesus christ, I am seeing why.

    When I pick up the value from my datetimepicker I get this: 8/4/2012 5:09:27 PM#

    instead of  8/4/2012 00:00:00 (the controls just allows you choose date no time!!!)

    So the problem comes in my Sql Server table where I have this: 2012-08-04 13:33:00

    Let me know how can I choose a date from my DateTimePicker avoiding the real time in my area (Spain) 17:09

    Thanks,


    Enric

    Tuesday, August 7, 2012 3:13 PM
  • // Set the Format type and the CustomFormat string.
       dateTimePicker1.Format = DateTimePickerFormat.Custom;
       dateTimePicker1.CustomFormat = "MMMM dd, yyyy - dddd";

    http://msdn.microsoft.com/en-us/library/system.windows.forms.datetimepicker.customformat.aspx
    • Marked as answer by Enric Vives Tuesday, August 7, 2012 3:29 PM
    Tuesday, August 7, 2012 3:22 PM
  • It is solved. Thank you so much.

    Enric

    Tuesday, August 7, 2012 3:29 PM