Chủ Nhật, 24 tháng 12, 2023

[SQL Server] Find Currently Running Queries

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT
    DB_NAME(req.database_id) as db_name
    ,sqltext.text
    ,req.session_id
    ,req.status
    ,req.command
    ,req.start_time
    ,req.cpu_time
    ,req.total_elapsed_time
    ,queryplan.query_plan
    ,c.client_net_address
    ,s.login_name
    ,s.login_time
    ,s.program_name
    ,s.host_name
    ,s.host_process_id
    ,s.client_interface_name
    ,s.client_version
    --,req.*
    --,c.*
    --,s.*
FROM sys.dm_exec_requests req
JOIN sys.dm_exec_connections c on req.connection_id = c.connection_id
JOIN sys.dm_exec_sessions s on req.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
CROSS APPLY sys.dm_exec_query_plan(req.plan_handle) as queryplan

Thứ Tư, 19 tháng 4, 2023

[SQL Server] Convert DateTimeOffset to DateTime

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
declare @dateTime datetime2 = SYSDATETIME()
declare @dateTimeOffset datetimeoffset = SYSDATETIMEOFFSET()
  
select @dateTime as [CurrentDateTime],
@dateTimeOffset as [CurrentDateTimeOffset],
CAST(@dateTimeOffset as datetime2) as [DateTime1]
 
 
select CAST('2023-04-19 15:32:31.2721255 +07:00' as datetime2) -- Output: 2023-04-19 15:32:31.2721255
select CAST('2023-04-19 15:32:31.2721255 +08:00' as datetime2) -- Output: 2023-04-19 15:32:31.2721255
 
select SWITCHOFFSET('2023-04-19 15:32:31.2721255 +07:00', '+01:00') -- Output: 2023-04-19 09:32:31.2721255 +01:00
select SWITCHOFFSET('2023-04-19 15:32:31.2721255 +08:00', '+01:00') -- Output: 2023-04-19 08:32:31.2721255 +01:00
 
select CAST(SWITCHOFFSET('2023-04-19 15:32:31.2721255 +07:00', '+01:00') as datetime2) -- Output: 2023-04-19 09:32:31.2721255
select CAST(SWITCHOFFSET('2023-04-19 15:32:31.2721255 +08:00', '+01:00') as datetime2) -- Output: 2023-04-19 08:32:31.2721255