I need help writing a SELECT query. 2

[rarubio1]

My SELECT query needs to format a field whose datetype is smalldatetime. The values returned by my query look like this: 1/1/1900 9:40:00 AM.
I need the values to look like this: 9:40 AM

How can I write my query so that it formats the field for me?

Thanks,
RR

 

[r937]

assuming 1/1/1900 implies mm/dd/yy, then this will work for you --

select convert(char(10),datecol,101)
     + substring(
       convert(char(30),datecol,100)
                ,12,7)  as dt
  from yourtable

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[forevertechie]

I guess you could create a UDF that mimics
select convert(char(10),getdate(),101) + ' '+ convert(char(5),getdate(),114)

I hope this helps.

- forevertechie

 

[r937]

forevertechie, style 114 doesn't give AM/PM



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)