CODEWITHSUNDEEP
sql
User0911
User0911

Reputation: 1582

Date in SQL Select Query

I have date in my select query in below format 

Thu Sep 04 23:03:30 GMT 2014

and in my Table date is in this format 

13-NOV-13 03.38.22.000000000 AM

How can I convert in the DB format. I tried using

TO_CHAR('Thu Sep 04 23:03:30 GMT 2014','DD-MMM-YY HH:MI:SS')

but not working. Any help will be appreciated.

Upvotes: 0

Views: 89

Answers (3)

Brian DeMilia
Brian DeMilia

Reputation: 13248

This:

select to_char(timestamp '2014-09-04 23:03:30 GMT','DD-MON-YY HH:MI:SS')
from dual

converts the timestamp represented by 'Thu Sep 04 23:03:30 GMT 2014'

into the format 'DD-MON-YY HH:MI:SS' (I think you meant MON, not MMM)

But the input literal has to be changed to '2014-09-04 23:03:30 GMT' (as shown)

Fiddle: http://sqlfiddle.com/#!4/d41d8/34741/0

Output:

04-SEP-14 11:03:30

The format you put in your select list only contains seconds, however, whereas you indicate you are storing fractional seconds in your table (likely a timestamp field), to match that format use FF rather than SS. Also, you indicate you are using HH12 format, not HH (12 hour vs. 24 hour). To do those you would want a different format than you are currently trying to convert to:

select to_char(timestamp '2014-09-04 23:03:30 GMT','DD-MON-YY HH12:MI:FF AM')
from dual

Fiddle: http://sqlfiddle.com/#!4/d41d8/34744/0

Note that if the time were in the latter half of the day it would show PM rather than AM, despite the fact that you see AM in the sql (refer to http://www.techonthenet.com/oracle/functions/to_char.php)

Output:

04-SEP-14 11:03:000000000 PM

Upvotes: 1

Espen
Espen

Reputation: 1

Have you tried to convert?

CONVERT(NVARCHAR(20),GETDATE(),13)

All the diffrent date formats can be found here: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 0

ItalianStallion
ItalianStallion

Reputation: 296

Try This:

LEFT(DATENAME(dw,date),3) +' '+     CAST(date AS VARCHAR(20))

Upvotes: 0

Related Questions