MSSQL 날짜 테이블 가져오기. 날짜 DataSet 가져오기.

기본적으로 SQL 에서 제공해주는 데이터로 우리는 원하는 기간동안의 날짜를 모두 출력 해 낼 수 있다. 

 

select convert(char(10),dateadd(증가시킬데이터(m:월, d:일, hh:시간, mm:분) ,number,'시작날짜'),120) as regdate
from master..spt_values with(nolock) 
where type = 'P' 
and convert(char(10),dateadd(d,number,'시작날짜'),120) < '마지막날짜'

이렇게 원하는 기간 내의 날짜를 출력 해 낼 수 있다.

 

 

ex)

select convert(char(10),dateadd(d,number,'2012-04-24'),120) as regdate
   from master..spt_values with(nolock) 
   where type = 'P' 
   and convert(char(10),dateadd(d,number,'2012-04-24'),120) < DateAdd("m", 1, '2012-04-24')  

 

 마지막 날짜를 mssql 에서 제공하는 DateAdd 함수를 이용해서 한달을 더해 출력 해 보았다

결과는 다음과 같다.

 

2012-04-24
2012-04-25
2012-04-26
2012-04-27
2012-04-28
2012-04-29
2012-04-30
2012-05-01
2012-05-02
2012-05-03
2012-05-04
2012-05-05
2012-05-06
2012-05-07
2012-05-08
2012-05-09
2012-05-10
2012-05-11
2012-05-12
2012-05-13
2012-05-14
2012-05-15
2012-05-16
2012-05-17
2012-05-18
2012-05-19
2012-05-20
2012-05-21
2012-05-22
2012-05-23 

 

 

이걸 이용하여 left조인 하여 원하는 날짜에 원하는 데이터를 추출 해 낼 수 있다.. 

 

보너스. 

활용을 조금 더 하자면 시간대 별 데이터 까지 출력이 가능하다. 

 

select right(convert(char(13),dateadd(hh,number,'시작날짜'),120),2) as regdate
from master..spt_values with(nolock) 
where type = 'P' 
and convert(char(10),dateadd(hh,number,'시작날짜'),120) < '마지막날짜' 

 

이렇게 해서 원하는 기간내에서 해당되는 날짜와 또한 그 날짜의 시간대 별 데이터까지  

group by 시킬 수가 있다. 

 

실전활용예제 )

 

select 
   regdate,
   isnull(tot,0),
   isnull(cnt,0)
from 
(
 select right(convert(char(13),dateadd(hh,1,'2012-04-24'),120),2) as regdate
 from master..spt_values with(nolock) 
 where type = 'P' 
 and convert(char(10),dateadd(hh,number,'2012-04-24'),120) < DateAdd(d, 1, '2012-04-24')
) as t1 
left join 
(
 SELECT right(CONVERT(char(13), d_regdate, 120),2) as 열1, sum(열1) as tot, count(열1) as cnt 
 FROM 데이터를 조회할 테이블
 WHERE convert(char(10),D_REGDATE,120) >= '2012-04-24'
 AND convert(char(10),D_REGDATE,120) <= '2012-04-24'
  group by right(CONVERT(char(13), d_regdate, 120),2)
) as t2
on t1.regdate = t2.d_regdate
group by t1.regdate, t2.d_regdate, tot,cnt
order by t1.regdate; 

 

난 하루치만 조회할 예정이었으므로 음하하핫. 

※이렇게 left join문을 써서 복잡하게 조회 한 까닭은 데이터를 페이지에 뿌려주는데, join을 하지 않고 그냥 쿼리문을 썼을 때 데이터가 없는 시간대에는 0을 해주고 싶은데, 데이터 자체가 없기에 그 시간대는 아예 결과에 나타나지 않았다.

예를 들면

01    1233      4

02    1234      2

04    3412      4

 

이런식으로 3시 대의 결과가 빠져서 된다.

이를 해결해 주기위해서 웹언어로 구현을 해도 되겠지만, 그렇게 되면 조회되는 데이터에 따라 달라지기에 소스가 복잡해 질 수 있기에, 모든 시간대를 결과로 출력하고 isnull(열이름,0) 을 통해

null일때는 0을 출력하게 만들어줌으로 결과가 없는 시간대에는

 

01 1233 4

02 1234 2

03    0  0

04 3412 4

  

이라는 결과를 얻을 수 있다. 

자세한 설명은 읽어보지 않았지만 써보면 master..spt_values 단순히 0, 1, 2, 3, 4,  쭉 증가하는 값을 가진 테이블이라고 보면 될 것 같다.

[출처] [mssql] 날짜 컬럼이 필요할 때, master..spt_values 응용|작성자 endterminal

[출처] MSSQL 날짜 출력, 시간대별 출력 |작성자 꾸앵