locked
Convert date to time RRS feed

  • Question

  • Dear All, May i know how to convert date to time ?

    as below show the example date & time, how to convert to time ? 

    20120131  200022

    thank you. 

    Tuesday, May 7, 2013 2:48 AM

Answers

  • Hi,

    Try like this

    SELECT REPLACE(CONVERT(CHAR(8),GETDATE(),108),':','') Onlytimepart
    SELECT REPLACE(CONVERT(CHAR(8),GETDATE(),114),':','') Onlytimepart
    SELECT CONVERT(CHAR(9),GETDATE(),112)+REPLACE(CONVERT(CHAR(8),GETDATE(),108),':','') Dateformats
    SELECT CONVERT(CHAR(9),GETDATE(),112)+REPLACE(CONVERT(CHAR(8),GETDATE(),114),':','') Dateformats

    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.



    Tuesday, May 7, 2013 3:04 AM

All replies

  • What is your SQL server version? In SQL 2008 and up:

    select dt, CAST(dt as time) as [Time] from myTable


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, May 7, 2013 2:50 AM
  • Refer this,

    select getdate() as CurrentDateTime,replace(convert(nvarchar,getdate(),101),'/','') as IntDate, convert(nvarchar, getdate(),114) as CurrentTime


    Regards, RSingh

    Tuesday, May 7, 2013 2:58 AM
  • Hi,

    Try like this

    SELECT REPLACE(CONVERT(CHAR(8),GETDATE(),108),':','') Onlytimepart
    SELECT REPLACE(CONVERT(CHAR(8),GETDATE(),114),':','') Onlytimepart
    SELECT CONVERT(CHAR(9),GETDATE(),112)+REPLACE(CONVERT(CHAR(8),GETDATE(),108),':','') Dateformats
    SELECT CONVERT(CHAR(9),GETDATE(),112)+REPLACE(CONVERT(CHAR(8),GETDATE(),114),':','') Dateformats

    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.



    Tuesday, May 7, 2013 3:04 AM
  • You can use cast or convert with little formatting,

    select cast ('20120131' as date), cast (stuff(stuff('200022',3,0,':'),6,0,':') as time)
    select convert(date,'20120131' ),convert(time ,stuff(stuff('200022',3,0,':'),6,0,':'))



    Thanks
    Sarat

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


    Tuesday, May 7, 2013 3:38 AM
  • Please read any book on SQL or the ISO-8601 Standards. You cannot convert a DATE to a TIME by definition. You can extract a time from a date. Guessing at what you were trying to say with that unquote string of digits: 

    CAST ('2012-01-31  20:00:22' AS TIME)


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    • Proposed as answer by Junaid_Hassan Tuesday, May 7, 2013 4:48 AM
    Tuesday, May 7, 2013 4:43 AM
  • Dear All, 

      Need help for convert as below SQL script date & time to time only, 


    as below image show the  first scan and last scan date and time, but i want convert it to time only,

    kindly advise. thank you

    SELECT CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) AS SCANDATE
                   ,FIRSTSCAN.EP_EMP_COMPANY
                   ,FIRSTSCAN.EP_EMP_DEPT
                    ,FIRSTSCAN.EP_EMP_ID
                   ,FIRSTSCAN.EP_EMP_NAME
                    ,FIRSTSCAN.EP_EMP_SECTION
                    ,FIRSTSCAN.EP_EMP_SHIFT
                    ,FIRSTSCAN.EP_SHIFT
                    ,FIRSTSCAN.EP_SCAN_DATE AS FIRSTSCAN
                   ,LASTSCAN.EP_SCAN_DATE AS LASTSCAN
                    ,SCANTIMECAL.INFAB AS INFAB_MIN
                    ,SCANTIMECAL.OUTFAB AS OUTFAB_MIN
                   ,DATEDIFF(MI,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
                   ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS WORK_MIN
                   ,DATEDIFF(HOUR,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
                   ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS WORK_HOUR
                    ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' 
                      THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' 
                      THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                       ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + 
                       REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
                        ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) ELSE ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
                        ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) END AS OTWORK_HOUR
                    ,CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1' OR FIRSTSCAN.EP_SHIFT = 'D1') 
                    AND ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' 
                     THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                       ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
                        ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) >= 3 THEN 3 - 0.25 END OTHOUR_FIX
                    ,CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1' OR FIRSTSCAN.EP_SHIFT = 'D1') 
                    AND ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' 
                    THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + 
                    REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                       ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + 
                       REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME)
                       ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) >= 3 THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF((CASE WHEN SHIFTCAL.EP_SHIFT = 'N1' 
                       THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE ,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                        ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE ,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END),12,0,':'),15,0,':') AS DATETIME),CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT) - 3,2) ELSE 
                         CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN ROUND(CAST((DATEDIFF(MINUTE,CAST(STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)
                         ,CAST(STUFF(STUFF(LASTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) / 60.0) AS FLOAT),2) END END OTHOUR_EXTRA
                    ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN FIRSTSCAN.LATEIN END LATEIN
                    ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN CASE WHEN SCANTIMECAL.OUTFAB >= SHIFTDESC.EP_SHIFT_OGRACE THEN 1 END END AS BREAK_ABNORMAL
                   ,CASE WHEN FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' THEN CASE WHEN RIGHT(LASTSCAN.EP_SCAN_DATE,6) < REPLACE(CONVERT(VARCHAR(8),CONVERT(VARCHAR(8),SHIFTDESC.EP_SHIFT_TIMETO,108),108),':','') THEN 1 END END AS EARLYOUT_NORMAL
                   ,(CASE WHEN FIRSTSCAN.EP_SHIFT<> 'NS' AND FIRSTSCAN.EP_SHIFT <> 'R1' AND FIRSTSCAN.EP_SHIFT <> 'R2' 
                   THEN (CASE WHEN RIGHT(LASTSCAN.EP_SCAN_DATE,6) < REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+3,CONVERT(VARCHAR(8),SHIFTDESC.EP_SHIFT_TIMETO,108)),108),':','') THEN 1 END) END)AS EARLYOUT_SHIFT
    
                    FROM
                    (
                     SELECT 
                    ROW_NUMBER() OVER(PARTITION  BY SCANHIST.EP_EMP_ID,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) < '130000' AND  SHIFTCAL.EP_SHIFT = 'N1' 
                    THEN CONVERT(VARCHAR(8),DATEADD(DAY,-1,CONVERT(VARCHAR(8),LEFT(SCANHIST.EP_SCAN_DATE,8),112)),112) 
                    ELSE LEFT(SCANHIST.EP_SCAN_DATE,8) END ORDER BY SCANHIST.EP_EMP_ID,SCANHIST.EP_SCAN_DATE) AS RowNum
                    ,SCANHIST.EP_SCAN_DATE
                    ,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) < '130000' AND  SHIFTCAL.EP_SHIFT = 'N1' 
                    THEN CONVERT(VARCHAR(8),DATEADD(DAY,-1,CONVERT(VARCHAR(8),LEFT(SCANHIST.EP_SCAN_DATE,8),112)),112) ELSE LEFT(SCANHIST.EP_SCAN_DATE,8) END AS EMP_WORKDATE
                    ,EMPINFO.EP_EMP_COMPANY
                    ,SCANHIST.EP_EMP_ID
                    ,SCANHIST.EP_EMP_NAME
                    ,SCANHIST.EP_EMP_DEPT
                    ,SCANHIST.EP_EMP_SECTION
                    ,SCANHIST.EP_EMP_SHIFT
                    ,SHIFTCAL.EP_SHIFT
                    ,SCANHIST.EP_SCAN_ID
                    ,SCANHIST.EP_TRANS_LOC
                    ,CASE WHEN RIGHT(SCANHIST.EP_SCAN_DATE,6) > REPLACE(SHIFTDESC.EP_SHIFT_TIMEFR,':','') 
                    THEN 1 END AS LATEIN
                    FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST
    
                    JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
                    ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID
    
    
                    JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
                    ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT
                    AND SHIFTCAL.EP_SHIFT_DATE = LEFT(SCANHIST.EP_SCAN_DATE,8)
    
                    JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
                    ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
    
                    WHERE 1=1
                    AND SCANHIST.EP_SCAN_DATE >= '20130428' + ' ' + 
                    CASE WHEN (SHIFTCAL.EP_SHIFT <> 'R1' AND SHIFTCAL.EP_SHIFT <> 'R2') 
                    THEN REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,-4,SHIFTDESC.EP_SHIFT_TIMEFR + ':00'),108),':','') 
                    ELSE REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,-0,SHIFTDESC.EP_SHIFT_TIMEFR + ':00'),108),':','') END
                    AND SCANHIST.EP_SCAN_DATE < CASE WHEN (SHIFTCAL.EP_SHIFT = 'N1')  
                    THEN 
                    CONVERT(VARCHAR(8),DateAdd(DAY,+1,'20130428'),112) + ' ' + 
                    REPLACE(CONVERT(VARCHAR(8),DateADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                    ELSE CASE WHEN (SHIFTCAL.EP_SHIFT = 'R1' OR SHIFTCAL.EP_SHIFT = 'R2') 
                    THEN '20130428' + ' ' + 
                    REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                    ELSE '20130429' + ' ' + 
                    REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
                     AND SCANHIST.EP_TRANS_LOC = 'IN'
                     AND EMPINFO.EP_EMP_LEVEL > '10'
                    --AND EMPINFO.EP_EMP_LEVEL <> ''
                   --AND SCANHIST.EP_EMP_DEPT = ''
                   --AND SCANHIST.EP_EMP_SECTION = ''
                   --AND SCANHIST.EP_EMP_SHIFT = ''
                   --AND SCANHIST.EP_EMP_ID = ''
                   AND SCANHIST.EP_EMP_SHIFT ='PM'
                    )FIRSTSCAN
    
                    OUTER APPLY
                    (
                    SELECT TOP 1
                    SCANHIST.EP_SCAN_DATE
                    ,EMPINFO.EP_EMP_COMPANY
                    ,SCANHIST.EP_EMP_ID
                    ,SCANHIST.EP_EMP_NAME
                    ,SCANHIST.EP_EMP_DEPT
                    ,SCANHIST.EP_EMP_SECTION
                    ,SCANHIST.EP_EMP_SHIFT
                    ,SHIFTCAL.EP_SHIFT
                    ,SCANHIST.EP_SCAN_ID
                    ,SCANHIST.EP_TRANS_LOC
                    FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST
    
                     JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
                    ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID
    
    
             JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
                 ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT
                    AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)
    
                   JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
                    ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
    
                WHERE 1=1
                
                   AND SCANHIST.EP_SCAN_DATE > FIRSTSCAN.EP_SCAN_DATE
                   AND SCANHIST.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') 
                   THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE,8)),112) + ' ' + 
                   REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                   ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') 
                   THEN LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + 
                   REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                   ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + 
                   REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
                   AND SCANHIST.EP_TRANS_LOC = 'OUT'
                   AND EMPINFO.EP_EMP_LEVEL > '10'
                   AND EMPINFO.EP_EMP_LEVEL <> ''
                   AND SCANHIST.EP_EMP_ID = FIRSTSCAN.EP_EMP_ID
    
                 ORDER BY SCANHIST.EP_SCAN_DATE DESC )LASTSCAN
    
                 OUTER APPLY
                  (
                    SELECT 
                     GROUP_SCANTIMECAL.EP_EMP_ID
                ,SUM(CAST(GROUP_SCANTIMECAL.INFAB_MIN AS FLOAT)) AS INFAB
                    ,SUM(CAST(GROUP_SCANTIMECAL.OUTFAB_MIN AS FLOAT)) AS OUTFAB
                    FROM
                    (
                  SELECT SCANHIST.EP_SCAN_DATE
                    ,SCANHIST.EP_EMP_ID
                    ,SCANHIST.EP_EMP_NAME
                    ,SCANHIST.EP_EMP_DEPT
                    ,SCANHIST.EP_EMP_SECTION
                    ,SCANHIST.EP_EMP_SHIFT
                   ,SCANHIST.EP_TRANS_LOC
                    ,DATEDIFF(MI,CAST(STUFF(STUFF(SCANHIST.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME),CAST(STUFF(STUFF(NEXTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS INFAB_MIN
                    ,DATEDIFF(MI,CAST(STUFF(STUFF(NEXTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME),CAST(STUFF(STUFF(PREVSCAN.EP_SCAN_DATE,12,0,':'),15,0,':') AS DATETIME)) AS OUTFAB_MIN
                    FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] SCANHIST
      
                    OUTER APPLY
                    (
                    SELECT TOP 1
                     NEXTSCAN.EP_SCAN_DATE
                    ,NEXTSCAN.EP_EMP_ID
                    ,NEXTSCAN.EP_EMP_NAME
                    ,NEXTSCAN.EP_EMP_DEPT
                    ,NEXTSCAN.EP_EMP_SECTION
                    ,NEXTSCAN.EP_EMP_SHIFT
                    ,NEXTSCAN.EP_SCAN_ID
                    ,NEXTSCAN.EP_TRANS_DESC
                    ,NEXTSCAN.EP_TRANS_LOC
                    FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] NEXTSCAN
    
                    JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
                    ON EMPINFO.EP_EMP_ID = NEXTSCAN.EP_EMP_ID
    
    
                    JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
                    ON SHIFTCAL.EP_SHIFT_NAME = NEXTSCAN.EP_EMP_SHIFT
                    AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)
    
                   JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
                   ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
    
                   WHERE 1=1
                  AND SCANHIST.EP_SCAN_ID = NEXTSCAN.EP_SCAN_ID
                   AND NEXTSCAN.EP_SCAN_DATE > SCANHIST.EP_SCAN_DATE
                  AND NEXTSCAN.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1')
                   THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(NEXTSCAN.EP_SCAN_DATE,8)),112) + ' ' + 
                   REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                   ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') 
                   THEN LEFT(NEXTSCAN.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') 
                   ELSE LEFT(NEXTSCAN.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
                  AND NEXTSCAN.EP_TRANS_LOC = 'OUT'
                  ORDER BY NEXTSCAN.EP_SCAN_DATE
                    )NEXTSCAN
    
                    OUTER APPLY
                  (
                    SELECT TOP 1
                   PREVSCAN.EP_SCAN_DATE
                    ,PREVSCAN.EP_EMP_ID
                    ,PREVSCAN.EP_EMP_NAME
                    ,PREVSCAN.EP_EMP_DEPT
                    ,PREVSCAN.EP_EMP_SECTION
                    ,PREVSCAN.EP_EMP_SHIFT
                    ,PREVSCAN.EP_SCAN_ID
                    ,PREVSCAN.EP_TRANS_DESC
                    ,PREVSCAN.EP_TRANS_LOC
                    FROM [AcmkIMS].[dbo].[EP_SCAN_HIST] PREVSCAN
    
                    JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
                     ON EMPINFO.EP_EMP_ID = PREVSCAN.EP_EMP_ID
    
                    JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
                    ON SHIFTCAL.EP_SHIFT_NAME = PREVSCAN.EP_EMP_SHIFT
                    AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)
    
                    JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
                    ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
    
                    WHERE 1=1
                   AND SCANHIST.EP_SCAN_ID = PREVSCAN.EP_SCAN_ID
                    AND PREVSCAN.EP_SCAN_DATE > SCANHIST.EP_SCAN_DATE
                   AND PREVSCAN.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(SCANHIST.EP_SCAN_DATE,8)),112) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') THEN LEFT(SCANHIST.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(SCANHIST.EP_SCAN_DATE,8) + ' ' + REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
                   AND PREVSCAN.EP_TRANS_LOC = 'IN'
                    ORDER BY PREVSCAN.EP_SCAN_DATE
                    )PREVSCAN
    
                     JOIN [AcmkIMS].[dbo].[EP_EMP_INFO] EMPINFO
                     ON EMPINFO.EP_EMP_ID = SCANHIST.EP_EMP_ID
    
    
                    JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
                    ON SHIFTCAL.EP_SHIFT_NAME = SCANHIST.EP_EMP_SHIFT
                   AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)
    
                    JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
                   ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
    
                 WHERE 1=1
                    AND SCANHIST.EP_SCAN_DATE >= FIRSTSCAN.EP_SCAN_DATE
                   AND SCANHIST.EP_SCAN_DATE < CASE WHEN (FIRSTSCAN.EP_SHIFT = 'N1') 
                     THEN CONVERT(VARCHAR(8),DATEADD(DAY,+1,LEFT(FIRSTSCAN.EP_SCAN_DATE,8)),112) + ' ' +
                       REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE
                     CASE WHEN (FIRSTSCAN.EP_SHIFT = 'R1' OR FIRSTSCAN.EP_SHIFT = 'R2') 
                     THEN LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' + 
                     REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+0,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') ELSE LEFT(FIRSTSCAN.EP_SCAN_DATE,8) + ' ' +
                        REPLACE(CONVERT(VARCHAR(8),DATEADD(HOUR,+6,SHIFTDESC.EP_SHIFT_TIMETO + ':00'),108),':','') END END
                    AND SCANHIST.EP_EMP_ID = FIRSTSCAN.EP_EMP_ID
                    AND SCANHIST.EP_TRANS_LOC = 'IN'
                    )GROUP_SCANTIMECAL
    
                   GROUP BY GROUP_SCANTIMECAL.EP_EMP_ID
    
                      )SCANTIMECAL
    
                 JOIN [AcmkIMS].[dbo].[EP_SHIFT_CALENDAR] SHIFTCAL
                   ON SHIFTCAL.EP_SHIFT_NAME = FIRSTSCAN.EP_EMP_SHIFT
                    AND SHIFTCAL.EP_SHIFT_DATE = LEFT(FIRSTSCAN.EP_SCAN_DATE,8)
    
              JOIN [AcmkIMS].[dbo].[EP_SHIFT_DESC] SHIFTDESC
                    ON SHIFTDESC.EP_SHIFT_NAME = SHIFTCAL.EP_SHIFT
    
                     WHERE 1=1
                 AND FIRSTSCAN.RowNum = 1
                   AND CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) BETWEEN '20130428' AND '20130429'
                   
                   ORDER BY CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112),FIRSTSCAN.EP_EMP_DEPT,FIRSTSCAN.EP_EMP_SECTION,EP_EMP_ID
      
      
    

    • Merged by Kalman Toth Tuesday, May 7, 2013 8:58 AM duplicate
    Tuesday, May 7, 2013 5:54 AM
  • Starting with SQL Server 2008 you can do:

    SELECT CONVERT(TIME, STUFF(STUFF(RIGHT('20120306102345',6),3,0,':'),6,0,':'));
    -- 10:23:45.0000000

    Datetime conversions:

    http://www.sqlusa.com/bestpractices/datetimeconversion/


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Tuesday, May 7, 2013 6:05 AM
  • For this how to convert to time between date 

     AND CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) BETWEEN '20130428' AND '20130429'

    Tuesday, May 7, 2013 6:20 AM
  • Try something like this,

    Declare @Table  Table (EP_SCAN_DATE varchar(50))
    insert into @Table
    select 20130428213555 union
    select 20130428213556 union
    select 20130428213557 union
    select 20130428073558 
    
    select 
    Right(EP_SCAN_DATE,6)/10000 hours,
    Right(EP_SCAN_DATE,4)/100 min,
    Right(EP_SCAN_DATE,2) sec,
    Convert(time,Dateadd(SECOND,
    		Right(EP_SCAN_DATE,2)/1,
    		Dateadd(MINUTE,
    				Right(EP_SCAN_DATE,4)/100,
    				Dateadd(hour,
    						Right(EP_SCAN_DATE,6)/10000,
    						'1900-01-01')))) ConvertedTime
    from @Table


    Regards, Dineshkumar,
    Please 'Mark as Answer' if my post answers your question and 'Vote as Helpful' if it helps you

    Dineshkumar's BI Blog

    Tuesday, May 7, 2013 6:31 AM
  • For this how to convert to time between date 

     AND CONVERT(VARCHAR(8),STUFF(STUFF(FIRSTSCAN.EP_SCAN_DATE,12,0,':'),15,0,':'),112) BETWEEN '20130428' AND '20130429'

    A similar thread was started earlier today , http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/09b91bc9-1964-42fd-bf0a-4fdd6b6702d4

    The values in Between are dates, if you are converting to time and the comparison can be done?

    Give sample value for EP_SCAN_DATE, that gives some clue. 


    Thanks
    Sarat

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

    Tuesday, May 7, 2013 6:40 AM