February 22, 2015 at 11:08 am
I have created a table with a field Register_Date in DATE format. However, it results showed me 21/12/2008
I would like to change it to 21/Dec/2008
How do I change that using SQL command? I tried to Google and used codes like below and it will not work
SELECT CONVERT( CHAR(6), Register_Date, 106 )
FROM REG
The error message was ORA-00936: missing expression
I also tried:
SELECT replace(convert(char(15),Register_Date,106),' ',' - ')
FROM REG
and it gives it the same error message.
Any suggestions?
Thanks
February 22, 2015 at 11:18 am
Here's one way. Not sure if there's anything more elegant.
declare @d date = '20081221'
select @d
,replace(convert(char(11), @d, 13), ' ', '/')
And presumably you are aware that formatting of dates for display purposes is usually something which is done by the client application.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 22, 2015 at 11:25 am
Thank you so much. However, the declare function is giving me an error message
ORA-06550: line 1, column 9:
PLS-00103: Encountered the symbol "@" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
1. declare @d date = '20081221'
I may have done something wrong since I am new to SQL
February 22, 2015 at 11:29 am
sk88 (2/22/2015)
Thank you so much. However, the declare function is giving me an error messageORA-06550: line 1, column 9:
PLS-00103: Encountered the symbol "@" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior
1. declare @d date = '20081221'
I may have done something wrong since I am new to SQL
Are you using Oracle?
😎
February 22, 2015 at 11:32 am
Yes you are right. I am using Oracle
February 22, 2015 at 11:34 am
sk88 (2/22/2015)
Yes you are right. I am using Oracle
You're in the wrong forum, I'm afraid: this one is for SQL Server 🙂
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 22, 2015 at 11:42 am
I thought its related to SQL so I came here 😀 hahah sorry. I will post in the Oracle forum.
Thanksss!!
February 22, 2015 at 11:44 am
sk88 (2/22/2015)
I thought its related to SQL so I came here 😀 hahah sorry. I will post in the Oracle forum.Thanksss!!
That would be fine, if all dialects of SQL were the same. But, as you've discovered, they are often quite different, once you go beyond SELECT, INSERT, UPDATE and DELETE.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 23, 2015 at 7:25 am
This is simple and any Oracle forum should give you the answer, but I'll include it here.
You need to use the TO_CHAR() function to format dates and TO_DATE() to enter dates in a specific format.
eg.
SELECT TO_CHAR(SYSDATE, 'DD/MON/YYYY')
,TO_DATE('21/FEB/2015', 'DD/MON/YYYY')
FROM Dual;
February 23, 2015 at 7:30 am
Luis Cazares (2/23/2015)
This is simple and any Oracle forum should give you the answer, but I'll include it here.You need to use the TO_CHAR() function to format dates and TO_DATE() to enter dates in a specific format.
eg.
SELECT TO_CHAR(SYSDATE, 'DD/MON/YYYY'),TO_DATE('21/FEB/2015', 'DD/MON/YYYY')
FROM Dual;
Traitor! :hehe:
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 23, 2015 at 7:33 am
Phil Parkin (2/23/2015)
Luis Cazares (2/23/2015)
This is simple and any Oracle forum should give you the answer, but I'll include it here.You need to use the TO_CHAR() function to format dates and TO_DATE() to enter dates in a specific format.
eg.
SELECT TO_CHAR(SYSDATE, 'DD/MON/YYYY'),TO_DATE('21/FEB/2015', 'DD/MON/YYYY')
FROM Dual;
Traitor! :hehe:
I'm just demonstrating that SQL Server community is more helpful than many others. 😀
February 24, 2015 at 12:08 pm
Phil Parkin (2/23/2015)
Luis Cazares (2/23/2015)
This is simple and any Oracle forum should give you the answer, but I'll include it here...Traitor! :hehe:
Nah, he's just bi-something. 😀
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
February 25, 2015 at 5:25 am
can you try this.
select replace(CONVERT(VARCHAR(11),GETDATE(),106),' ','/')
February 25, 2015 at 5:30 am
select replace(CONVERT(VARCHAR(11),GETDATE(),106),' ','/')
February 25, 2015 at 5:33 am
gopi6417 (2/25/2015)
select replace(CONVERT(VARCHAR(11),GETDATE(),106),' ','/')
Does this syntax work on Oracle?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply