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

DateTime conversion

Expand Messages
  • Noman Aftab
    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
    Message 1 of 5 , May 14 9:42 AM
    • 0 Attachment
      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]
    • 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 2 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 3 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 4 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 5 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.