none
Parse string to Format: YYYYMMDDHHMMSS. and then convert to date time

    Question

  • Hi all,Please help me.  

    From below StringColumn I need to Get Only Date Part and convert it into DateTime and place in new column FileDate

    String is not able to Convert to DateTime because its not in the standard Format: YYYYMMDDHHMMSS.

    Sometimes its like  YYYYMMDDHMMSS. YYYYMMDDHHMSS,etc . 

    I have to append 0 to make it standard format.

       

    String Column

    Account_Territory_Loader_Veeva-O26_201304242821.txt
    Account_Territory_Loader_Veeva-O26_2013042422193.txt
    Account_Territory_Loader_Veeva-O26_20130422235215.txt
    Merge_Outbound_Veeva-O32_20130422234454.txt
    Merge_Outbound_Veeva-O32_2013042414054.txt
    Merge_Outbound_Veeva-O32_2013042422945.txt 


    Please help me to parse or append 0 to make it to standard.

    Wednesday, May 15, 2013 1:13 AM

Answers

All replies

  • The missing position of Minutes/secs/hours can't be identified by an external logic, during the file generation itself it should be appended.

    If the missing values are properly replaced , you can try the below one to get it in date format

    declare @test table (filename varchar(1000))
    insert into @test values ('Merge_Outbound_Veeva-O32_20130422234454.txt'),('Merge_Outbound_Veeva-O32_2013042414054.txt'),('Merge_Outbound_Veeva-O32_2013042422945.txt')
    ,('Call_Detail_Outbound_Veeva-O6_20130424020101.txt ')
    
    
    select T.RAWDATE,cast (SUBSTRING(T.RAWDATE,0,9) as date),cast (STUFF(T.RAWDATE,1,8,'') as time) from (
    
    select  reverse(SUBSTRING( PARSENAME(REVERSE(filename),1),0,CHARINDEX('_',PARSENAME(REVERSE(filename),1)))) as RAWDATE
    
    from @test) T


    Thanks
    Sarat

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

    Wednesday, May 15, 2013 1:24 AM
  • the same question was posted in another thread, may be a single thread would be good for tracking.

    For ex: consider 2013042414054.

    the time part is 22945, how do we interpret it ?

    22:09:45 or 02:29:45?

    we may not be able add logic to find this, better to be handled from application.


    Thanks
    Sarat

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

    Wednesday, May 15, 2013 1:30 AM
  • Hi Sarat

    YYYY MM DD HH MM

    9999 12 31 24 60

    • Marked as answer by KODI_KODI Friday, September 13, 2013 10:50 AM
    Wednesday, May 15, 2013 1:40 AM
  • Hi Sarat

    YYYY MM DD HH MM

    9999 12 31 24 60

    Did u check the example give by me in last reply, which of the two possibilities is correct and how?

    Thanks
    Sarat

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

    Wednesday, May 15, 2013 2:15 AM
  • Try this alternative,

    declare

    @table table ([stringcolumn] nvarchar(300))

    insert

    into @table values ('Account_Territory_Loader_Veeva-O26_201304242821.txt')

    insert

    into @table values ('Account_Territory_Loader_Veeva-O26_2013042422193.txt')

    insert

    into @table values ('Account_Territory_Loader_Veeva-O26_20130422235215.txt')

    insert

    into @table values ('Merge_Outbound_Veeva-O32_20130422234454.txt')

    insert

    into @table values ('Merge_Outbound_Veeva-O32_2013042414054.txt')

    insert

    into @table values ('Merge_Outbound_Veeva-O32_2013042422945.txt')

    ------------------ final query

    select

    PARSENAME(REPLACE(PARSENAME(REPLACE(RTRIM(stringcolumn),'-','.'), 2),'_','.'),1) as X from @table


    Regards, RSingh

    Wednesday, May 15, 2013 3:14 AM
  • Rsingh,

    Thanks for your time.

    Result Set

    X

    201304242821
    2013042422193
    20130422235215
    20130422234454
    2013042414054
    2013042422945


    I have to Parse the result set of your query to DATETIME. ( YYYYMMDDHHMMSSMMM). this is the issue.

    Wednesday, May 15, 2013 9:58 AM
  • Hi Sarat

    YYYY    MM DD HH MM SS

    9999   12 31   24 60   60          ( STANDARD for every One)

    0000  12  30   22  94 5

    FROM ABOVE 

    its 22:09:45

    Wednesday, May 15, 2013 10:09 AM