Month names as 1,2,...12

Posted by Suneel161 under Sql Server on 12/14/2010 | Points: 10 | Views : 1301 | Status : [Member] | Replies : 3
In my data base i have a table with column date
In that i have some dates in a month.but i should display all days
when an entry is not done in particular date it should show null help me out




Responses

Posted by: T.saravanan on: 12/14/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi Suneel161,

Are you insert the record using Stored Procedure?
if yes means refer the following code....

In your Procedure check your Date Parameter is empty, to set that value is null.
Ex:
 

if (@date ='')
Begin
Set @date =null
End


In that case your date value pass null so in your table "null" value set in that empty date value.


Thanks,
T.Saravanan

Suneel161, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 12/14/2010 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi
Proceed the sample script given below

1. Preparing sample table & data
IF OBJECT_ID('Tb_Sample1','U') IS NULL

CREATE TABLE Tb_Sample1
(
ID INT IDENTITY(1,1),
DateColumn DATETIME
)
GO

TRUNCATE TABLE Tb_Sample1
GO

INSERT Tb_Sample1(DateColumn) VALUES('2010-12-01')
INSERT Tb_Sample1(DateColumn) VALUES('2010-12-02')
INSERT Tb_Sample1(DateColumn) VALUES('2010-12-05')
INSERT Tb_Sample1(DateColumn) VALUES('2010-12-07')
INSERT Tb_Sample1(DateColumn) VALUES('2010-12-08')
INSERT Tb_Sample1(DateColumn) VALUES('2010-12-09')
INSERT Tb_Sample1(DateColumn) VALUES('2010-12-11')
Now, we have we have entry for 1,2,5,7,8,9,11 date of December month. In your case, It should result actual data along with missing date entry as NULL.

2. Execute the script given below...
;WITH CTEs

AS
(
SELECT 1 AS 'Numbers'
UNION ALL
SELECT Numbers + 1 FROM CTEs WHERE Numbers<=30
)

SELECT A.DateColumn FROM CTEs C LEFT JOIN Tb_Sample1 a(NOLOCK)
ON (DAY(A.DateColumn) = C.Numbers)
The result would be as given below...
DateColumn

---------------------------
2010-12-01 00:00:00.000
2010-12-02 00:00:00.000
NULL
NULL
2010-12-05 00:00:00.000
NULL
2010-12-07 00:00:00.000
2010-12-08 00:00:00.000
2010-12-09 00:00:00.000
NULL
2010-12-11 00:00:00.000
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Note: we have used 31 days, But you have to include condition based on Months (28,30,31,...Etc)

Cheers

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Suneel161, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Karthikanbarasan on: 1/9/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
If u want to get the DAYS then customize the below code

SELECT datename(DW,nDays) TimelineDays, 

Convert(varchar(10), nDays, 101) TimelineDate,
ISNULL(SUM(Counter),0) Totals
FROM (Select GETDATE() AS nDays
union Select GETDATE()-1
union Select GETDATE()-2
union Select GETDATE()-3
union Select GETDATE()-4
union Select GETDATE()-5
union Select GETDATE()-6) AS tDays

Left Join (Select * From tHistory Where Account = 1000) AS History
on (DATEPART(year,nDays) + DATEPART(MONTH,nDays) + DATEPART(day,nDays)) =
(DATEPART(year,RecordDate) + DATEPART(MONTH,RecordDate) + DATEPART(day,RecordDate))
GROUP BY nDays
ORDER BY nDays DESC


Thanks
Karthik
www.f5Debug.net

Suneel161, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response