CODEWITHSUNDEEP
sqlsql-server
Bob
Bob

Reputation: 885

Convert Date & Time

I hv the following table :-

enter image description here

I need to convert it into:-

enter image description here

Do take note that the date of TIMEINA must follow exactly as per TIMEIN1. As for the time, it will be fixed ie 7.30AM, 8.30AM etc.

I tried the below SQL but it doesn’t work:-

enter image description here

Thanks

Upvotes: 1

Views: 141

Answers (3)

CRAFTY DBA
CRAFTY DBA

Reputation: 14915

The main question I have is how many queries are going against this table?

If you are doing this complex logic in one report, then by all means use a SELECT.

But it is crying out to me for a better solution.

Why not use computed column?

Since it is a date and non-deterministic, you can not use the persisted key word to physically store the calculated value.

However, you will only have this code in the table definition, not in every query.

I did the case for the first two ranges and two sample date items. The rest is up to you.!

-- Just play
use tempdb;
go

-- Drop table
if object_id('time_clock') > 0
drop table time_clock
go

-- Create table
create table time_clock
(
    tc_id int,
    tc_day char(3),
    tc_time_in datetime,
    tc_time_out datetime,
    tc_division char(3),
    tc_empid char(5),

    -- Use computed column
    tc_time_1 as
    (
    case 

      -- range 1
      when 
        tc_division = 'KEP' and 
        cast(tc_time_in as time) between '04:30:00' and '07:29:59'
      then 
        cast((convert(char(10), tc_time_in, 101) + ' 07:30:00') as datetime)

      -- range 2
      when 
        tc_division = 'KEP' and 
        cast(tc_time_in as time) between '17:30:00' and '19:29:59'
      then 
        cast((convert(char(10), tc_time_in, 101) + ' 19:30:00') as datetime)

      -- no match
      else NULL
    end
    ) 
);

-- Load store products
insert into time_clock values
(1,'SUN', '20131201 06:53:57', '20131201 16:23:54', 'KEP', 'A007'),
(2,'TUE', '20131201 18:32:42', '20131201 03:00:47', 'KEP', 'A007');

-- Show the data
select * from time_clock

Expected results.

enter image description here

Upvotes: 2

Jade
Jade

Reputation: 2992

Check this script might helps you.

print replace(convert(nvarchar(25) , getdate(), 120), '-', '') -- yyyyMMdd HH:mm:ss
print replace(convert(nvarchar(25) , getdate(), 102), '.', '') -- yyyyMMdd
print convert(nvarchar(25) , getdate(), 108) -- HH:mm:ss

HERE is the T-SQL

SELECT  [Day],
        TIMEIN1,
        TIMEOUT1,
        DIVISION,
        EMPLOYEE,

        --IF TIMEIN1 is varchar
        LEFT(TIMEIN1, 9) +
        CASE
            WHEN DIVISION = 'KEP' AND RIGHT(TIMEIN1, 8) BETWEEN '04:30:00' AND '07:29:59'
                THEN '07:30:00'
            WHEN DIVISION = 'KEP' AND RIGHT(TIMEIN1, 8) BETWEEN '17:30:00' AND '19:29:59'
                THEN '19:30:00'
            WHEN DIVISION = 'SER' AND RIGHT(TIMEIN1, 8) BETWEEN '04:30:00' AND '08:29:59'
                THEN '08:30:00'
            WHEN DIVISION = 'SER' AND RIGHT(TIMEIN1, 8) BETWEEN '17:30:00' AND '20:29:59'
                THEN '20:30:00'
            ELSE '00:00:00'
        END AS TIMEINA


        --IF TIMEIN1 is Date Time
        , replace(convert(nvarchar(25) , TIMEIN1, 102), '.', '') + ' ' +
        CASE
            WHEN DIVISION = 'KEP' AND convert(nvarchar(25) , TIMEIN1, 108) BETWEEN '04:30:00' AND '07:29:59'
                THEN '07:30:00'
            WHEN DIVISION = 'KEP' AND convert(nvarchar(25) , TIMEIN1, 108) BETWEEN '17:30:00' AND '19:29:59'
                THEN '19:30:00'
            WHEN DIVISION = 'SER' AND convert(nvarchar(25) , TIMEIN1, 108) BETWEEN '04:30:00' AND '08:29:59'
                THEN '08:30:00'
            WHEN DIVISION = 'SER' AND convert(nvarchar(25) , TIMEIN1, 108) BETWEEN '17:30:00' AND '20:29:59'
                THEN '20:30:00'
            ELSE '00:00:00'
        END AS TIMEINA

FROM    tempdb.dbo.TSA

check this for more info

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You should post your code as code and not as an image.

In any case, your code is comparing a datetime to a time value. Just do a conversion. Instead of timein1 between . . ., use:

cast(timein1 as time) between . . .

EDIT:

Oh, you also need to get the full date out. For that, use arithmetic on datetime:

cast('07:30:00' as datetime) + cast(cast(timein1 as date) as datetime)

The double cast on timein1 is just to remove the time component.

Upvotes: 1

Related Questions