May 17, 2011 at 2:52 pm
Hello,
I have two columns 'date' varchar (8) and 'time' varchar (4)
I want to club 2 columns in to one one column as smalldatetime and order by date and time.
and the time should be in 12 hr format meaning i want the time to be displayed in AM and PM in desc order
how to script for the above requirement?
Thanks.
May 17, 2011 at 3:23 pm
ranuganti (5/17/2011)
Hello,I have two columns 'date' varchar (8) and 'time' varchar (4)
I want to club 2 columns in to one one column as smalldatetime and order by date and time.
and the time should be in 12 hr format meaning i want the time to be displayed in AM and PM in desc order
how to script for the above requirement?
Thanks.
This code will put the data into a new column of smalldatetime and order by that column:
DECLARE @test-2 TABLE (MyDate VARCHAR(8), MyTime VARCHAR(4));
INSERT INTO @test-2
(MyDate,
MyTime)
VALUES ('20110517','1717'),
('20110517','1718'),
('20110517','1312');
SELECT *,
MyNewDate = CONVERT(SMALLDATETIME, MyDate + ' ' + STUFF(MyTime,3,0,':') + ':00')
FROM @test-2
ORDER BY MyNewDate DESC;
If you want to control the way that the data looks, investigate the style option of the CONVERT function.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 17, 2011 at 3:24 pm
Something like this?
DECLARE @TableOfDatesAndTimes Table
(
Dates VARCHAR(8) ,
Times VARCHAR(4)
)
--== Populate with test data
--== this is what u missed in the post ; Please provide sample data
INSERT INTO @TableOfDatesAndTimes
SELECT '20110517' , CAST(N AS VARCHAR(4)) + '00'
FROM TALLY
WHERE N <= 23
ORDER BY N
; WITH CTE AS
(
SELECT CAST ( STUFF ( STUFF( DATES ,5,0,'/') , 8 , 0, '/') + ' ' + STUFF( TIMES , LEN(times) - 1 ,0,':') AS DATETIME )DateWithTime
FROM @TableOfDatesAndTimes
)
SELECT CONVERT ( VARCHAR(25) , DateWithTime , 100) AMPMTime
FROM CTE
ORDER BY DateWithTime DESC
But why in the first place u did not store ur date and time values in thier native format ?? DONT mix datatypes !!
May 17, 2011 at 3:37 pm
Both provided solutions look good but I'll go ahead and pile on a third option since I spent the time messing with it 😀
IF OBJECT_ID(N'tempdb..#dates_and_times') > 0
DROP TABLE #dates_and_times ;
GO
CREATE TABLE #dates_and_times
(
my_date VARCHAR(8),
my_time VARCHAR(4)
) ;
GO
INSERT INTO #dates_and_times
(my_date, my_time)
VALUES ('20101212', '1512'),
('20100112', '1512')
GO
WITH cte
AS (
SELECT CONVERT(DATE, my_date) AS my_date,
CONVERT(TIME(0), LEFT(my_time, 2) + ':' + RIGHT(my_time, 2)) AS my_time,
CONVERT(SMALLDATETIME, CONVERT(SMALLDATETIME, my_date) + CONVERT(TIME, LEFT(my_time, 2) + ':' + RIGHT(my_time, 2))) AS my_smalldatetime
FROM #dates_and_times
)
SELECT cte.my_date,
cte.my_time,
cte.my_smalldatetime,
CONVERT(VARCHAR(50), cte.my_smalldatetime, 100) AS my_formatted_smalldatetime
FROM cte
ORDER BY cte.my_smalldatetime DESC ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 17, 2011 at 4:56 pm
Several good solutions here. Just to note that mine does NOT handle times of only three characters (hours < 10); it is assuming that they will be zero filled. If not, change
STUFF(MyTime,3,0,':') to STUFF(RIGHT('0000' + MyTime, 4),3,0,':')
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply