Appy Order by clause on datetime field of type varchar.


Convert varcahar DateTime into specific Datetime format and and apply order by clause on the same field

Problem :We have colunm in DB Tables of type varchar to store Date value. And the values are stored like 15 May 10, 12 june 10, 30 May 10. etc

We want to disply date in same "dd Month YY" (15 May 10) format and in decending/ascending order.


Solution: As the column type is VARCHAR We cont get accurate results. because Order by clause orders the record in ASCII basic (A, B, C, D).

To get accurate result, we have to conver the column value type into DATETIME and apply order by on the typed column


SELECT Convert(char(10), BATCH_STARTDATE,113) BATCHSTARTDATE
FROM TB_COURSE_SCHEDULE WHERE BATCH_COURSECODE='ccna'
GROUP BY BATCH_STARTDATE ORDER BY CAST(BATCH_STARTDATE AS DATETIME) desc

here
BATCH_STARTDATE = Actul data column of type varcahr
BATCHSTARTDATE = is alias name of the column which converted varchar into datetime and into specific format by convert function
CAST(BATCH_STARTDATE AS DATETIME) desc = applies order by one datetime type

Sample test code:

declare @table1 table
(
data1 varchar(15)
)

insert into @table1 values('30 May 10');
insert into @table1 values('15 May 10');
insert into @table1 values('2 June 10');
SELECT Convert(char(10), data1,113) BATCHSTARTDATE
FROM @table1 order by CAST(data1 AS DATETIME)


Comments

No responses found. Be the first to comment...


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: