Loading ...
Sorry, an error occurred while loading the content.

Re: [SQLQueriesNoCode] DateTime conversion

Expand Messages
  • Paul Livengood
    because you are not everything in the column is not a date and your date format is not a SQL Standard you will need to test for a DATE and convert using
    Message 1 of 5 , May 14 9:56 AM
    • 0 Attachment
      because you are not everything in the column is not a date and your date format is not a SQL Standard you will need to test for a DATE and convert using SUBSTRING.
       
      Example
       
       

      SELECT CONVERT(DATETIME,
        SUBSTRING(col1, 1, 4) + '-' +
        SUBSTRING(col1, 5, 2) + '-' +
        SUBSTRING(col1, 7, 2) + ' ' +
        SUBSTRING(col1, 9, 2) + ':' +
        SUBSTRING(col1, 11, 2) + ':00',
        120)
      from TABLENAME
      WHERE ISDATE(SUBSTRING(col1, 1, 4) + '-' +
        SUBSTRING(col1, 5, 2) + '-' +
        SUBSTRING(col1, 7, 2) + ' ' +
        SUBSTRING(col1, 9, 2) + ':' +
        SUBSTRING(col1, 11, 2) + ':00') = 1
       
      HTH
      Paul Livengood
       

      ________________________________
      From: Noman Aftab <noman17pk@...>
      To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
      Sent: Monday, May 14, 2012 10:42 AM
      Subject: [SQLQueriesNoCode] DateTime conversion



       

      Hi,
      I have a varchar column which contains a formatted datetime in 'yyyyMMddHHmm' format.
      For e.g. '201205142050' represents 14 May 2012 08:50 PM

      This column holds several other types of data, so converting this column into datetime is not possible.

      How can I efficiently convert this string into a datetime instance in TSQL for SQL2005 and above? Thanks.
       
      Best Wishes,
      Noman Aftab

       
      http://corpus.quran.com/wordbyword.jsp

      [Non-text portions of this message have been removed]




      [Non-text portions of this message have been removed]
    • Noman Aftab
      Thanks Paul, Since this query has to be run for just for value at a time..inside a udf, your solution is pretty much accepted.   Best Wishes, Noman Aftab  
      Message 2 of 5 , May 14 11:34 AM
      • 0 Attachment
        Thanks Paul,
        Since this query has to be run for just for value at a time..inside a udf, your solution is pretty much accepted.


         
        Best Wishes,
        Noman Aftab

         
        http://corpus.quran.com/wordbyword.jsp



        ________________________________
        From: Paul Livengood <p_livengood@...>
        To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
        Sent: Monday, 14 May 2012 8:56 PM
        Subject: Re: [SQLQueriesNoCode] DateTime conversion


         
        because you are not everything in the column is not a date and your date format is not a SQL Standard you will need to test for a DATE and convert using SUBSTRING.
         
        Example
         
         

        SELECT CONVERT(DATETIME,
          SUBSTRING(col1, 1, 4) + '-' +
          SUBSTRING(col1, 5, 2) + '-' +
          SUBSTRING(col1, 7, 2) + ' ' +
          SUBSTRING(col1, 9, 2) + ':' +
          SUBSTRING(col1, 11, 2) + ':00',
          120)
        from TABLENAME
        WHERE ISDATE(SUBSTRING(col1, 1, 4) + '-' +
          SUBSTRING(col1, 5, 2) + '-' +
          SUBSTRING(col1, 7, 2) + ' ' +
          SUBSTRING(col1, 9, 2) + ':' +
          SUBSTRING(col1, 11, 2) + ':00') = 1
         
        HTH
        Paul Livengood
         

        ________________________________
        From: Noman Aftab <noman17pk@...>
        To: "SQLQueriesNoCode@yahoogroups.com" <SQLQueriesNoCode@yahoogroups.com>
        Sent: Monday, May 14, 2012 10:42 AM
        Subject: [SQLQueriesNoCode] DateTime conversion


         

        Hi,
        I have a varchar column which contains a formatted datetime in 'yyyyMMddHHmm' format.
        For e.g. '201205142050' represents 14 May 2012 08:50 PM

        This column holds several other types of data, so converting this column into datetime is not possible.

        How can I efficiently convert this string into a datetime instance in TSQL for SQL2005 and above? Thanks.
         
        Best Wishes,
        Noman Aftab

         
        http://corpus.quran.com/wordbyword.jsp

        [Non-text portions of this message have been removed]

        [Non-text portions of this message have been removed]




        [Non-text portions of this message have been removed]
      • John Warner
        I have two tables TableA and TableB TableA TaskNbr varchar(25) , EmpID varchar(10) TableB TaskNbr varchar(25) , Respond varchar(10) Respond and EmpID are
        Message 3 of 5 , Jun 20, 2012
        • 0 Attachment
          I have two tables TableA and TableB

          TableA
          TaskNbr varchar(25)
          , EmpID varchar(10)

          TableB
          TaskNbr varchar(25)
          , Respond varchar(10)

          Respond and EmpID are actually the same thing an Identifier assigned to employees, they have different names because in the two tables they come from different sources and the name has meaning regarding the source.

          data:

          TableA
          A123, 23
          A234, 45 <--
          B567, 35
          C234, 40
          A567, null <--
          E234, 8

          TableB
          A123, 23
          A234, 29 <--
          B567, 35
          C234, 40
          A567, 10 <--
          B123, 9
          B789, null

          I need all distinct tasks returned, that is if it occurs in A I need it if it occurs in B I need it. Here is the trick that sort of makes a UNION a problem, if there is a NULL in either table for employee (EmpId, Respond) I want the record where the emp is not null if both tables are null then don't care. But if there is a conflict in Employess see row 2, I want the employee from TableA as its feed is more likely to be correct in this field.

          desired result:
          A123, 23
          A234, 45 <--
          B567, 35
          C234, 40
          A567, 10 <--
          E234, 8
          B123, 9
          B789, null

          The order here from the tables is not important the sort (ORDER BY) would be on Employee not the task. Notice table A wins when the Emps don't match, NULL loses to data in either table for Emp and all task numbers are returned regardless of table and if one of the tasks unique to a table had a NULL for Emp then that task still needs to return

          I've UNIONed the tables and then did a row_number partition by clause and that appears to work but I don't really trust the result in all cases. I'm even looking at a cursor worst case but I am sure there is a set based method to achieve this. The good news performance and scaling wise, neither table will ever have more than 100 to 200 qualifying rows (other fields not included here filter the larger list but provide no help to this problem)

          SQL Server 2008R2, Win 2003R2, both 32 bit version.

          Thanks for your suggestions.

          (Arnie, Paul, I hope life is treating you both well this summer!)

          John Warner
        • Farhan Ahmed
          Hi John, Try out this solution... I hope this will work for you.... Declare @T Table (TaskNum Varchar(10),EmpID varchar(10)) Declare @T2 Table (TaskNum
          Message 4 of 5 , Jun 20, 2012
          • 0 Attachment
            Hi John,

            Try out this solution... I hope this will work for you....


            Declare @T Table (TaskNum Varchar(10),EmpID varchar(10))
            Declare @T2 Table (TaskNum Varchar(10),Respond varchar(10))

            Insert into @T
            Select 'A123', '23'
            Union All
            Select 'A234', '45'
            Union All
            Select 'B567', '35'
            Union All
            Select 'C234', '40'
            Union All
            Select 'A567', null
            Union All
            Select 'E234', '8'

            Insert into @T2
            Select 'A123', '23'
            Union All
            Select 'A234', '29'
            Union All
            Select 'B567', '35'
            Union All
            Select 'C234', '40'
            Union All
            Select 'A567', '10'
            Union All
            Select 'B123', '9'
            Union All
            Select 'B789', NULl

            ;With Cte as (
            Select TaskNum,NULL EmpID from @T A
            Union
            Select TaskNum,Respond from @T2
            )
            Select DISTINCT C.TaskNum,ISNULL(T.EmpID,T2.Respond) EmpID from CTE C
            Left join @T T on C.TaskNum=T.TaskNum
            Left Join @T2 T2 on C.TaskNum=T2.TaskNum



            _________________________________________________________________________________________________________
            Regards,

            Farhan Ahmed
            Programmer Analyst
            The Shams Group
            Karachi, Pakistan
            (+92) 345 2523688
            View my Certifications




            To: SQLQueriesNoCode@yahoogroups.com
            From: john@...
            Date: Wed, 20 Jun 2012 04:53:33 -0400
            Subject: [SQLQueriesNoCode] SELECT query problem





            I have two tables TableA and TableB

            TableA
            TaskNbr varchar(25)
            , EmpID varchar(10)

            TableB
            TaskNbr varchar(25)
            , Respond varchar(10)

            Respond and EmpID are actually the same thing an Identifier assigned to employees, they have different names because in the two tables they come from different sources and the name has meaning regarding the source.

            data:

            TableA
            A123, 23
            A234, 45 <--
            B567, 35
            C234, 40
            A567, null <--
            E234, 8

            TableB
            A123, 23
            A234, 29 <--
            B567, 35
            C234, 40
            A567, 10 <--
            B123, 9
            B789, null

            I need all distinct tasks returned, that is if it occurs in A I need it if it occurs in B I need it. Here is the trick that sort of makes a UNION a problem, if there is a NULL in either table for employee (EmpId, Respond) I want the record where the emp is not null if both tables are null then don't care. But if there is a conflict in Employess see row 2, I want the employee from TableA as its feed is more likely to be correct in this field.

            desired result:
            A123, 23
            A234, 45 <--
            B567, 35
            C234, 40
            A567, 10 <--
            E234, 8
            B123, 9
            B789, null

            The order here from the tables is not important the sort (ORDER BY) would be on Employee not the task. Notice table A wins when the Emps don't match, NULL loses to data in either table for Emp and all task numbers are returned regardless of table and if one of the tasks unique to a table had a NULL for Emp then that task still needs to return

            I've UNIONed the tables and then did a row_number partition by clause and that appears to work but I don't really trust the result in all cases. I'm even looking at a cursor worst case but I am sure there is a set based method to achieve this. The good news performance and scaling wise, neither table will ever have more than 100 to 200 qualifying rows (other fields not included here filter the larger list but provide no help to this problem)

            SQL Server 2008R2, Win 2003R2, both 32 bit version.

            Thanks for your suggestions.

            (Arnie, Paul, I hope life is treating you both well this summer!)

            John Warner






            [Non-text portions of this message have been removed]
          Your message has been successfully submitted and would be delivered to recipients shortly.