Date Format problem - 3years? - 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

General SQL Server Forums

New to SQL Server Programming

Date Format problem - 3years?

Author

Topic

dotman
Starting Member

5 Posts

Posted - 2009-05-20 : 11:47:11

Hi,

I am extracting data using a date and the following condition -

Date1 between 20080101 and 20080131

I want to use a second condition on a 2nd date (Date2)

I want to return records where Date2 is within 3 of Date1 (Date2 is always prior to Date1)

The format of both dates is yyyymmdd

Can anyone help?

Thanks

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-20 : 12:13:38

are you storing dates in a character type?

you really shouldn't do that -- use DATETIME instead! You've just come across 1 reason why not.

Luckily you are storing the dates in one of the ISO standard DATETIME character strings so you can do this:
-- up to 3 Either way before or after
SELECT
x
FROM
y
WHERE
ABS(DATEDIFF(DAY, CAST([date1] AS DATETIME), CAST([date2] AS DATETIME))) <= 3

-- up to 3 days before (note this will give you false results if date 2 is ever greater than date 1 but you've said that can't happen)
SELECT
x
FROM
y
WHERE
DATEDIFF(DAY, CAST([date2] AS DATETIME), CAST([date1] AS DATETIME)) <= 3

NB : Ignore the code I posted it is bullcrap! misread your post -- you need within 3 years? not days. will post soon.

But seriously -- you should make them DATETIME datatypes.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-20 : 12:27:57

Does this fit your bill?


DECLARE @foo TABLE (
	[ID] INT IDENTITY(1,1) PRIMARY KEY
	, [dateA] DATETIME
	, [dateB] DATETIME
	)

INSERT @foo ( [dateA], [dateB] )
      SELECT '20090101', '20090101'
UNION SELECT '20090101', '20081201'
UNION SELECT '20090101', '19650514'
UNION SELECT '20090101', '20000112'
UNION SELECT '20090101', '20070430'
UNION SELECT '20090101', '20040101'
UNION SELECT '20090101', '20060101'
UNION SELECT '20090101', '20051231'
UNION SELECT '20090101', '20060413'

SELECT * FROM @foo

-- 3 Years before (exclusive)
SELECT
	*
FROM
	@foo
WHERE
	[dateA] - [dateb] < CAST('19030102' AS DATETIME)

-- 3 Years before (Inclusive)
SELECT
	*
FROM
	@foo
WHERE
	[dateA] - [dateb] < CAST('19030103' AS DATETIME)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-20 : 12:58:17

Here is another way for grins:


SELECT 
	*
FROM 
	@Foo
WHERE 
	ABS(DATEDIFF(DAY, DateA, DateB)) <= 3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-20 : 13:12:25

quote:
Originally posted by dotman

Hi,

I am extracting data using a date and the following condition -

Date1 between 20080101 and 20080131

I want to use a second condition on a 2nd date (Date2)

I want to return records where Date2 is within 3 of Date1 (Date2 is always prior to Date1)

The format of both dates is yyyymmdd

Can anyone help?

Thanks


...
WHERE Date1 BETWEEN 20080101 and 20080131
AND Date2 > DATEADD(dd,DATEDIFF(dd,0,Date1)-3,0)

remember if you want to include records with date1 20080131 as well change end condition as 20080201 in between

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-21 : 04:12:51

Lamprey, visakh16

The title of this post is:
Date Format problem - 3years?

And as he didn't specify days in the post then I think he means within 3 years.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-21 : 04:14:12

but then again -- that doesn't seem to fit what OP said in his post.

Ho Hum,

Abandoning this as I don't know what's actually required.

Charlie.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

dotman
Starting Member

5 Posts

Posted - 2009-05-21 : 04:44:45

It is 3 years. (sorry me rushing again)

Thanks For your help, all solutions seem to work.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 03:28:46

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources