Buy Questpond's video subscriptions on huge discount.
DotNetFunda.com
Online: 5412
Home > Forums > Sql Server >

Month names as 1,2,...12

Posted by Suneel161 under Sql Server on 12/14/2010 | Points: 10 | Views : 2489 | Status : [Member] | Replies : 3
Write New Post | Search Forums | AnsweredResolved Posts | Un Answered Posts | Forums Home 
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



Reply | Reply with Attachment
Alert Moderator 

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

Latest Posts