How can I select dates in SQL server to show?

Question

Is there a way for me to show dates using a select statement in sql dates from to To? Like if I select the date Jan. 15 2013 as from and Jan. 20, 2013 as To the query will show the following:

    DATE
 2013/01/15  12:00
 2013/01/16  12:00
 2013/01/16  12:00
 2013/01/17  12:00
 2013/01/18  12:00
 2013/01/19  12:00
 2013/01/20  12:00

Is this possible?

Possible dup of http://stackoverflow.com/questions/5125076/sql-query-to-select-dates-between-two-dates — zhongxiao37, Jun 09 '14 at 07:22

score 2 · Accepted Answer · answered Jun 09 '14 at 07:08

2

A better approach would be to write something as:

DECLARE @from DATE, @to DATE;
SELECT @from = '2013-01-15' , @to = '2013-01-20';

  SELECT DATEADD(DAY, n-1, @from)
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, @from, @to)+1) ROW_NUMBER() 
      OVER (ORDER BY s1.[object_id])
      FROM sys.all_objects AS s1
      CROSS JOIN sys.all_objects AS s2
  ) AS x(n);

Check Demo here.

answered Jun 09 '14 at 07:08

Deepshikha

9,896
2
21
21

I am not sure if he has the permission to access `sys` but it's good solution. +1 – zhongxiao37 Jun 09 '14 at 07:18

score 1 · Answer 2 · answered Jun 09 '14 at 06:58

1

Use BETWEEN...AND:

SELECT DateCol
FROM TableName
WHERE DateCol BETWEEN @FromDate AND @ToDate
ORDER BY DateCol

answered Jun 09 '14 at 06:58

Raging Bull

18,593
13
50
55

what if I don't have a column that have dates on it? Do it still possible for me to get dates? – Lucas Juan Jun 09 '14 at 07:05
1

It depends what data type in field. Ex. if you have varchar u can use convert to datetime and than use between as @raging said. http://technet.microsoft.com/pl-pl/library/ms187928%28v=sql.110%29.aspx – robertw Jun 09 '14 at 07:08
You can use cast function but dates should be in correct format . try select cast(data_col as datetime) from table_1 – Anupam Singh Jun 09 '14 at 07:09

score 1 · Answer 3 · answered Jun 09 '14 at 07:11

Next solution. I think more intuitive

declare @table table (d datetime)

declare @start datetime = '2014-01-01'
declare @stop datetime = '2014-01-21'

while @start <= @stop
begin
  insert into @table (d)
  values (@start)

  set @start = DATEADD(day, 1, @start)
end

select * from @table

How can I select dates in SQL server to show?

3 Answers3