August 25, 2009 at 1:49 am
Hi All,
i have a table which contains details with respect to date and time. the data in date column is stored as string 20090825 and the same for time like 104432. i need to get the latest records depending on date and time like below formt
name enterdate entertime
khaled 20080825 104432
khaled 20080825 111021
khaled 20080825 120010
fairozkhan 20080825 101130
fairozkhan 20080825 134012
desired output
name enterdate entertime
khaled 20080825 120010
fairozkhan 20080825 134012
on the sql in converting the date and time to date and time format and using the max function. but it is not returning the expected results.
select name, max(convert(varchar,convert(datetime,History.date_in,108),103))as [Date In],
max(convert(varchar(8),convert(datetime,left(History.Time_In,2) + ':' + substring(History.Time_In,3,2) + ':' + right(History.Time_In,2),108),108))as [Time In]
From History
group by
convert(varchar,convert(datetime,History.date_in,108),103),
convert(varchar(8),convert(datetime,left(History.Time_In,2) + ':' + substring(History.Time_In,3,2) + ':' + right(History.Time_In,2),108),108), name
any help is higly appreciated.
August 25, 2009 at 2:09 am
I would urge you to store the data as a datetime column on your table which make sense for a whole lot of reasons. But this should answer your question
with cteconvdates(name,enterdatetime)
(
select name,convert(datetime,enterdate+' '+stuff(stuff(entertime,3,0,':'),6,0,':'))
from History
)
select name,max(enterdatetime)
from cteconvdates
August 25, 2009 at 3:59 am
Hi SSC,
I tried ur sql and modified like below but it is giving me error incorrect syntax near '('
with ct(subject_id,time_in1)
(
select subject_id,
convert(datetime,left(History.Time_In,2) + ':' + substring(History.Time_In,3,2) + ':' + right(History.Time_In,2),108)
as time_in1
from history
)
select subject_id, max(time_in1)
from ct
August 25, 2009 at 4:04 am
If this doesnt work please post DDL , so that i can test it .
with ct(subject_id,time_in1)
as
(
select subject_id,
convert(datetime,left(History.Time_In,2) + ':' + substring(History.Time_In,3,2) + ':' + right(History.Time_In,2),108)
as time_in1
from history
)
select subject_id, max(time_in1)
from ct
August 25, 2009 at 4:29 am
thanks SSC it did worked
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply