SQL query to convert Date to another format

0
votes

Thanks for your help. I am not able to make out the type/format of the "Value" in a Date column.I guess its in Julian Date format.

The Column is paid_month and the values are below.

                  200901
                  200902

So,please help in writing SQL query to convert the above values(Mostly in Julian Format) in the Date Column to normal date (MM/DD/YYYY) .

Thanks Rohit

Hi,

I am sorry for missing in giving the whole information.

1)Its a Oracle Database. 2)The column given is Paid_Month with values 200901,200902

3)I am also confused that the above value gives month & year.Day isnt given if my guess is right.

4)If its not in Julian format ,then also please help me the SQL to get at least mm/yyyy

I am using a Oracle DB and running the query THANKS i GOT THE ANSWER.

**Now,i have to do the reverse meaning converting a date 01/09/2010 to a String which has 6 digits. Pls help with syntax- select to_char(01/01/2010,**

4
sqloracledate-conversion
What database are you using? The functions for converting to date vary between vendors. – Bohemian
Should say which flavor SQL you are working on.... – tofutim
I'm guessing paid_month is actually a (var)char column, rather than a date(time) column also... – Damien_The_Unbeliever
Or number(6) or int4 or something? year=paid_month/100, month=paid_month % 100, day=01? Where shall the day come from? I agree, we need the DB-vendor, because converting is vendor dependent. And we need to know the format of paid_month. String, number, something else? – user unknown

4 Answers

2
votes

It looks like YYYYMM - depending on your database variant, try STR_TO_DATE(paid_month, 'YYYYMM'), then format that.

Note: MM/DD/YYYY is not "normal" format - only Americans use it. The rest of the world uses DD/MM/YYYY

0
votes

For MySQL, you would use the STR_TO_DATE function, see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_str-to-date

SELECT STR_TO_DATE(paid_month,'%Y%m');

Sounds like the column contains some normal dates and some YYYYMM dates. If the goal is to update the entire column, you can attempt to isolate the YYYYMM dates and update only those. Something like:

UPDATE YourTable
SET paid_month = DATE_FORMAT(STR_TO_DATE(paid_month, '%Y%m'), '%m/%d/%Y')
WHERE LENGTH(paid_month) = 6
0
votes
SELECT (paid_month % 100) + "/01/" + (paid_month/100) AS paid_day
FROM tbl;

I'm not sure about how oracle concatenates strings. Often, you see || in SQL:

SELECT foo || bar FROM ...

or functions: 

SELECT cat (foo, bar) FROM ...