Post reply

question about Datetime conversion

  • November 9, 2018 at 5:43 am

    #378592

    Hi 
    I have a C# application the is sending a stored procedure a date time in this format:  '01/29/2018 12:45:41 PM
    I need to convert this to this format 2018-01-25 09:01:00.000.
    I have not found something that does this.
     I found this for example CONVERT( VARCHAR(30), @Date ,105) but if I do the following for example:
    DECLARE @Date   varchar(50) ='01/29/2018 12:45:41 PM
      SELECT CONVERT( VARCHAR(30), @Date ,105)
    I get this 01/29/2018 12:45:41 PM
    I need this format
    2018-01-25 09:01:00.000.
    Any ideas I would appreciate.

    Thank you

  • November 9, 2018 at 5:52 am

    #2012945

    The problem is you're declaring your variable as the wrong data type. A datetime should be stored as a datetime(2), not a varchar. When using CONVERT and supplying varchar as the target datatype, and a style code, you're telling SQL Server that it will receive a non-(n)varchar datatype, and the style of the varchar, as you're datatype is already a varchar, the style code is completely ignored.

    Dates have no format in SQL Server, however, in SSMS the display format is chooses to use is normally yyyy-MM-dd hh:mm:ss.sss. What your application should be doing, however, is passing the value of @Date as a datetime datatype, not a varchar, and declare @Date as a datetime2(0) as well.

    If you have to convert that value, then you would do:
    DECLARE @Date datetime2(0) = CONVERT(date,'01/29/2018 12:45:41 AM',101);

    Edit: I note you say you want the time 09:01:00 of that day. If that is the case, then  you would then do:
    SET @Date = DATEADD(MINUTE, 541, DATEADD(DAY, DATEDIFF(DAY, 0, @Date),0));

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 9, 2018 at 6:15 am

    #2012946

    oh okay thanks
    I appreciate the help

  • November 9, 2018 at 6:43 am

    #2012951

    Thom A - Friday, November 9, 2018 5:52 AM

    The problem is you're declaring your variable as the wrong data type. A datetime should be stored as a datetime(2)....

    I suggest DATETIME rather than DATETIME2 unless absolutely needed.
    😎

    The main difference between the two data types is that the former is a normal Windows small-endian and the latter is big-endian format, slows down all operations on the latter whilst arithmetic operations work on the former.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply