Splitting Date into 2 Columns (Date + Time) in SQL
You may want to investigate the convert() function:
select convert(date, getdate()) as [Date], convert(varchar(8), convert(time, getdate())) as [Time]
gives
Date Time
---------- --------
2013-07-16 15:05:43
Wrapping these around your original SQL gives the admittedly very ugly:
SELECT convert(date,
DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
SUBSTRING([Date],10,2)+':'+
SUBSTRING([Date],12,2)+':'+
SUBSTRING([Date],14,2)+'.'+
SUBSTRING([Date],15,3)))) [Date],
convert(varchar(8), convert(time,
DATEADD(HOUR,-4,CONVERT(DATETIME,LEFT([Date],8)+' '+
SUBSTRING([Date],10,2)+':'+
SUBSTRING([Date],12,2)+':'+
SUBSTRING([Date],14,2)+'.'+
SUBSTRING([Date],15,3))))) [Time],
[Object] AS [Dataset],
SUBSTRING(Parms,1,6) AS [Media]
FROM (Select CONVERT(VARCHAR(18),[Date]) [Date],
[Object],
MsgId,
Parms
FROM JnlDataSection) A
Where MsgID = '325' AND
SUBSTRING(Parms,1,6) = 'V40449'
Order By Date DESC;
You may want to move part of this into a view, just to reduce complexity.
How to separate date and time and show in separate columns in SQL Server?
You can use CAST
and the appropriate datatype you want to cast to, DATE
and TIME
:
select TaskAutoId
, UserAutoId
, CONVERT(varchar, TaskAssignDate, 100) as TaskAssignDate
, TaskDescription
, TaskStartDate
, CONVERT(varchar, TaskEndDate, 100) as TaskEndDate
, IsCloseByUser
, CAST(TaskAssignDate AS DATE) TaskAssignDate -- the date part
, CAST(TaskAssignDate AS TIME) TaskAssignTime -- the time part
from tblTask
where IsSelfAssign = 1
How to split one date column into two columns and get data from the start date to last date
This is just Gordon's answer with a fix on column name. It should output the result you want.
WITH cte AS(
select *, (row_number() over (partition by ref order by [TransactionDate]) -
row_number() over (partition by ref, code order by [TransactionDate])
) as group_no
from LevyTransactions t
)
SELECT
Ref, MIN(Code) AS Code, MIN(TransactionDate) AS StartDate, MAX(TransactionDate) AS EndDate
FROM cte
GROUP BY Ref, group_no
ORDER BY 1,3
SQL Fiddle Demo
Edit: To get the display format as you mentioned, please use the following query
WITH cte AS(
select *, (row_number() over (partition by ref order by [TransactionDate]) -
row_number() over (partition by ref, code order by [TransactionDate])
) as group_no
from LevyTransactions t
)
SELECT
Ref
,MIN(Code) AS Code
,STUFF(CONVERT(VARCHAR,MIN(TransactionDate),107), 4,4, ' ') AS [From]
,STUFF(CONVERT(VARCHAR,MAX(TransactionDate),107), 4,4, ' ') AS [To]
FROM cte
GROUP BY Ref, group_no
ORDER BY Ref,MIN(TransactionDate)
Split date into multiple columns
- You can use various Date functions, like
YEAR()
,MONTH()
, andDAY()
, to get the year, month and day value from a MySQL date.
Try:
SELECT Id,
Email,
First_Name,
Last_Name,
DOB,
YEAR(DOB) AS DOB_Yr,
MONTH(DOB) AS DOB_Mo,
DAY(DOB) AS DOB_Day,
Gender
FROM your_table
Incase you want leading 0s in Month and Day (03
instead of 3
), you can use the Lpad()
function. Try the following instead:
SELECT Id,
Email,
First_Name,
Last_Name,
DOB,
YEAR(DOB) AS DOB_Yr,
LPAD(MONTH(DOB), 2, '0') AS DOB_Mo,
LPAD(DAY(DOB), 2, '0') AS DOB_Day,
Gender
FROM your_table
Splitting time and date in two separate date columns, in SQL server, is best practice?
In sql server 2008 you have date and time data types so this becomes a non issue. datetime always allowed for time even back in sql server 6 and 7
the reason people split it up is because with everything in 1 column a query that returns all orders placed between 3 and 4 PM for any day requires a scan, with a time column this can be accomplished with a seek (much, much faster)
How to split a datetime column into a date column and a concatenated time, for same date, column in SQL Server 2012
You just need to correlate your inner query with your outer query.
Note that format
is very expensive and should be avoided where possible - especially when using in the where
clause - its much better to compare in a native datatype rather than a string.
declare @Test table (UserId int, CheckTime datetime2(0))
insert into @Test (UserId, CheckTime)
values
(89, '02/02/2021 05:06:07'),
(89, '02/02/2021 09:07:08'),
(89, '02/02/2021 12:15:20'),
(89, '03/02/2021 10:11:12');
SELECT CAST(CHECKTIME AS date) AS [DATE]
, STUFF((
SELECT ', ' + FORMAT(T2.CHECKTIME, 'HH:mm:ss')
FROM @Test T2
WHERE CAST(T2.CHECKTIME AS date) = CAST(T1.CHECKTIME AS date)
AND T1.USERID = T2.USERID
FOR XML PATH ('')
), 1, 2, '') AS [HOURS]
FROM @Test T1
WHERE T1.USERID = 89
GROUP BY T1.USERID, CAST(T1.CHECKTIME AS date);
You can use string_agg (if using SQL Server 2017+)
SELECT CAST(CHECKTIME AS date) AS [DATE]
, STRING_AGG(FORMAT(T1.CHECKTIME, 'HH:mm:ss'),', ') WITHIN GROUP (order by T1.CHECKTIME asc) [HOURS]
FROM @Test T1
WHERE T1.USERID = 89
GROUP BY T1.USERID, CAST(T1.CHECKTIME AS date);
Note: If you add the DDL+DML for your sample data to future questions it makes it much easier to assist.
Related Topics
Sql Join: Selecting the Last Records in a One-To-Many Relationship
Make SQL Select Same Row Multiple Times
How to Return a Flag If Exist Id in Another Table MySQL
How to Select True/False Based on Column Value
Remove Multiple Keys from Jsonb Column in One Statement
How to Find the Row Count for All Your Tables in Postgres
What Is and How to Remove Tablespace Error from My Database
Mysql Left Join With Limit 1 Not Returning Desired Result
Count Table Difference Between Two Tables by a Complex Key in Hive
Select First Row in Each GROUP BY Group
Create a Query That Will Display Employee Name and Department Number
How to Subtract One Month from a Date Column
Add Default Value of Datetime Field in SQL Server to a Timestamp
T-Sql Query to Get the String Between Two Special Characters
Mysql - Move Rows from One Table to Another
How to Get Total Count Value Each Day Upto 5 Days
State Wise Data of Country and City
How to Strip All Non-Alphabetic Characters from String in SQL Server