Date & Time formatting to sort by date and time.

Hi,
I have a Startdate and StartTime in my table, I would like to combine to the
2 columns as one column so that it can be sorted as date.
startDate - smalldatetime
startTime - nvarchar ( I cannot change this)
my current query:
select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
null then '' else ' '+ses_start_time end,
from Course
where courseID=1
order by StartsAt
The problem with the above query is that it does not sort by date and timecorrection to my query -
current query:
select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
null then '' else ' '+ StartTime end,
from Course
where courseID=1
order by StartsAt
"Mike" wrote:

> Hi,
> I have a Startdate and StartTime in my table, I would like to combine to t
he
> 2 columns as one column so that it can be sorted as date.
> startDate - smalldatetime
> startTime - nvarchar ( I cannot change this)
> my current query:
> select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
> null then '' else ' '+ses_start_time end,
> from Course
> where courseID=1
> order by StartsAt
> The problem with the above query is that it does not sort by date and time|||Can you give us some sample data? Did you try
ORDER BY Convert(DATETIME, convert(varchar, StartDate,111)+case when
StartTime is null then '' else ' '+ses_start_time end)
Also, suggest you define varchar(length) and not just leave the default.
You will be surprised when you get varchar(30) in some places and varchar(1)
in others. Finally, I also suggest a non-ambiguous ANSI-standard format
style, such as 112 or 120.
A
"Mike" <Mike@.discussions.microsoft.com> wrote in message
news:1E03CBCF-31DD-4530-BE5C-DD3F2F2134EF@.microsoft.com...
> Hi,
> I have a Startdate and StartTime in my table, I would like to combine to
> the
> 2 columns as one column so that it can be sorted as date.
> startDate - smalldatetime
> startTime - nvarchar ( I cannot change this)
> my current query:
> select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
> null then '' else ' '+ses_start_time end,
> from Course
> where courseID=1
> order by StartsAt
> The problem with the above query is that it does not sort by date and time|||> Finally, I also suggest a non-ambiguous ANSI-standard format style, such
> as 112 or 120.
...this will sort correctly without the additional conversion (provided you
are storing time in 24H/military time, not 12H/AM/PM format).|||time is 12h format
sample data:
2006/05/14 11:00AM
2006/05/14 4:00 AM
2006/05/14 9:00 AM
I would like it to sort as
2006/05/14 4:00 AM
2006/05/14 9:00 AM
2006/05/14 11:00 AM
I tried your way but the 'AM' and 'PM' is not displayed.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Can you give us some sample data? Did you try
> ORDER BY Convert(DATETIME, convert(varchar, StartDate,111)+case when
> StartTime is null then '' else ' '+ses_start_time end)
> Also, suggest you define varchar(length) and not just leave the default.
> You will be surprised when you get varchar(30) in some places and varchar(
1)
> in others. Finally, I also suggest a non-ambiguous ANSI-standard format
> style, such as 112 or 120.
> A
>
>
> "Mike" <Mike@.discussions.microsoft.com> wrote in message
> news:1E03CBCF-31DD-4530-BE5C-DD3F2F2134EF@.microsoft.com...
>
>|||try this... untested..
select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
null then '' else ' '+ses_start_time end,
from Course
where courseID=1
order by startdate asc, cast(starttime as datetime) asc
"Mike" wrote:

> Hi,
> I have a Startdate and StartTime in my table, I would like to combine to t
he
> 2 columns as one column so that it can be sorted as date.
> startDate - smalldatetime
> startTime - nvarchar ( I cannot change this)
> my current query:
> select StartsAt As convert(varchar, StartDate,111)+case when StartTime is
> null then '' else ' '+ses_start_time end,
> from Course
> where courseID=1
> order by StartsAt
> The problem with the above query is that it does not sort by date and time|||> I tried your way but the 'AM' and 'PM' is not displayed.
What is "my way"? Can you show specs (see http://www.aspfaq.com/5006)
including sample data (INSERT statements), and the query you ran that
somehow dropped off the AM/PM?