oracle中日期與字符串的相互轉(zhuǎn)化的方法詳解

更新時(shí)間：2024年09月24日 09:04:54 作者：知識(shí)分子_

Oracle數(shù)據(jù)庫(kù)系統(tǒng)是世界上最廣泛使用的數(shù)據(jù)庫(kù)管理系統(tǒng)之一,尤其在企業(yè)級(jí)應(yīng)用中占據(jù)主導(dǎo)地位,其中,日期函數(shù)是Oracle SQL中非常重要的組成部分,它們用于處理和操作日期和時(shí)間數(shù)據(jù),本文將給大家介紹oracle中日期與字符串的相互轉(zhuǎn)化的方法,需要的朋友可以參考下

1.字符串轉(zhuǎn)為日期格式（to_date）

例1：把字符串類型2005-01-01 13:14:20 轉(zhuǎn)成 2005/1/1 13:14:20日期格式

select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;

結(jié)果：

在這里插入圖片描述

例2：把字符串類型30-11月-19 轉(zhuǎn)成 2018/12/31 日期格式

select to_date('30-11月-19 ', 'dd-mon-yy') from dual

結(jié)果：

在這里插入圖片描述

注意：字符串轉(zhuǎn)日期時(shí)，字符串和日期格式要匹配，如字符串格式為30-11月-19，如果后邊跟yyyy-MM-dd就會(huì)報(bào)格式不匹配的錯(cuò)誤，必須使用dd-mon-yy

2.日期格式轉(zhuǎn)字符串（to_char）

例1：把sysdate（2020/5/12 17:31:23）轉(zhuǎn)化為yyyy-MM-dd HH24:mi:ss字符串格式

select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') from dual;

結(jié)果：

在這里插入圖片描述

例2：表car.lp_totallpdata中有一個(gè)字段enddate字段，代表了結(jié)束日期。enddate字段中的數(shù)據(jù)格式是varchar2類型的：（30-11月-19），現(xiàn)在要求查出表中結(jié)束日期等于字符串’2019-11’的數(shù)據(jù)

也就是說(shuō)圖片描也就是說(shuō)述

也就是說(shuō)找出enddate = ‘2019-11’的數(shù)據(jù)

分析：

首先30-11月-19 和 2019-12都屬于字符串類型的，但是他們的格式不一樣，我們可以先把enddate字段中的數(shù)據(jù)轉(zhuǎn)化為正常的日期格式，再把他轉(zhuǎn)化為字符串，看他與2019-12是否相等

1.先把enddate字段中的數(shù)據(jù)轉(zhuǎn)化為正常的日期格式

to_date(t.enddate, 'dd-mon-yy') //先轉(zhuǎn)化為日期30-11月-19==> 2019/11/30

2.再把他轉(zhuǎn)化為我們想要的字符串

to_char(to_date(t.enddate, 'dd-mon-yy'),'yyyy-mm') // 2019/11/30 ==> 2019-11

3.完整的過(guò)濾sql

select t.*
from car.lp_totallpdata  t
where to_char(to_date(t.enddate, 'dd-mon-yy'),'yyyy-mm')='2019-11'

3.日期范圍查詢

日期的范圍查詢，假設(shè)要查詢 2011-05-02 到 2011-05-30 之間的數(shù)據(jù)
這條查詢語(yǔ)句有兩種實(shí)現(xiàn)方式：

假設(shè)數(shù)據(jù)庫(kù)的字段 time 為日期類型，傳來(lái)的數(shù)據(jù)為字符串類型?。?！
1. to_date 方式

把傳來(lái)的數(shù)據(jù) 2011-05-02 、 2011-05-02 轉(zhuǎn)化為日期再與time比較

select * from tablename 
where time >= to_date('2011-05-02','yyyy-mm-dd')    
and  time <= to_date('2011-05-30','yyyy-mm-dd')

運(yùn)行的結(jié)果是：可以顯示05-02的數(shù)據(jù)，但是不能顯示05-30的數(shù)據(jù)。

解決方案：

①如果想顯示05-30的數(shù)據(jù)可以<to_date(‘2011-05-31’,‘yyyy-mm-dd’)，這樣就能顯示30號(hào)的了。

②如果想要顯示05-30的數(shù)據(jù)可以<=to_date(‘2011-05-30 23:59:59 999’,‘yyyy-mm-dd hh24:mi:ss’)也是可以查出來(lái)的。

2.to_char方式：

把time轉(zhuǎn)化為字符串再與傳來(lái)的數(shù)據(jù) 2011-05-02 、 2011-05-02 做比較

select * from tablename 
where to_char(time,'yyyy-mm-dd') >= '2011-05-02'    
and to_char(time,'yyyy-mm-dd') <= '2011-05-30'

查詢結(jié)果：可以同時(shí)顯示05-02和05-30的數(shù)據(jù)。

3. between … and

經(jīng)?？吹接腥嗽谀扯螘r(shí)間區(qū)間上喜歡用between … and … ，其實(shí)，可以直接地說(shuō)：這種用法是錯(cuò)誤的!

查看某一天的數(shù)據(jù)，或某一段時(shí)間內(nèi)的數(shù)據(jù)，其實(shí)是一個(gè)左閉、右開的區(qū)間內(nèi)的數(shù)據(jù)；
例如：我要查一張表 2011年3月11日到2011年3月24日內(nèi)所生成的數(shù)據(jù)，其區(qū)間應(yīng)該為[2011-03-11 00:00:00, 2011-03-25 00:00:00)
– 即：不包括右邊2011-03-25 00:00:00時(shí)間點(diǎn)的值!

4. 等于某日期的查詢

格式化傳入的日期字符串與數(shù)據(jù)庫(kù)比較
select * 
from goods
where g_time=to_date('2018/12/26 10:05:17','yyyy-MM-dd hh:mi:ss');

格式化數(shù)據(jù)庫(kù)的日期與傳入的日期字符串比較
select *  
from goods
where carnum = '粵BEK735' and 
to_char(damageStartdate, 'yyyy-MM-dd HH24:mi:ss') = '2017-04-05 12:00:00';

傳入值與數(shù)據(jù)值模糊匹配
select *  
from goods
where carnum = '粵BEK735' and 
to_char(damageStartdate, 'yyyy-MM-dd HH24:mi:ss')  like '2017-04-05%';

5. LocalDateTime的使用

        //轉(zhuǎn)換器
        DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        //當(dāng)前時(shí)間
        LocalDateTime time = LocalDateTime.now();

        //日期轉(zhuǎn)字符串
        String localTime = df.format(time);
        System.out.println("LocalDateTime轉(zhuǎn)成String類型的時(shí)間：" + localTime);

        //字符串轉(zhuǎn)日期
        LocalDateTime ldt = LocalDateTime.parse("2017-09-28 17:07:05", df);
        System.out.println("String類型的時(shí)間轉(zhuǎn)成LocalDateTime：" + ldt);
        //日期轉(zhuǎn)時(shí)間戳
        System.out.println("LocalDateTime：2017-09-28T17:07:05 轉(zhuǎn)時(shí)間戳："+ldt.toEpochSecond(ZoneOffset.of("+8")));


        System.out.println("===================================");

        //LocalDateTime把字符串轉(zhuǎn)日期互轉(zhuǎn) （不帶時(shí)、分、秒的）
        DateTimeFormatter dateTimeFormatter1 = DateTimeFormatter.ofPattern("yyyy/M/d");
        
        //字符串轉(zhuǎn)日期
        LocalDate parse = java.time.LocalDate.parse("2016/03/28", dateTimeFormatter1);
        System.out.println("2016/03/28轉(zhuǎn)成日期后：" + parse);

        //日期轉(zhuǎn)字符串
        String format = dateTimeFormatter1.format(parse);
        System.out.println("2016/03/28轉(zhuǎn)成合適的字符串后：" + format);

運(yùn)行結(jié)果如下：

在這里插入圖片描述

查看日期范圍間隔

  LocalDate today = LocalDate.now();
  System.out.println("Today：" + today);
  LocalDate oldDate = LocalDate.of(2018, 9, 23);
  System.out.println("OldDate：" + oldDate);

  Period p = Period.between(oldDate, today);
  System.out.printf("目標(biāo)日期距離今天的時(shí)間差：%d 年 %d 個(gè)月 %d 天\n", p.getYears(), p.getMonths(), p.getDays());

運(yùn)行結(jié)果

在這里插入圖片描述

案例：

“startTime”:“2021-10-08 13:21:08”,
“endTime”:“2021-10-08 13:21:12”,

求兩者的時(shí)間差：

   DateTimeFormatter df = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
   LocalDate ldate = LocalDateTime.parse(settlementDetailRequest.getStartTime(), df).toLocalDate();
   LocalDate rdate = LocalDateTime.parse(settlementDetailRequest.getEndTime(), df).toLocalDate();
   Period p = Period.between(ldate, rdate);

   if (p.getYears() > 1 || p.getMonths() > 3 ||(p.getMonths()==3&&p.getDays()>0)) {
       return BaseResp.fail("查詢?nèi)掌诜秶鷥H支持三個(gè)月！");
   }

