locked
Convert NULL to datetime RRS feed

  • Question

  • Hi all,

    I need your help to solve this little problem I have:

    case
     when file like '%.xls' then 'NA'
     else NULL
    end as Time
    

    when filename has an xls extension field Time is 'NA' else I'd like to put a NULL value in field Time. The problem is that if any value is NULL, all results for that field become NULL. But the major problem is that in another code block I have to update values for this field with a datetime value and everything is ok, but the datetime format becomes like Jan 4 2010 09:55:12 and I don't want that format, so I try with a convert function (convert(varchar, Time, 101)) but it doesn't work, the datetime format stays like Jan 4 2010 ...

    Any idea and thanks in advance

    Tuesday, January 4, 2011 3:57 PM

Answers

  • > the format doesn't change although I'm doing an explicit conversion, the format stays like Jan 4 2010 10:15

    That is expected, since you can convert datetime to string using the style but not string to string. You will have to convert current value to datetime and then to string if you want to change the format.

    DECLARE @s varchar(35);
    
    SET @s = 'Jan 01 2011 09:45:00AM';
    
    SELECT
      @s AS c1,
      CONVERT(datetime, @s, 109) AS c2,
      CONVERT(varchar(35), CONVERT(datetime, @s, 109), 101) + ' ' + CONVERT(char(5), CONVERT(datetime, @s, 109), 108) AS c3;
    GO
    

    AMB

    Some guidelines for posting questions...

    • Marked as answer by skurava Tuesday, January 4, 2011 5:49 PM
    Tuesday, January 4, 2011 4:35 PM
  • declare @dt varchar(255)

    set @dt = 'Jan 4 2010 09:55:12'




    SELECT @dt = CONVERT(VARCHAR(10), CONVERT(DATETIME, @DT, 1), 101) + ' ' + CONVERT(varchar(10), convert(datetime, @dt, 1), 108)

    select @dt = SUBSTRING(@dt, 1, LEN(@DT) - 3)

    SELECT @dt
    • Marked as answer by skurava Tuesday, January 4, 2011 5:49 PM
    Tuesday, January 4, 2011 4:45 PM

All replies

  • You provided half information.

     

    How can you put NA as value in Time data field?

    convert function (convert(varchar(YOU MISS THIS PART), time, 101) .

    Time is field name? or data type in your case?

     

    101 does not have time in it.

    Please refer this http://msdn.microsoft.com/en-us/library/ms187928.aspx for all time formats.

     

    and please provide your problem in detail....

    Tuesday, January 4, 2011 4:02 PM
  • SQL Server uses 2 groups of four bytes to store a datetime value. The first 4 bytes is for the date, and it is the numbers of days before or after Jan 01, 1900. The second group is for the time, and it is number of 1/300-second units after midnight.

    Said that, you can not store different data types in a column that is not sql_variant. If the data type is datetime then you cannot have 'NA', NULL, or a datetime value in this column, since the value 'NA' cannot be converted to datetime.

    I would suggest to check this article to get a better understanding about datetime data type.

    The ultimate guide to the datetime datatypes

     


    AMB

    Some guidelines for posting questions...

    Tuesday, January 4, 2011 4:24 PM
  • Hi cool mind, in fact "Time" data field is a nvarchar(255) but I need to update this field with a datetime value in this format:

    01/04/2010 10:15

    Time is the field name not datatype.

    In order to avoid all the results become NULL I convert NULL value to varchar(255)

    case
     when file like '%.xls' then 'NA'
     else cast(NULL as varchar(255))
    end as Time
    
    

    Then when I update the NULL values specifying a datetime format

    convert(char(10), Time, 101) + ' ' + convert(char(5), Time, 108)
    

    the format doesn't change although I'm doing an explicit conversion, the format stays like Jan 4 2010 10:15

     

    Tuesday, January 4, 2011 4:25 PM
  • > the format doesn't change although I'm doing an explicit conversion, the format stays like Jan 4 2010 10:15

    That is expected, since you can convert datetime to string using the style but not string to string. You will have to convert current value to datetime and then to string if you want to change the format.

    DECLARE @s varchar(35);
    
    SET @s = 'Jan 01 2011 09:45:00AM';
    
    SELECT
      @s AS c1,
      CONVERT(datetime, @s, 109) AS c2,
      CONVERT(varchar(35), CONVERT(datetime, @s, 109), 101) + ' ' + CONVERT(char(5), CONVERT(datetime, @s, 109), 108) AS c3;
    GO
    

    AMB

    Some guidelines for posting questions...

    • Marked as answer by skurava Tuesday, January 4, 2011 5:49 PM
    Tuesday, January 4, 2011 4:35 PM
  • hi,

    By experience, converting types to varchar causes more issues than it solved problems. Why don't you keep types?

    Regards,

    Stef

    • Proposed as answer by Naomi N Tuesday, January 4, 2011 5:38 PM
    Tuesday, January 4, 2011 4:42 PM
  • declare @dt varchar(255)

    set @dt = 'Jan 4 2010 09:55:12'




    SELECT @dt = CONVERT(VARCHAR(10), CONVERT(DATETIME, @DT, 1), 101) + ' ' + CONVERT(varchar(10), convert(datetime, @dt, 1), 108)

    select @dt = SUBSTRING(@dt, 1, LEN(@DT) - 3)

    SELECT @dt
    • Marked as answer by skurava Tuesday, January 4, 2011 5:49 PM
    Tuesday, January 4, 2011 4:45 PM
  • Thanks CoolMind and HunchBack, this is the solution I was looking for. And thanks to all that took a look to this question.

     

    Tuesday, January 4, 2011 5:49 PM