CODEWITHSUNDEEP
t-sqlsql-server-2008
Chitra
Chitra

Reputation: 21

How to get days dd in dd/mm/yyyy in a dateime column

I want to get all the dd in dd/mm/yyyy date column. How do I write the query for it? For Example: If I want dd between 28/06/2011 to 02/07/2011, then I should get the output like this:

28
29
30
01
02

Please help me through this

Upvotes: 2

Views: 293

Answers (5)

THEn
THEn

Reputation: 1938

I have something like that. you can use or create as many day period you can adding CROSS JOIN in CTE.

SET DATEFORMAT DMY

DECLARE @d1 datetime, @d2 datetime

SET @d1 = '28/06/2011'
SET @d2 = '02/07/2011';

WITH LIST(E) AS (
SELECT 1 E UNION ALL SELECT 2 E UNION ALL SELECT 3 E UNION ALL SELECT 3 E UNION ALL 
SELECT 4 E UNION ALL SELECT 6 E UNION ALL SELECT 6 E UNION ALL SELECT 7 E UNION ALL 
SELECT 8 E UNION ALL SELECT 9 E UNION ALL SELECT 10 E UNION ALL SELECT 12 E UNION ALL 
SELECT 12 E 
),

Numbers (number) AS (
SELECT ROW_NUMBER() OVER (ORDER BY L.E) Number FROM LIST L --CROSS JOIN LIST J
)

SELECT DATEPART(D,DATEADD(D,N.NUMBER-1,@D1)) FROM Numbers n WHERE DATEADD(D,N.NUMBER-1,@D1) <= @D2

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44316

Here is a solution using a tally table

DECLARE @StartDate date = '2011-06-28'
DECLARE @EndDate date = '2011-07-02'

SELECT right(convert(varchar, dateadd(day,number,@Startdate), 111), 2)
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND DATEDIFF(DAY, @StartDate, @EndDate)

You should be aware that this is limited to 2047 days. If you need more than that, you should consider recusive.

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @FromDate date = '20110628';
declare @ToDate date = '20110702';

with cte(TheDate) as
(
  select @FromDate
  union all
  select dateadd(d, 1, TheDate)
  from cte       
  where TheDate < @ToDate
)
select right('0'+cast(datepart(day, TheDate) as varchar(2)), 2) as TheDay
from cte
order by TheDate;

Upvotes: 2

sll
sll

Reputation: 62484

Use DATEPART(dd, dateColumnName)

DATEPART(dd, '28/06/2011') returns 28

More details on DATEPART

Upvotes: 0

Eslam Soliman
Eslam Soliman

Reputation: 1296

my friend here are a sql server function that can help you :

select datepart (dw, '5/11/7') as dayOfTheWeek,
       datename (dw, '5/11/7') as NameOfDay

that is for one date then you can make for loop on each date and store the result in data table

mark as answered if it helps :)

Upvotes: 0

Related Questions