需求案例：找出所有任務(wù)中，離當(dāng)前時(shí)間最近的任務(wù)信息，并返回！

    @RequestMapping(value = "/memory",method = RequestMethod.GET)
    @ApiOperation("修理廠錄入定損任務(wù)-記憶功能")
    @ApiImplicitParam(paramType = "query", name = "openid", value = "修理廠openid", required = true, dataType = "String")
    public R memory(@RequestParam("openid") String openid) {

        // 1.獲取該openid下的所有定損任務(wù)
        List<KfAppointmentDsEntity> memoryInfo = kfAppointmentDsService.list(new QueryWrapper<KfAppointmentDsEntity>().eq("factory_openid", openid));

        // 2.收集所有已提交的定損任務(wù)的-提交時(shí)間戳
        List<Long> collect = memoryInfo.stream()
                .map(KfAppointmentDsEntity::getApplyTime)
                .map(x -> {
                    DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
                    // 把Date格式轉(zhuǎn)化為時(shí)間戳
                    return LocalDateTime.parse(x, formatter).toEpochSecond(ZoneOffset.of("+8"));
                }).collect(Collectors.toList());

        // 3.通過(guò)比較時(shí)間戳大小，收集最近一次的提交時(shí)間戳
        Long maxTime = collect.get(0);
        for (int i = 0; i < collect.size(); i++) {
            if (maxTime < collect.get(i)) {
                maxTime = collect.get(i);
            }
        }

        // 4.收集最近一次提交時(shí)間的任務(wù)信息
        Long finalMaxTime = maxTime;
        List<KfAppointmentDsEntity> entitity = memoryInfo.stream().filter(x -> {
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
            Long everyTime = LocalDateTime.parse(x.getApplyTime(), formatter).toEpochSecond(ZoneOffset.of("+8"));
            return everyTime.equals(finalMaxTime);
        }).collect(Collectors.toList());

		// 5.屬性copy，并返回
        MemoryVo memoryVo = new MemoryVo();
        BeanUtils.copyProperties(entitity.get(0),memoryVo);
        return R.ok().put("data",memoryVo);
    }

6. between…and查詢?nèi)掌跁r(shí)存在的問(wèn)題

場(chǎng)景：用 select * from TABLE where date between ‘2009-1-22’ And ‘2009-1-22’ ，或者 select * from TABLE where date >= ‘2009-1-22’ And date <= ‘2009-1-22’ 查詢當(dāng)天數(shù)據(jù)，結(jié)果查不到數(shù)據(jù)。

原因：短日期類型默認(rèn)Time為00:00:00，所以當(dāng)使用between作限制條件時(shí)，就相當(dāng)于 between ‘2009-1-22 00:00:00’ and ‘2009-1-22 00:00:00’，因此就查不出數(shù)據(jù)。使用 >=、<= 時(shí)同理

解決方法：

方法一：

如果 2009-01-23 00:00:00 沒有業(yè)務(wù)發(fā)生的話，也可以讓前端直接加一天，寫成

select * from table where date between '2009-01-22' and '2009-01-23'

但這樣寫會(huì)包含 2009-01-23 00:00:00 ！
也可后端直接增加一天，后端解決代碼如下：

public class MianTest {
    public static void main(String[] args) {

        //轉(zhuǎn)換器
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //當(dāng)前日期
        Date date = new Date(2021-1900,1,28);
        System.out.println(format.format(date)+"+++++++++");
        
        Calendar instance = Calendar.getInstance();
        //把當(dāng)前日期放進(jìn)去
        instance.setTime(date);
        //日期向后推一天
        instance.add(instance.DATE,1);

        //這個(gè)日期就是日期向后推 1天 的結(jié)果
        Date realyTime = instance.getTime();
        System.out.println(format.format(realyTime)+"+++++++++");
    }
}

結(jié)果如下：

在這里插入圖片描述

方法二：

如果用String接收的日期，可以讓前端按如下方式傳數(shù)據(jù)

在這里插入圖片描述

我們?cè)诤笈_(tái)對(duì)endTime進(jìn)行修改，增加59:59:59

  //endTime增加一天
  String endTime = vo.getEndTime();
  String replaceTime = endTime.replace("00:00:00", "59:59:59");
  vo.setEndTime(replaceTime);

也可完成正確的查詢結(jié)果

方法三：

如果用Data接收的日期，可以讓前端還按這種方式傳數(shù)據(jù)

在這里插入圖片描述

后臺(tái)為Data類型的endTime增加59:59:59

public class MianTest {
    public static void main(String[] args) {

        //轉(zhuǎn)換器
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        //當(dāng)前日期
        Date date = new Date(2021-1900,1,8);
        System.out.println(format.format(date)+"+++++++++");

        Calendar instance = Calendar.getInstance();
        //把當(dāng)前日期放進(jìn)去
        instance.setTime(date);
        //日期向后推，整數(shù)往后推，負(fù)數(shù)向前推
        instance.add(Calendar.HOUR,23);
        instance.add(Calendar.MINUTE,59);
        instance.add(Calendar.SECOND,59);

        //這個(gè)日期就是日期向后推 23:59:59的結(jié)果
        Date realyTime = instance.getTime();
        System.out.println(format.format(realyTime)+"+++++++++");
    }
}

結(jié)果如下，也可達(dá)成正確的效果

在這里插入圖片描述