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 5 (of 5 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

Johan Bijnens SSC Guru Points: 135375 More actions · Answer 5

with

<< SELECT JOBID, JOBTITLE, JOBPOSTEDDATE

FROM JOB_POSTINGS ORDER BY CONVERT(VARCHAR(5), JOBPOSTEDDATE, 108) ASC >>

your query only sorts on the TIME(HH:mm)-part of your datetime (108format check BOL) !

If that is wat you want, your query works as designed

If you want to order based on full date and time, Frank Kalis has given the solution

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me