date querry - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

date querry

Author

Topic

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-12-15 : 01:24:04

Hi All,

can any one help me to sort out this senario.

In my payroll table i have startdate and enddate of the salary for a company(ex: 27 and 26) ie., 27 of jan anf 26 of feb i have to genarate the dates for one year based on this startdate and end date.
like M1 27/01 to 26/02
M2 27/02 to 26/03
up to 12 months
and store in separate table.

can any one help me to solve this.

Note: both the start date and enddate are integers.

Thanks in Advance,
Kiran Murali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-15 : 03:04:31

what should be year selected? should date ranges be generated for current year?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stepson
Aged Yak Warrior

545 Posts

Posted - 2011-12-15 : 03:16:16

you can spt_values....

SELECT
number
,dateadd(m,number,'20110127') as data1
,dateadd(d,-1,dateadd(m,number+1,'20110127')) as data2
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 0 AND 11

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-12-16 : 00:09:11

quote:
Originally posted by visakh16

what should be year selected? should date ranges be generated for current year?

yes, its for current year.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-12-16 : 00:37:38

quote:
Originally posted by kiranmurali

quote:
Originally posted by visakh16

what should be year selected? should date ranges be generated for current year?

yes, its for current year.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Have you tried Stepson's code? It will work

Madhivanan

Failing to plan is Planning to fail

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-12-26 : 05:06:50

i have worked on the above posted querry but it will work only when the startdate is in double digit (i.e.,23 or etc if it is 1 or 2...etc means it is throwing error).

The error is :
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

thanks in advance
Kiran Murali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-26 : 05:46:36

quote:
Originally posted by kiranmurali

i have worked on the above posted querry but it will work only when the startdate is in double digit (i.e.,23 or etc if it is 1 or 2...etc means it is throwing error).

The error is :
Msg 241, Level 16, State 1, Line 7
Conversion failed when converting date and/or time from character string.

thanks in advance
Kiran Murali

show your used query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-12-26 : 08:31:29

this is the querry.

DECLARE @SALARYSTART INT
SELECT @SALARYSTART = SALARY_STARTDATE FROM PNET_PAYROLL_SALARYCONFIG WHERE SALARYCONFIDID=1

DECLARE @DATE1 VARCHAR(15)
SELECT @DATE1= CAST(YEAR(CONVERT(date, getdate())) AS VARCHAR(25))+CAST(MONTH(CONVERT(date, getdate())) AS VARCHAR(50))+CAST(@SALARYSTART AS VARCHAR(20))
SELECT
number
,CONVERT(DATE,dateadd(m,number,@DATE1)) as data1
,CONVERT(DATE,dateadd(d,-1,dateadd(m,number+1,@DATE1 ))) as data2
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 0 AND 11

am getting the salary start from salary config table based on the categoryid the salary start is integer.

Regards,
Kiran Murali

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-12-26 : 10:26:43

I didn't quite follow the logic you are trying to implement, however, I suspect the problem is that your date string comes out to be less than 8 characters long. You can fix that by doing the following:

DECLARE @SALARYSTART INT
SELECT @SALARYSTART = SALARY_STARTDATE FROM PNET_PAYROLL_SALARYCONFIG WHERE SALARYCONFIDID=1


DECLARE @DATE1 VARCHAR(15)
SELECT @DATE1= CAST(YEAR(CONVERT(date, getdate())) AS VARCHAR(25))
+RIGHT('0'+CAST(MONTH(CONVERT(date, getdate())) AS VARCHAR(50)),2)
+RIGHT('0'+CAST(@SALARYSTART AS VARCHAR(20)),2)
SELECT 
number
,CONVERT(DATE,dateadd(m,number,@DATE1)) as data1
,CONVERT(DATE,dateadd(d,-1,dateadd(m,number+1,@DATE1 ))) as data2
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 0 AND 11

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-12-27 : 00:59:17

Thanks a lot its working fine.

Regards,
Kiran Murali

quote:
Originally posted by sunitabeck

I didn't quite follow the logic you are trying to implement, however, I suspect the problem is that your date string comes out to be less than 8 characters long. You can fix that by doing the following:
DECLARE @SALARYSTART INT
SELECT @SALARYSTART = SALARY_STARTDATE FROM PNET_PAYROLL_SALARYCONFIG WHERE SALARYCONFIDID=1

DECLARE @DATE1 VARCHAR(15)
SELECT @DATE1= CAST(YEAR(CONVERT(date, getdate())) AS VARCHAR(25))
+RIGHT('0'+CAST(MONTH(CONVERT(date, getdate())) AS VARCHAR(50)),2)
+RIGHT('0'+CAST(@SALARYSTART AS VARCHAR(20)),2)
SELECT
number
,CONVERT(DATE,dateadd(m,number,@DATE1)) as data1
,CONVERT(DATE,dateadd(d,-1,dateadd(m,number+1,@DATE1 ))) as data2
FROM
master..spt_values
WHERE
Type = 'P' AND Number BETWEEN 0 AND 11

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-27 : 03:10:10

another way is to do like this


DECLARE @StartDay int,@startmonth int, @EndDay int,@EndMonth int

SELECT @StartDay =27,@startmonth =1, @EndDay =26,@EndMonth=2

;With Dates_Collection (Startdt,Enddt)
AS
(
SELECT DATEADD(dd,@startday-1,DATEADD(mm,@startmonth-1,DATEADD(yy,YEAR(GETDATE())-1900,0))),
DATEADD(dd,@endday-1,DATEADD(mm,@endmonth-1,DATEADD(yy,YEAR(GETDATE())-1900,0)))
UNION ALL
SELECT DATEADD(mm,1,Startdt),DATEADD(mm,1,Enddt)
FROM Dates_Collection 
WHERE DATEADD(mm,1,Enddt)<= DATEADD(yy,YEAR(GETDATE())-1899,0)
)
SELECT *
FROM Dates_Collection 
OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kiranmurali
Yak Posting Veteran

55 Posts

Posted - 2011-12-28 : 05:30:23

Thanks, its working.

Regards,
Kiran Murali

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-28 : 07:21:39

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources