Oracle: String to Date, Timestamp with Timezone

I recently got a text from a friend to convert '2021-04-07T14:03:54.000000-0700' string to date.

He Tried -
SQL> select
  2  to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM')
  3  from dual;
to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DDHH:MI:SS.FFTZH-TZM')
                                          *
ERROR at line 2:
ORA-01821: date format not recognized
but it was failing with ORA-01821: date format not recognized.

There are 2 issues in above SQL. 

1. The format in the SQL is itself incorrect. The correct format is 'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'.
SQL> select
  2  to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
  3  from dual;
to_date('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
                                          *
ERROR at line 2:
ORA-01821: date format not recognized
2. The use of the TO_DATE function is incorrect also, the value is timestamp with timezone, so we can try to_timestamp instead of to_date
SQL> select
  2  to_timestamp('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
  3  from dual;
to_timestamp('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
             *
ERROR at line 2:
ORA-01821: date format not recognized
but to_timestamp is also failing. 

Oracle provides to_timestamp_tz to convert a string to timestamp with timezone.
SQL> select
  2  to_timestamp_tz('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM')
  3  from dual;

TO_TIMESTAMP_TZ('2021-04-07T14:03:54.000000-0700','YYYY-MM-DD"T"HH24:MI:SS.
---------------------------------------------------------------------------
07-APR-21 02.03.54.000000000 PM -07:00
Now we just need to convert this to date.
SQL> select
  2  cast(
  3  to_timestamp_tz('2021-04-07T14:03:54.000000-0700',
  4  'YYYY-MM-DD"T"HH24:MI:SS.FFTZHTZM'
  5  ) as date
  6  )
  7  from dual;

CAST(TO_TIMESTAMP_TZ
--------------------
07-Apr-2021 14:03:54
If someone do not want to go into these details, he can simply remove the data after seconds and then convert it to date
SQL> select
  2  to_date(
  3  regexp_replace('2021-04-07T14:03:54.000000-0700','\..+'),
  4  'YYYY-MM-DD"T"HH24:MI:SS'
  5  )
  6  from dual;

TO_DATE(REGEXP_REPLA
--------------------
07-Apr-2021 14:03:54

Related Posts - 

59 comments:

  1. Nice Post! Thanks for sharing such an amazing article, really informative, it helps me a lot.
    Oracle Database Certification

    ReplyDelete
  2. The excellent article is pretty impressive and really interesting to read. looking forward to a more informative blog. keep up the good work. We would like to express our service of cash for cars and car removal with the only the purpose to serve our residents for a better solution rather than privately selling the cars.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. Nice Article and love to read it. I'm using the same blog platform as yours. A1Wreckers is the best cash for cars Brisbane service in Queensland area. If you have a wrecked car then you should get rid of it. Everyone loves extra cash by getting cash for cars Brisbane. We all need money to survive this bad situation. We are here to help you in this manner. Contact us

    ReplyDelete
  5. Thanks, foг ones marvelous posting! I genuinely enjoyed reading it, you miggһt Ƅe a great author. Au Cash for Cars is the Gold Coast’s No.1 Cash For Cars Buyer. Instant cash on the spot up to $11,000. 100% customer satisfaction guaranteed. Sell an old car today and earn top cash for cars Gold Coast with us.

    ReplyDelete
  6. Thank you very much for giving this useful information. I searched for this information for a long time but was unable to locate a reliable source. I really appreciate the time and effort you put into writing this fantastic essay. Here, I've learned a lot of useful information. It's well worth bookmarking for future reference. Let me share a reputable website for sell my car Brisbane and free car removals Brisbane services can be found here.

    ReplyDelete
  7. The data you supplied on this website is quite helpful. Keep up the wonderful work, and may God bless you. Check out the Cash for Cars Adelaide and Car Removals Adelaide services if you need to get rid of an old, trash, or unwanted vehicle while also earning some cash.

    ReplyDelete
  8. Nice article. Thanks for sharing with us. Please continue publishing helpful topics like this. Check our Car Removal Adelaide and Cash for Cars Adelaide. Thank you!

    ReplyDelete
  9. Cash for cars Brisbane are a great option to sell your car. These companies offer many different services to make the process easy and quick. For example, you can choose to have the car collected the same day if you would liketop cash for car brisbane

    ReplyDelete
  10. If you're thinking about selling your vehicle, Cash for Cars Melbourne is a great option. The company buys all types of vehicles, from new to used, and can even pay cash on the spot.car removal melbourne

    ReplyDelete
  11. thank you for a wonderful information. To know about automated help desk and service desk and also to read blogs about it. click here to know more: ms teams helpdesk chatbot

    ReplyDelete
  12. Wonderful put up really pleasant to read and attractive as well. Cash For Cars Brisbane is looking to assist people who are willing to sell cars and earn money out of it. Sell8 is the best old car buyers deal with scrap, old ,junk, used, unwanted and lots more.

    ReplyDelete
  13. This is an excellent article. I'm overjoyed to have discovered it. Thank you for providing this information. Thumbs up! It’s good to see so many positive customer reviews and happy clients! By choosing. cash for cars gold coast you can get your car removed and receive cash up to $9,999. With minimal paperwork and free towing, rest assured your car is in good hands. So, why wait to avail the best cash for cars offers.

    ReplyDelete
  14. The excellent article is pretty impressive and really interesting to read.
    We are the major Cash For Cars service provider in Melbourne, if you are looking to sell your car, contact us now.

    ReplyDelete
  15. Thanks for posting a very sweet article. So much needed data I'll get and keep in my file. Thank u so much.
    ba 3rd year result roll number wise

    ReplyDelete
  16. Appreciating the time and effort you put into your website. Searching to remove your Car for Cash? Metal Biz Recyclers warm-heartedly greets every customer with any car they have. With our 100% customer satisfaction, you can easily get Cash for cars Brisbane up to $9,999.

    ReplyDelete
  17. Tab City is a new project which is currently developing in Rawalpindi.

    ReplyDelete
  18. Really happy to be here.
    When I was searching for the Car Removals Sydney deals, I happened to read many, but I can't find anything here and obviously, this will add the greatest attention to the readers. The best way and best method of car selling topics can be described and it will helpful to your readers.

    ReplyDelete
  19. I found the information provided very helpful and informative. It's great to know that there are services available to help people get rid of their old car. Thanks for sharing!
    Keep posting!
    Fast Car Removal Sydney

    ReplyDelete
  20. Your page has got very important information which I was looking for for a long time this is the best page of 2023 see rest of the information
    Syed Rizwan Ahmed Wiki
    Cecilia Asoro wiki
    Lisa Marie Presley Wiki
    Erika Donalds Wiki

    ReplyDelete
  21. I like your post, if you know about my blog so, read about it.One option is to Sell Your Car for Cash in Cranebrook privately through classified ads or online marketplaces. This can take some time and effort, but you may be able to get a higher price for your car than if you were to sell it to a dealership or car buying service.Another option is to sell your car to a car buying service that specializes in purchasing vehicles for cash. These companies will typically provide you with a quote based on the make, model, year, and condition of your car. If you accept their offer, they will come to you to inspect the car and finalize the sale.

    ReplyDelete
  22. This comment has been removed by the author.

    ReplyDelete
  23. This comment has been removed by the author.

    ReplyDelete
  24. Digital Digital marketing involves some of the same principles as traditional marketing and is often considered an additional way for companies to approach consumers and understand their behaviour. Companies often combine traditional and digital marketing techniques in their strategies. But digital marketing comes with its own set of challenges, including implicit bias.

    ReplyDelete
  25. Food consists essentially of protein, carbohydrates, fat, and other nutrients used in the body of an organism to sustain growth and vital processes and to furnish energy. The absorption and utilization of food by the body are fundamental to nutrition and are facilitated by digestion.

    ReplyDelete
  26. Very Good! Informational sites keep it up. Best of luck for the future.
    car removal brisbane

    ReplyDelete
  27. Thank you for sharing this informative and helpful article on converting string values to date, time, and timestamp with timezone in Oracle. As someone who frequently works with databases, I found your explanations and examples to be clear and easy to follow.
    Regards: Old Car Removal Sydney

    ReplyDelete
  28. I also appreciate the inclusion of various formatting options, as formatting can often be a tricky aspect when dealing with date and time values.
    Regards:Cash for Used Cars Sydney

    ReplyDelete
  29. Overall, I found your article to be well-written, informative, and a valuable resource for anyone working with Oracle databases. It certainly helped me gain a deeper understanding of handling date, time, and timestamp conversions in Oracle.
    Regards: We Buy Any Car Sydney

    ReplyDelete
  30. Car Removal Sydney offers hassle-free and swift services, ensuring seamless removal of your old or unwanted vehicles while providing top cash offers in the Sydney area.

    ReplyDelete
  31. I am intrigued by the writer's style even though he uses straightforward language and sentence structures. In this article, we will inform the audience of how to get cash for scrap and non-running cars and the fairest prices offered by Cash For Cars Brisbane.

    ReplyDelete
  32. Identifying the main focus of the customer was one of the best things about the article. Non-running vehicles can be sold to Get Cash For Cars. Get Cash For Cars is an auto remarketing company with the business name Sell My Car Brisbane. The free car evaluations we provide our customers are a way for us to demonstrate our appreciation for their time

    ReplyDelete
  33. In order to engage the reader, the writer must have a good understanding of how to do so. Metal Biz Recyclers already has a large following of people who know me. In the near future, I will write about the friendly and knowledgeable professionals at Cash For Cars Brisbane, which I own and operate.

    ReplyDelete
  34. An excellent job was done by the writer to structure this article and make it enticing! Fast Car Removals is committed to eco-friendliness and offers Cash for Scrap Cars Sunshine Coast with recycled automotive parts.

    ReplyDelete
  35. Wow, what an incredibly informative blog post on converting Oracle string to date timestamp! The detailed explanations and step-by-step guide make it easy to understand even for a newbie like me. What caught my attention was how seamlessly this feature aligns with the We Buy Your Car services.

    ReplyDelete
  36. Your article on converting string values to date, time, and timestamp with timezone in Oracle is both informative and easy to understand. As someone who frequently deals with databases, I truly appreciate the clarity you've provided in your explanations and examples.
    Regards: Cash For Cars Brisbane

    ReplyDelete
  37. Hey there! 👋 Just stumbled upon this tech-savvy post about converting strings to date timestamps on nimishgarg.blogspot.com. 📅🔧 It's like unraveling coding mysteries! But here's what's piqued my interest: the mention of "Car Removal Townsville" right in the midst of all these tech details. 🚗♻️ Who would've thought we'd switch from programming to car removal in the blink of an eye? Now that's some unexpected diversity in content! 💻🔁 Time to see how this keyword fits into the tech landscape. ♻️💰

    ReplyDelete
  38. Wow, I'm really impressed with the car removal services offered here in Adelaide! The convenience and efficiency of their car body removal service are truly remarkable. It's such a relief to know that I can easily bid farewell to my old vehicle while also contributing to a cleaner environment. The fact that they offer free car removals adelaide is a game-changer – no more worrying about the cost of getting rid of that unwanted car taking up space.

    ReplyDelete
  39. Your blog is a refreshing source of inspiration and knowledge, offering valuable insights with every post. Thank you for sharing your expertise and passion with the world!
    Looking to free up space and earn cash for your old car? Contact our trusted car removal service today for quick and hassle-free Cash for Car Removal Brisbane with top-dollar payouts!

    ReplyDelete
  40. It's pivotal to understand that the best PEO servicesbest PEO services are subjective. What works for a tech startup might not be the right fit for a manufacturing behemoth. Tailored solutions, personalized support, and an understanding of industry-specific challenges are what differentiate good PEOs from the best ones.

    ReplyDelete
  41. Contract staffing in UAE helps companies by providing flexible workforce solutions. They can hire skilled workers temporarily, saving costs and adapting to changing needs.

    ReplyDelete
  42. Dubai Freezone Business Setup offers tax advantages, full ownership, and no import fees, making it ideal for entrepreneurs.

    ReplyDelete
  43. Starting a business setup in a UAE free zone can cost around $5,000 to $20,000, depending on the free zone and business type. The cheapest free zone in the UAE varies, but some cost-effective options include Ajman Free Zone and Umm Al Quwain Free Zone.

    ReplyDelete
  44. Some of the top business centers in Dubai include DMCC Business Center, Regus Dubai, and Dubai World Trade Centre.

    ReplyDelete
  45. That's what I was talking about. Now you can check your nbad salary check UAE and ratibi card balance check in just a few moments.

    ReplyDelete
  46. This comment has been removed by the author.

    ReplyDelete
  47. pg เว็บตรง มี เว็บ อะไรบ้าง เป็นที่น่าสนใจและคู่ควรสำหรับผู้เล่นสล็อตที่ต้องการประสบการณ์กที่ยอดเยี่ยมและมีคุณภาพด้วยการนำเสนอเกม PG ที่หลากหลายและกราฟิกที่สวยงามที่น่าตื่นเต้น

    ReplyDelete