在where子句中的Oracle日期比较 [英] Oracle date comparison in where clause
问题描述
例如,我有一个学生表,带有一个DOJ(加入日期)列,其类型现在设置为DATE,因为我以dd-mon-yy格式存储了记录.
For eg I have a student table with a DOJ(date of joining) column with its type set as DATE now in that I have stored records in dd-mon-yy format.
我在运行时有一个IN参数,日期以字符串形式传递,其格式为dd/mm/yyyy.如何比较和获取日期结果?
I have an IN param at runtime with date passed as string and its in dd/mm/yyyy format. How do I compare and fetch results on date?
我想获取每个数据库表学生的DOJ为25-AUG-92的学生的记录数,但是我以dd/mm/yyyy格式在IN参数中获取varchar的日期,请指导.
I want to fetch count of records of students who have DOJ of 25-AUG-92 per my database table student, but I am getting date as varchar in dd/mm/yyyy format in an IN param, kindly please guide.
我尝试了多个选项,例如trunc
,to_date
,to_char
,但不幸的是似乎没有任何作用.
I have tried multiple options such as trunc
, to_date
, to_char
but, unfortunately nothing seems to work.
推荐答案
我现在有一个学生表,带有一个DOJ(加入日期)列,其类型设置为
DATE
,因为我已经以dd-mon-yy
格式存储了记录.
I have a student table with a DOJ(date of joining) column with its type set as
DATE
now in that I have stored records indd-mon-yy
format.
不太完全,DATE
数据类型没有格式;它以 7字节(年为2字节,月,日,小时,分钟和秒均为1字节).您正在使用的用户界面(即SQL/PLUS,SQL Developer,Toad等)将处理DATE
的格式,从其二进制格式到人类可读的格式.在SQL/Plus(或SQL Developer)中,此格式基于 NLS_DATE_FORMAT
会话参数.
Not quite, the DATE
data-type does not have a format; it is stored internally in tables as 7-bytes (year is 2 bytes and month, day, hour, minute and second are 1-byte each). The user interface you are using (i.e. SQL/PLUS, SQL Developer, Toad, etc.) will handle the formatting of a DATE
from its binary format to a human readable format. In SQL/Plus (or SQL Developer) this format is based on the NLS_DATE_FORMAT
session parameter.
如果仅使用日,月和年输入DATE
,则时间部分(可能)将设置为00:00:00
(午夜).
If the DATE
is input using only the day, month and year then the time component is (probably) going to be set to 00:00:00
(midnight).
我在运行时有一个IN参数,日期以字符串或varchar的形式传递,其格式为
dd/mm/yyyy
.如何比较和获取日期结果??
I have an IN param at runtime with date passed as string or say varchar and its in
dd/mm/yyyy
format. How do I compare and fetch results on date.?
假设您的DOJ列的时间部分始终是午夜,那么:
Assuming the time component for you DOJ column is always midnight then:
SELECT COUNT(*)
FROM students
WHERE doj = TO_DATE( your_param, 'dd/mm/yyyy' )
如果不总是午夜,那么:
If it isn't always midnight then:
SELECT COUNT(*)
FROM students
WHERE TRUNC( doj ) = TO_DATE( your_param, 'dd/mm/yyyy' )
或:
SELECT COUNT(*)
FROM students
WHERE doj >= TO_DATE( your_param, 'dd/mm/yyyy' )
AND doj < TO_DATE( your_param, 'dd/mm/yyyy' ) + INTERVAL '1' DAY
这篇关于在where子句中的Oracle日期比较的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!