Problem in date..please help me

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 2000 Forums

SQL Server Development (2000)

Problem in date..please help me

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2008-05-13 : 00:33:58

Hello to All,

I have one query in stored procedure.

I want to retrieve number of month between two Date part Field.

First Date Part = there is one input field which having string data type and data can be entered like 01/01 or January 01(Date can be entered in any of these format) and there is one harcoded year part which is 2007(which will be reamin 2007 for first date part).

Second Date Part = there are two input field, in first input field data can be of 12/31 or December 31(Date can be entered in any of these format) and second input field that is year part..that is anything can enter i.e. 2007 or 2008 or 2009...any year part.

Now i want to compare both First Date Part and Second Date Part, to get the number of month difference between this two date part.

Please help me to resolve this issues.

Thanks,
ABHI

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 00:47:14

Check out DATEDIFF in SQL Server Books Online.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 00:49:00

Let @StartYear,@StartDate,@EndYear,@EndDate be the values passed from input field

DECLARE @StartYear varchar(4),@StartDate varchar(20),@EndYear varchar(4),@EndDate varchar(20)

SELECT DATEDIFF(mm,CAST(@StartYear + ' ' + @StartDate AS datetime),CAST(@EndYear + ' ' + @EndDate AS datetime))

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2008-05-13 : 00:57:17

Thanks for the reply...
but when i have used this logic

SELECT DATEDIFF(mm,CAST('2007' + ' ' + '01/01' AS datetime),CAST('2007' + ' ' + '12/31' AS datetime))

Error:-Conversion failed when converting datetime from character string.

The above code is only working, when i take January 01 in place of 01/01. Please look into this.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 01:02:52

Put a forward slash after 2007 as 2007 01/01 is not a valid date but 2007/01/01 is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 01:04:08

Ok. Then modify like this & try:-

DECLARE @Start nvarchar(10),@SDate nvarchar(20),@End nvarchar(10),@EDate nvarchar(20)
SELECT @Start=N'2007',@SDate='December 31',@End=N'2008',@EDate='April 12'
SELECT @SDate=@Start + CASE WHEN CHARINDEX('/',@SDate)>0 THEN  '/' ELSE ' ' END + @SDate,
@EDate=@End + CASE WHEN CHARINDEX('/',@EDate)>0 THEN  '/' ELSE ' ' END + @EDate

SELECT DATEDIFF(mm,@SDate,@EDate)

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2008-05-13 : 02:17:42

Thanks to all of you...
This is working now:-

SELECT DATEDIFF(mm,CAST('2007' + ' ' + '/' + IncYrBg AS datetime),CAST(IncYrEd + ' ' + '/'+IncYrEdDt AS datetime)) + 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 12:40:36

'2007/'

IncYrEd + '/'

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2008-05-16 : 04:48:38

In addition of this topic, iam still facing issues, when this code are tested in different type of data.

DECLARE @Start nvarchar(10),@SDate nvarchar(20),@End nvarchar(10),@EDate nvarchar(20)

SELECT @Start=N'2007',@SDate='12/31/',@End=N'2008',@EDate='04/12/'

SELECT @SDate=@Start + CASE WHEN CHARINDEX('/',@SDate)>0 THEN '/' ELSE ' ' END + @SDate,
@EDate=@End + CASE WHEN CHARINDEX('/',@EDate)>0 THEN '/' ELSE ' ' END + @EDate

SELECT DATEDIFF(mm,@SDate,@EDate)

This code is only working perfect, when i enter date like 12/31 and 04/12, if i enter 12/31/ or 04/12/ then it get failed and display following error:-

Error:-Arithmetic overflow error converting expression to data type datetime.

Please help me in respect of same, so that when i enter suppose 12/31/ then no error will come..its ok if calc not work..but it should not throw any type of error.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-16 : 11:16:40

Remove the second slash after the variables are set so that the calculations still work. You can use SUBSTRING or LEFT function to do this. It'll work in both cases of second slash existing or not.

SELECT @p1 = SUBSTRING(@p1, 1, 5)
or
SELECT @p1 = LEFT(@p1, 5)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources