将日期与预定义格式进行比较 [英] comparing date with a predefined format pl sql

问题描述

我有一个函数，该函数应接受格式为DD-MON-YY的日期，并应显示为格式为DD-MM-YYYY的日期.我创建的函数是:

I have a function which should accept date in a format DD-MON-YY and should display in the format DD-MM-YYYY. The function I have created is :

create or replace function PRINT_IT(abc date)
RETURN DATE
IS
  v_date DATE;
BEGIN
  if regexp_like(abc,'^[0-9]{2}-[a-z]{3}-[0-9]{2}$')
  then
    v_date := TO_DATE(abc,'DD-MM-YYYY');
  else
    dbms_output.put_line('wrong format');
  end if;
  dbms_output.put_line('The date is ');
  return v_date;
END PRINT_IT;

但是返回的值始终是错误的日期格式！

but the value returned is always wrong date format!!

推荐答案

不，不是.您的日期以您的 NLS_DATE_FORMAT .我想显示(如果不同)，然后为您的会话更改此参数:

No, it's not. Your date is being output in the format specified by your NLS_DATE_FORMAT. I you want to display if differently then change this parameter for your session:

alter session set nls_date_format = 'dd-mm-yyyy'

注意单词 display .这就是全部.那就是您应该考虑做的所有事情.日期的显示方式绝不会影响其存储方式.

Note the word display. That's all this does. That's all you should consider doing. The way a date is displayed in no way effects the way it is stored.

通常，您可以使用 TO_CHAR()具有适当的格式模型以显示日期，即to_char(my_date, 'dd-mm-yyyy').它将不再是日期，而是一个字符.

More normally you might use TO_CHAR() with an appropriate format model to display a date, i.e. to_char(my_date, 'dd-mm-yyyy'). It will no longer be a date but a character.

看起来您似乎不想显示日期.您正在从函数中返回值，在这种情况下，我会坚持使用您所拥有的.从数据库中取出日期时，只需要将日期转换为适当的格式即可显示，请始终将其作为日期存储在数据库中.反过来，这意味着存储在数据库中的样子并不重要，只是它实际上是一个日期.

It doesn't look like you want to display a date as you've said. You're returning the value from your function, in which case I would stick with what you have. You only need to transform a date into an appropriate format for display when taking it out of the database, always store it as a date in the database. This in turn means that it doesn't matter what it looks like when stored in the database, merely that it is actually a date.

这篇关于将日期与预定义格式进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！