locked
Remove time portion from character colums RRS feed

  • Question

  • SQL Server 2005

    Hi Guys

    I have a column am_value5 (varchar(254))  and in that column the values are store like below:

    3/5/2018 2:00:00 AM

    3/5/2018

    now what I want is to remove time portion from my 1st row, so the result should be like below:

    3/5/2018

    3/5/2018

    In addition, I cannot its data type to date in database because I'm using Bi tool and its a default behavior for that tool.

    but if it is a  good idea to change this column value first to date and then remove time portion then please also suggest

    Appreciate your reply, Thanks


    • Edited by Muzokhh Friday, April 20, 2018 4:23 PM
    Friday, April 20, 2018 4:14 PM

All replies

  • create table #forum (dtt varchar(20))
    insert into #forum
    values ('3/5/2018 2:00:00 AM'),('3/5/2018')
    
    select IIf(patindex('% %',dtt)>1,left(dtt,len(dtt)-patindex('% %',dtt)-1),dtt)  from #forum

    Please mark as answer if this post helped you

    Friday, April 20, 2018 4:23 PM
  • Thanks for the reply but your provided solution is not working , see below for more details:

    Select IIf(patindex('% %',h.am_value21)>1,left(h.am_value21,len(h.am_value21)-patindex('% %',h.am_value21)-1),h.am_value21)

    from am_history h

    Error

    Msg 156, Level 15, State 1, Line 2

    Incorrect syntax near the keyword 'If'.

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near ','.

    Msg 156, Level 15, State 1, Line 25

    Incorrect syntax near the keyword 'AND'.

    Msg 156, Level 15, State 1, Line 26

    Incorrect syntax near the keyword 'AND'.

    Friday, April 20, 2018 4:32 PM
  • CONVERT(VARCHAR(20),CAST(dtt AS DATETIME), 101)

    • Proposed as answer by Daniel Janik Friday, April 20, 2018 8:27 PM
    Friday, April 20, 2018 4:33 PM
  • It is working fine when I put it in my select statement but when I try to use it in my where clause then I can see error, please see below:

    and CONVERT(VARCHAR(20),CAST(h.am_value2 AS DATETIME), 101)=rtrim(cast(month(getdate()-1) as char)) + '/' + rtrim(cast(day(getdate()-1) as char)) + '/' + rtrim(cast(year(getdate()) as char))

    Error

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting datetime from character string.


    I don't understand when I try to execute query without this clause then it works fine means there is no problem converting data but when I try to use it in where clause then it shows an error...strange
    • Edited by Muzokhh Friday, April 20, 2018 4:47 PM
    Friday, April 20, 2018 4:45 PM
  • What are trying to do with those three TRIM functions?

    You could extend Toms solution to format getdate() as 

    well

    select rtrim(cast(month(getdate()-1) as char)) + '/' + rtrim(cast(day(getdate()-1) as char)) + '/' + rtrim(cast(year(getdate()) as char))
    
    replace with 
    
    select CONVERT(VARCHAR(20),getdate(), 101)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 20, 2018 4:48 PM
  • Nice idea, now my clause is like below:

    and

    CONVERT(VARCHAR(20),CAST(h.am_value2 AS DATETIME), 101)=CONVERT(VARCHAR(20),getdate()-1, 101)

    but I'm still facing error, see below:

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting datetime from character string.

    Any idea how to deal with this error?

    Friday, April 20, 2018 4:57 PM
  • You have invalid values in h.am_value2.

    Try:

    CONVERT(VARCHAR(20),TRY_CAST(h.am_value2 AS DATETIME), 101)=CONVERT(VARCHAR(20),getdate()-1, 101)

    Friday, April 20, 2018 5:05 PM
  • Looks like I cannot use Try_cast function, see below:

    Msg 195, Level 15, State 10, Line 35

    'TRY_CAST' is not a recognized built-in function name.

    Friday, April 20, 2018 5:24 PM
  • What is your sql server version/

    https://docs.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-2017


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 20, 2018 6:24 PM
  • SQL Server 2005
    Friday, April 20, 2018 6:32 PM
  • I really don't understand one thing, when I try to use this clause in select then I cannot see any error but when I add it into where clause then I can see error, Any guidance will be highly appreciated. Thanks

    Friday, April 20, 2018 6:41 PM
  • Can you try filtering values which cannot be converted to date format using ISDATE function? 

    Per W3schools this should work in 2005 version.

    https://www.w3schools.com/sql/func_sqlserver_isdate.asp


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 20, 2018 6:54 PM
  • I really don't understand one thing, when I try to use this clause in select then I cannot see any error but when I add it into where clause then I can see error, Any guidance will be highly appreciated. Thanks

    You've got values in your date column that are not valid SQL Server dates, such as "2018-02-33" or "2018-02-01 25:00:00.000"

    As Sarat suggests, run the ISDATE() function on the data to see the values that are invalid.

    Try running the following against your table:

    SELECT am_value5
    FROM MyTable
    WHERE ISDATE(am_value5) = 0

    You'll want to change the table name to your table. Once that's done this query will show you all your bad date data.


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Friday, April 20, 2018 8:37 PM
  • It is working fine when I put it in my select statement but when I try to use it in my where clause then I can see error, please see below:

    and CONVERT(VARCHAR(20),CAST(h.am_value2 AS DATETIME), 101)=rtrim(cast(month(getdate()-1) as char)) + '/' + rtrim(cast(day(getdate()-1) as char)) + '/' + rtrim(cast(year(getdate()) as char))

    Error

    Msg 241, Level 16, State 1, Line 1

    Conversion failed when converting datetime from character string.


    I don't understand when I try to execute query without this clause then it works fine means there is no problem converting data but when I try to use it in where clause then it shows an error...strange

    It's really poor practice to use RTRIM and other non-deterministic functions on predicate columns. This makes the query non-SARGable and will greatly reduce performance.

    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)

    Friday, April 20, 2018 8:39 PM
  • I can see lots of value with 0 value when I try to filter it on whole table , is their any possibilities to discard those records that have isdate=0
    Friday, April 20, 2018 8:41 PM
  • I can see lots of value with 0 value when I try to filter it on whole table , is their any possibilities to discard those records that have isdate=0

    Good that means right now you were able to figure that there is some data which cannot be converted to date . 

    Next ,you could use CASE with ISDATE to filter out such rows,

    create table #forum (dtt varchar(20))
    insert into #forum
    values ('3/5/2018 sg 2:00:00'),('3/5/2018'),('4/19/2018')
    
     --- select throwing error during conversion
    select CONVERT(VARCHAR(20),CAST(dtt AS DATETIME), 101)
    from #forum
    
    --- select with ISDATE 
    select case when ISdate(dtt)= 1 then CONVERT(VARCHAR(20),CAST(dtt AS DATETIME), 101) else dtt end 
    from #forum
    
    -- Using ISDATE with CASE to filter non-date format rows
    select *   
    from #forum
    where case when ISdate(dtt)= 1 then CONVERT(VARCHAR(20),CAST(dtt AS DATETIME), 101)   end =CONVERT(VARCHAR(20),getdate()-1, 101)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, April 20, 2018 8:48 PM
  • but I don't want to include those records in my query

    • Edited by Muzokhh Friday, April 20, 2018 8:54 PM
    Friday, April 20, 2018 8:53 PM
  • SELECT * , SUBSTRING( am_Value5 , 1 , PATINDEX( '%[0-9][0-9][0-9][0-9]%', am_Value5 ) +4) FROM yourTBL

    where isDate(am_Value5) = 1



    • Edited by msbi_Dev Friday, April 20, 2018 9:02 PM
    Friday, April 20, 2018 9:01 PM
  • Awesome it is working now, I enterned the following

    and CONVERT(VARCHAR(20),CAST(SUBSTRING(  h.am_value2 , 1  ,    PATINDEX( '%[0-9][0-9][0-9][0-9]%', h.am_value2  ) +4) AS DATETIME), 101)=CONVERT(VARCHAR(20),getdate()-1, 101)


    actually it was not working
    • Edited by Muzokhh Friday, April 20, 2018 11:40 PM
    Friday, April 20, 2018 9:24 PM
  • create table #forum (dtt varchar(20))
    insert into #forum
    values ('3/5/2018 2:00:00 AM'),('3/5/2018')
    select IIf(patindex('% %',dtt)>1,left(dtt,len(dtt)-patindex('% %',dtt)-1),dtt) from #forum

    Make that

    create table #forum (dtt varchar(20))
    insert into #forum
    values ('3/5/2018 2:00:00 AM'),('3/5/2018')
    
    select CASE WHEN patindex('% %',dtt) > 1
                            THEN left(dtt,len(dtt)-patindex('% %',dtt)-1)
                            ELSE dtt
                 END
    from #forum

    Since Muzokhh is on SQL 2005 and IIF was added in SQL 2005. The advantage with method over those that convert to datetime is that invalid dates is not going to the stop the show. Well, at least as long no joker has introduced in the space. Which unfortunately is not entirely unlikely. Storing dates in string column will almost always result in bad data creeping in.

    Friday, April 20, 2018 9:34 PM
  • I just tried it and it is not working, see below for error

    Query

    SELECT* ,  CONVERT

    (VARCHAR(20),CAST(CASE WHEN patindex('% %',h.am_value2) > 1                      

    THEN left(h.am_value2,len(h.am_value2)-patindex('% %',h.am_value2)-1)

    ELSE h.am_value2

    END AS DATETIME), 101)

    FROM am_history h

    Error

    Msg 242, Level 16, State 3, Line 1

    The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

    Friday, April 20, 2018 11:06 PM
  • Awesome it is working now, I enterned the following

    and CONVERT(VARCHAR(20),CAST(SUBSTRING(  h.am_value2 , 1  ,    PATINDEX( '%[0-9][0-9][0-9][0-9]%', h.am_value2  ) +4) AS DATETIME), 101)=CONVERT(VARCHAR(20),getdate()-1, 101)


    actually it was not working

    can you try this ..

    AND CAST(SUBSTRING(  h.am_value2 , 1  ,    PATINDEX( '%[0-9][0-9][0-9][0-9]%', h.am_value2  ) +4) AS DATETIME)   = CAST(CONVERT(VARCHAR(20),getdate()-1, 101) AS DATETIME)


    • Edited by msbi_Dev Saturday, April 21, 2018 12:46 AM
    Saturday, April 21, 2018 12:46 AM
  • create table t1 (c1 varchar(254))
    insert t1 (c1) values ('3/5/2018 2:00:00 AM')

    select reverse(substring(reverse(c1),12,datalength(c1)))
    from t1

    Result:

    3/5/2018


    Saturday, April 21, 2018 3:38 AM
  • I just tried it and it is not working, see below for error

    No, you did not try what I and Diego suggested. All we told you was to strip everything after the first space. We did definitely not tell you do convert the data to any date or time data type.

    That is bound to fail, because you are attempting to store date values in a string, and you are more or less guaranteed to get invalid data.
    So don't mix two things. One is to strip the time portion. Another is to convert it to datetime.
    But for fun, you can do this:

    ; WITH CTE1 AS (
       SELECT am_value2,
              CASE WHEN patindex('% %', am_value2) > 1
                            THEN left(am_value2, len(am_value2) - patindex('% %',am_value) - 1)
                            ELSE am_value
                   END AS timeless
       FROM  am_history
    ), CTE2 AS (
       SELECT am_value2, timeless, isdate(am_value2) AS original_is_valid,
              isdate(timeless) AS timeless_is_valid
       FROM   CTE1
    )
    SELECT am_value2, timeless, original_is_valid, timeless_is_valid
    FROM   am_history2
    WHERE  original_is_valid = 0
        OR timeless_is_valid = 0

    This query will return all values that were invalid dates before the operation, or became invalid after stripping the time. You can vary the WHERE clauses to only see or the other.

    And you really need to make this sort of analysis. If you can find the pattern of the bad data, you may be able to resurrect it by applying further transformations.

    Saturday, April 21, 2018 11:58 AM
  • Thanks for you reply, actually my requirements are very simple and appreciate if you could guide me.

    --Want to exclude all invalid date in my where clause for am_value2 column

    --Want to remove time portion from am_value2 column

    --need to compare am_value values with todays date -1 but in order to compare both columns values  should have same format...a

    Thanks in advance

    Sunday, April 22, 2018 8:45 PM
  • To avoid that bad data causes your query to crash, you need to protect it with CASE.

    ; WITH CTE AS (
         SELECT CASE WHEN isdate(am_value2) = 1
                     THEN convert(char(8), convert(datetime, am_value2), 112)
                END am_value2_dateonly,             othercol1, othercol2, ...
         FROM   tbl
    )
    SELECT am_value2_dateonly, othercol, othercol2, ...
    FROM   CTE
    WHERE  am_value2_dateonly < convert(char(8), dateadd(DAY, -1, getdate()), 112)

    You may note that I get the dates here as char(8), but I use format 112 which is YYYYMMDD, so string comparison work. You can add an extra convert to datetime if you prefer.

    The reason you need the CASE is that you only have a WHERE on isdate, the optimizer may decide to evaluate values in the SELECT list before the filter is applied.
    This is a whole lot easier on modern versions of SQL Server!

    • Proposed as answer by Naomi N Sunday, April 22, 2018 10:41 PM
    Sunday, April 22, 2018 9:05 PM
  • Thanks I will try it tomorrow and let you know...
    Monday, April 23, 2018 1:59 AM