Sorting data on date n time values

Question

Post reply

Sorting data on date n time values

lucky-80472

SSCertifiable

Points: 7793
More actions
February 19, 2004 at 1:01 am

#161502

Hi,
I want to know the procedure for sorting the data and publish back on date and time. I am having the data as follows:
JobID    JobTilte                                        JobPostedDate
11        Unit Tester                                   2004-02-06 20:53:19.433
13        devoleper                                     2004-02-07 14:31:27.173
14        develpoer                                     2004-02-09 11:44:56.050
15        system admin                                2004-02-09 11:48:38.580
16        Project Manager                            2004-02-09 11:51:50.177
17        Remote Access Support Analyst       2004-02-17 00:00:00.000
18        Business Analyst                           2004-02-17 00:00:00.000
When i run the query like this
SELECT JOBID, JOBTITLE, JOBPOSTEDDATE
  FROM JOB_POSTINGS  ORDER BY CONVERT(VARCHAR(5), JOBPOSTEDDATE, 108), 8) ASC
am getting the data like this:
JobID    JobTitle                                        JobPostedDate
17        Remote Access Support Analyst         2004-02-17 00:00:00.000
18        Business Analyst                             2004-02-17 00:00:00.000
14        develpoer                                      2004-02-09 11:44:56.050
15        system admin                                 2004-02-09 11:48:38.580
16        Project Manager                             2004-02-09 11:51:50.177
13        devoleper                                      2004-02-07 14:31:27.173
11        Unit Tester                                    2004-02-06 20:53:19.433
which should be like :
JobID     JobTitle                                       JobPostedDate
17         Remote Access Support Analyst       2004-02-17 00:00:00.000
18         Business Analyst                           2004-02-17 00:00:00.000
16         Project Manager                           2004-02-09 11:51:50.177
15         system admin                               2004-02-09 11:48:38.580
14         developer                                    2004-02-09 11:44:56.050
13         devoleper                                    2004-02-07 14:31:27.173
11         Unit Tester                                  2004-02-06 20:53:19.433
Can anybody throw some light, where i am going wrong?
Thanks in advance for your suggestion and guidance.

Lucky

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Ahem, maybe I've missed again something but wouldn't this do the job

SELECT JOBID, JOBTITLE, JOBPOSTEDDATE

FROM JOB_POSTINGS ORDER BY JOBPOSTEDDATE desc

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 2

Hi Frank,

Thanks for the reply. I have modified the query and checked, but the same probs. Is there any other way of working it?

Lucky

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

set nocount on

if object_id('job_postings') is not null

drop table job_postings

go

create table job_postings(

jobid int,

JOBTITLE varchar(30),

JOBPOSTEDDATE datetime)

go

insert into job_postings (jobid, JOBTITLE, JOBPOSTEDDATE) values(11,'Unit Tester','06.02.2004 20:53:19.433')

insert into job_postings (jobid, JOBTITLE, JOBPOSTEDDATE) values(13,'devoleper','07.02.2004 14:31:27.173')

insert into job_postings (jobid, JOBTITLE, JOBPOSTEDDATE) values(14,'devoleper','09.02.2004 11:44:56.050')

insert into job_postings (jobid, JOBTITLE, JOBPOSTEDDATE) values(15,'system admin','09.02.2004 11:48:38.580')

insert into job_postings (jobid, JOBTITLE, JOBPOSTEDDATE) values(16,'Project Manager','09.02.2004 11:51:50.177')

insert into job_postings (jobid, JOBTITLE, JOBPOSTEDDATE) values(17,'Remote Access Support Analyst','17.02.2004 00:00:00.000')

insert into job_postings (jobid, JOBTITLE, JOBPOSTEDDATE) values(18,'Business Analyst','17.02.2004 00:00:00.000')

go

SELECT JOBID, JOBTITLE, JOBPOSTEDDATE

FROM JOB_POSTINGS ORDER BY JOBPOSTEDDATE desc

drop table job_postings

set nocount off

JOBID JOBTITLE JOBPOSTEDDATE

----------- ------------------------------ -----------------------

17 Remote Access Support Analyst 2004-02-17 00:00:00.000

18 Business Analyst 2004-02-17 00:00:00.000

16 Project Manager 2004-02-09 11:51:50.177

15 system admin 2004-02-09 11:48:38.580

14 devoleper 2004-02-09 11:44:56.050

13 devoleper 2004-02-07 14:31:27.173

11 Unit Tester 2004-02-06 20:53:19.433

Isn't that what you are after?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

lucky-80472 SSCertifiable Points: 7793 More actions · Answer 4

Hi Frank,

Thanks once again for the effort put in. The query is working fine on my local server, and when it is run on the live it is playing. I have cheked the versions on both the systems and the details are as follows:

My Local Server:

Live Server:

The difference, i could trace out is only the OS service pack, which my server is having a lesser version than compared to my live server and also the Microsoft Internal build number is different.

Can you guide me in this aspect?

Lucky