0

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

Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
Sherif Hamdy
  • 587
  • 6
  • 10
  • which rdbms you are using? – radar Sep 27 '14 at 01:13
  • 1
    Read your own question again and see if it makes any sense..`" I want to convert format date to: d / M /yyyy"`... then you said `"date 09/09/2014 to 9-9-2014 and right to left 2014-9-9 in sql converter"` , What exactly you want, could you not have simply said I have Datetime field which in such such format and I want it to be in such such format. Ask a question here not a riddle. – M.Ali Sep 27 '14 at 01:40
  • That, after converting the format to date : 2014/9/9 and for the time: 2:45:14 PM – Sherif Hamdy Sep 27 '14 at 01:59

1 Answers1

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)
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51