none
Convert date exception RRS feed

  • Question

  • I want to display a ExpiryDate 2018-02-02 as 02/02/2018 in visual studio.  I've tried convert 

    Select CONVERT(date,ExpiryDate,103) from table

    but I get an exception when reading ExpiryDate using SqlDataReader.  The exception message just says ExpiryDate.  

    Thursday, February 1, 2018 8:22 PM

All replies

  • Are you sure that in ExpireDate you have only dates? Try doing select ExpiryDate from Table order by ExpiryDate and select ExpiryDate from Table order by ExpiryDate desc and see if there is some strange value around.

    Please mark as answer if this post helped you

     
    Thursday, February 1, 2018 9:06 PM
  • Without doing convert, both Month and Day display without leading 0's, 2/2/2018, so I am trying to figure out what is causing that.  
    Thursday, February 1, 2018 9:21 PM
  • Hi RichardDunneBSc,

    >>but I get an exception when reading ExpiryDate using SqlDataReader.  The exception message just says ExpiryDate.  

    Could you please share the screenshot or the error message to us for analysis? 

    Have you tried using this code in SQL Server Management Studio (SSMS)? Can you get the result from SSMS? If you can get true result with SSMS, this problem can be caused by your code, SqlDataReader is a class of .net framework, which language did you use? C++, C# or VB? I suggest you opening a case in the related forum.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, February 2, 2018 2:01 AM
    Moderator
  • Using C#.  I raised a bug in VS feedback concerning SqlDataReader last April (yes 2017).  Even though hasRows() property is true, View Results says Empty, Enumeration yielded no results.  Its still being triaged!  So if its causing the problem, no way of knowing until its resolved.  

    .

    Friday, February 2, 2018 3:57 AM
  • If I select Date from table, without convert it displays 02/02/2018 00:00:00.  If I use 

    Select convert(date,Date,103) from table

    is displays nothing.  I want to display the date without the time.  What am I doing wrong?

    Friday, February 2, 2018 9:41 AM
  • Got it working with ToShortDateString()
    Friday, February 2, 2018 12:07 PM
  • Hi RichardDunneBSc,

     

    >>If I select Date from table, without convert it displays 02/02/2018 00:00:00.  If I use Select convert(date,Date,103) from table is displays nothing.

     

    Do you mean that it displays nothing in SSMS? 

     

    Best Regards,

     

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 6, 2018 8:03 AM
    Moderator
  • I want to display a ExpiryDate 2018-02-02 as 02/02/2018 in visual studio.  I've tried convert 

    Select CONVERT(date,ExpiryDate,103) from table

    but I get an exception when reading ExpiryDate using SqlDataReader.  The exception message just says ExpiryDate.  

    Good day Richard,

    You confuse displayed format with input/out string format ;-)

    go over this short demo - Full explanation is in the code

    -- The style while using CONVERT from string to Date,
    -- does not give you the display format of the result,
    -- but the style format which SQL Server need to use for the converting
    -- In other words, this is the input style
    -- In bellow query the input is in style 120 and therefore convert using format 103 raise an error
    declare @ExpiryDate nvarchar(10) = '2018-02-02'
    Select CONVERT(date,@ExpiryDate,103)
    GO -- Conversion failed when converting date and/or time from character string.
    
    -- Convering the string using the right format of the input will work well
    declare @ExpiryDate nvarchar(10) = '2018-02-02'
    Select CONVERT(date,@ExpiryDate,120)
    GO-- OK
    -- Notice that the output is not necessarily in format 103 :-)
    -- The displayed format of the output for Date depend on the client side
    
    ---------------------------------------------------
    -- You confuse displayed format with input/output format
    ---------------------------------------------------
    
    -- Displayed format of date depend on your client app setting, if you sent to the client date type date
    -- 1. You can change the displayed format in the client side, for example in C# you can use the Property DataFormatString
    -- Once you change the format, the value is no longer a DATE but a String
    -- https://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.boundfield.dataformatstring.aspx
    -- 2. You can change the format in the query level
    -- (usually not a good idea since string will be larger then Date, which means that you will need to pass more data from the server to the client, assuming your provider pass it as Date and does not convert it to string) declare @ExpiryDate nvarchar(10) = '2018-02-01' -- input string declare @ExpiryDate2 Date -- First we convert to Date using the input format Select @ExpiryDate2 = CONVERT(date,@ExpiryDate,120) -- now we configure the desplayed format while converting the value to String -- We can use the FORMAT function here: Select FORMAT(@ExpiryDate2, 'dd/MM/yyyy', 'en-us') -- Or we can use CONVERT function again SELECT CONVERT(VARCHAR, @ExpiryDate2, 103) -- but remember that from this point you do not use Date but String GO

    I hope this explain the behavior ;-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    Tuesday, March 20, 2018 9:19 AM
    Moderator