locked
Datetime Column time having zeroes in timepart RRS feed

  • Question

  • User1203305613 posted

    There are some columns in our database which are DateTime but store zeroes in time.

    Please suggest a generic query to identify the table names and columns 

    ExamPle I want to Identify T.DOB 2000-08-01 00:00:00.000

    and NOT   T.Creatdon 2020-08-19 19:45:39.350

    Monday, October 5, 2020 8:54 AM

Answers

  • User475983607 posted

    We have a tool which creates  UI for us , for now in some cases when we select Date field , it creates columns as datetime in sql server , but inserts zeroes in time part.

    I wrote a script like the below one to identify such columns, yes we need to fix rootcause but in the interim we need to identify such columns and alter them to date.

    I think the problem is you do not understand the DateTime type which is causing you to make an assumption. 

    A DateTime type is a numeric value that combines a date with the time of day.  The zeros indicate midnight.  Midnight is certainly not a bug.  If you feel midnight is a bug then the problem is within the code that inserts the DateTime.   The DateTime column accepts whatever DateTime value the code provides including midnight.

    I recommend contacting the tool owners if you need assistance using the tool.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 5, 2020 12:19 PM

All replies

  • User475983607 posted

    2000-08-01 00:00:00.000 is midnight.  Can you explain the problem you are trying to solve?  Why are you trying to find all the DateTime values where the time portion of the date is midnight?

    Monday, October 5, 2020 11:46 AM
  • User1203305613 posted

    We have a tool which creates  UI for us , for now in some cases when we select Date field , it creates columns as datetime in sql server , but inserts zeroes in time part.

    I wrote a script like the below one to identify such columns, yes we need to fix rootcause but in the interim we need to identify such columns and alter them to date.

    The below script is too slow , want to know if there could be a better one.

    DECLARE @table_variable  TABLE (
         tblecol varchar(max)
    );
     DECLARE @tempcount int
    declare @tname varchar(100),@colname varchar(100)
    DECLARE @tCOLNAME VARCHAR(MAX)
    declare @sql NVARCHAR(MAX);
    DECLARE @RESULT INT
    DECLARE cursor_product CURSOR
    FOR 
          

    SELECT table_name,column_name
    FROM INFORMATION_SCHEMA.COLUMNS (noLOCK)
    where data_type ='datetime' 
    and column_name not in('CreatedOn', 'LastModifiedOn')
    OPEN cursor_product;
     
    FETCH NEXT FROM cursor_product INTO
        @tname,
         @colname;
         
    WHILE @@FETCH_STATUS = 0
        BEGIN

         
    set @tCOLNAME=    CONCAT(@tname,'.',@colname)
         SET @sql = N'  select  @ROWNUMBER=  coalesce(COUNT( distinct  cast(  ' + @tCOLNAME +' as time)),0) from '+ @tname; 
     
    --print @sql

     
     EXEC sp_executesql @sql,N'@RowNumber INT OUTPUT',@RowNumber=@Result OUTPUT;
         set @tempcount=0
         
       SELECT @tempcount=  @Result  
       if(@tempcount=1)
      begin
      INSERT INTO @table_variable values (@tCOLNAME)
     
      end
         
         


          FETCH NEXT FROM cursor_product INTO
                 @tname,
         @colname;
        END;

    CLOSE cursor_product;

    DEALLOCATE cursor_product;

      select * from  @table_variable 

     

    Monday, October 5, 2020 12:02 PM
  • User475983607 posted

    We have a tool which creates  UI for us , for now in some cases when we select Date field , it creates columns as datetime in sql server , but inserts zeroes in time part.

    I wrote a script like the below one to identify such columns, yes we need to fix rootcause but in the interim we need to identify such columns and alter them to date.

    I think the problem is you do not understand the DateTime type which is causing you to make an assumption. 

    A DateTime type is a numeric value that combines a date with the time of day.  The zeros indicate midnight.  Midnight is certainly not a bug.  If you feel midnight is a bug then the problem is within the code that inserts the DateTime.   The DateTime column accepts whatever DateTime value the code provides including midnight.

    I recommend contacting the tool owners if you need assistance using the tool.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, October 5, 2020 12:19 PM