Then gives you the appropriate week number. This formula uses the 6th April each and every year as your first possible date of the fiscal year and calculates where the first Monday is. I do have one further formula you could use that is not my own but may help you. If you want to tweak that so 6th April 2013 is actually in week 1 of 2013 and still have Monday as your first day of the week that would automatically mean the 1st to 5th April would also be in week 1. Which means the first Monday of the new fiscal year would be 8th April 2013 = Week 1. using those dates for 2013 would suggest 6th April 2013 is actually in week 52 as it is a Saturday. In you original post you wanted week numbering from the 6th of April to the 5th April each year using Monday as your start day of the week. However I understand the frustration you can sometimes feel when you are trying to find an answer. You have had some excellent answers from two great Excel Ninja's. If not please advise so as people who read it could get back to you as soon as possible.Īssuming your date is in cell A1 try this: You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you. 50 which does not confirm with our working calender. If I convert (Sunday) to week number the result is week No. Using the solutions above gave the following : if I convert to week number the conversion week is 51 which is OK.
#Oracle week number free#
There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).Īmong them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.įeel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Our first working day of the week is sunday. Thank you for your joining us and glad to have you here.Īs a starting point I'd recommend you to read the green sticky topics at this forums main page. Which will work whatever your NLS settings are, as there is no longer any implicit conversion going on.First of all welcome to Chandoo's website Excel forums. To_number(to_char(date_created,'IW')) as WEEK_PRODUCED Just do: to_number(to_char(date_created,'IW')) as WEEK_PRODUCEDĪs in, with the same CTE: with your_table (date_created) as ( (You'd still have issues with BCE dates, of course.)īut you shouldn't be converting the original date at all. The implicitly-generated string now has a 4-digit year, so converting it back preserves the original century. If your session had a 4-digit year model then it would work: alter session set nls_date_format = 'DD/MM/YYYY'
#Oracle week number iso#
So, you're getting the ISO week number for January 7th in the year 19, not the year 2019, and in that year it was ISO week 1. (That's what the RRRR format model is for.) Notice that the conv_date, which has been converted to a string using the NLS 2-digit year model, and then back to a date using a 4-digit year model, has lost it's century you seem the same with literals: select to_char(to_date('01/07/19', 'DD/MM/YYYY'), 'YYYY-MM-DD') from dual īecause 19 is, well, year 19 there's no hint or mechanism here to assume you meant this century. NLS_DATE ISO_DATE CONV_DATE WEEK_PRODUCED
To_number(to_char(to_date(date_created,'DD/MM/YYYY'),'IW')) as WEEK_PRODUCED To_char(to_date(to_char(date_created), 'DD/MM/YYYY'), 'YYYY-MM-DD') as conv_date,
To_char(date_created, 'YYYY-MM-DD') as iso_date, As a demo, providing your date value via a CTE: alter session set nls_date_format = 'DD-Mon-RR' If your NLS settings are set to show dates with 2-digit years, e.g the still-default 'DD-Mon-RR' format, then your conversion of the date value to a string and back again is losing the century. This appears to be because of implicit date conversion - assuming that date_created is actually a DATE column not a string.
0 kommentar(er)
