How i can convert format date to d/M/yyyy and convert format time to h:mm:ss AM in SQL

Question

I have created a field of type datetime. I want to display this in YYYY/M/D format

example

date 09/09/2014 to display as 2014/9/9

Also I need display a time field [Time_Orde] in this format 2:43:14 PM

I use SQL server 2008

I have tried the function

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

My code:

SELECT CONVERT(date,Order_Date) from Orders_Teakeaway

But I could not get this format 2014/9/9

Answer 1

There is no single style number that will give you YYYY/M/D 2014/9/9

Try this:

convert(varchar(5),Order_Date,111) + replace(convert(varchar(5),Order_Date,1),'0','')

Style 111 is YYYY/MM/DD but we only take the first 5 chars (YYYY/) Style 1 is MM/DD/YY but again we only take the first 5 chars (MM/DD) then replace any zero's in the MM/DD part and concatenate

For the time, try this

  left(right(CONVERT(VARCHAR(20), Time_Orde, 100),7),5)
+ left(right(CONVERT(VARCHAR(27), Time_Orde, 9),9),3)
+ right(CONVERT(VARCHAR(20), Time_Orde, 100),2)