Current Date > DateTo then DateTo

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

Transact-SQL (2000)

Current Date > DateTo then DateTo

Author

Topic

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-13 : 07:19:06

Hi, I need help please.

I have the following fields:
Date_From: 20080201
Date_To: 20080229
Date_Current: 13/03/2008 00:00:00

Now a format them, but my date to should be on a condition/Case.
when Date_Current-1 MORE than Date_To THEN Date_To ELSE Date_Current-1 but it does not work, it returns Date_Current-1.

Select Run_DateFrom
,case when replace(convert(varchar, (Run_Date_Current - 1), 104), '.', '/') > replace(convert(varchar, CONVERT(Datetime, CONVERT(char(10), Run_DateTo), 102), 104), '.', '/')
then replace(convert(varchar, CONVERT(Datetime, CONVERT(char(10), Run_DateTo), 102), 104), '.', '/')
else replace(convert(varchar, (Run_Date_Current - 1), 104), '.', '/')
end as Run_To
,Run_Date_Current

from dbo.MIS_System_Parameters where Run_Type = 'SELECTED'

Please Assist!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 08:14:43

Why, why, why, oh why, this casting and converting back and forth?

What are the datatypes for each and one column?

E 12°55'05.25"
N 56°04'39.16"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-13 : 08:23:02

Hi, Please help.

Date_From: 20080201 (int)
Date_To: 20080229 (int)
Date_Current: 13/03/2008 00:00:00 (datetime)

I want to display it like: 29/02/2008

I think the converting confuses the > operator

Please Assist!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 08:27:12

Always use proper DATETIME datatype to store data
Compare only DATEs and not VARCHARs
Convert them to proper dates and compare like

cast(col as datetime)>cast(col as date_from)

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-13 : 08:41:51

Help please - a bit lost

I tried the followingb but i get an arithmetic overflow error!
I can't really change the datatype as i did not define it & is being used by several apps. So i don't want ot change it.

select
cast(Date_Current as datetime)
,cast(Date_To as datetime)
,case when cast(Date_Current as datetime) > cast(Date_To as datetime)
then Date_Current else Date_To end as test

from dbo.MIS_System_Parameters
where Run_Type = 'SELECTED'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 08:50:30

[code]SELECT Date_Current,
CONVERT(DATETIME, CAST(Date_To AS CHAR(8)), 112) AS Date_To,
CASE
WHEN CONVERT(CHAR(8), Date_Current, 112) > CAST(Date_To AS CHAR(8)) THEN Date_Current
ELSE CONVERT(DATETIME, CAST(Date_To AS CHAR(8)), 112)
END AS Test
FROM dbo.MIS_System_Parameters
WHERE Run_Type = 'SELECTED'[/code]AND TO THE FINAL FORMATTING OD DATE IN YOUR FRONTEND.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-13 : 08:53:19

Try

select
cast(Date_Current as datetime)
,cast(cast(Date_To as varchar(8)) as datetime)
,case when cast(Date_Current as datetime) > cast(cast(Date_To as varchar(8)) as datetime)
then Date_Current else cast(cast(Date_To as varchar(8)) as datetime)
end as test from dbo.MIS_System_Parameters
where Run_Type = 'SELECTED'

Madhivanan

Failing to plan is Planning to fail

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-13 : 09:23:34

Thank You Very Much Peso & madhivanan.
I was really confused - Great stuff.
Really Appreciate the knowledge shared.

select
rtrim(Run_Period) as Period
,replace(convert(varchar, Date_From, 104), '.', '/') as Date_From
,replace(convert(varchar, Run_To, 104), '.', '/') as Date_To
,replace(convert(varchar, Date_Current, 104), '.', '/') as Run_To

from
(select
rtrim(Run_Period) as Period
,cast(cast(Date_From as varchar(8)) as datetime) as Date_From
,cast(cast(Date_To as varchar(8)) as datetime) as Date_To
,cast(Date_Current-1 as datetime) as Date_Current
,case when cast(Date_Current-1 as datetime) > cast(cast(Date_To as varchar(8)) as datetime)
then cast(cast(Date_To as varchar(8)) as datetime)
else cast(Date_Current-1 as datetime)
end as Run_To

from dbo.MIS_System_Parameters where Run_Type = 'SELECTED'
)v1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 09:34:00

You haven't read about the different styles yet in Books Online for CONVERT function?
There is already a Style 103, which is what you want and no need for converting to 103 and then replace.

SELECT	RTRIM(LTRIM(Run_Period)) AS Period,
	CONVERT(VARCHAR(10), CAST(CAST(Date_From AS CHAR(8)) AS DATETIME), 103) AS Date_From,
	CONVERT(VARCHAR(10), CAST(CAST(Date_To AS CHAR(8)) AS DATETIME), 103) AS Date_To,
	DATEADD(DAY, -1, Date_Current) AS Date_Current,
	CASE
		WHEN DATEADD(DAY, -1, Date_Current) > CAST(CAST(Date_To AS CHAR(8)) AS DATETIME) THEN CAST(CAST(Date_To AS CHAR(8)) AS DATETIME)
		ELSE DATEADD(DAY, -1, Date_Current)
	END AS Run_To
FROM	dbo.MIS_System_Parameters
WHERE	Run_Type = 'SELECTED'

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-13 : 09:47:18

Or this?

SELECT	d.Period,
	CONVERT(VARCHAR(10), d.Date_From, 104) AS Date_From,
	CONVERT(VARCHAR(10), d.Date_To, 104) AS Date_To,
	CASE
		WHEN d.Date_Current > d.Date_To THEN CONVERT(VARCHAR(10), d.Date_To, 104)
		ELSE CONVERT(VARCHAR(10), d.Date_Current, 104)
	END AS Run_To
FROM	(
		SELECT	LTRIM(RTRIM(Run_Period)) AS Period,
			DATEADD(MONTH, 12 * (Date_From / 10000) + (Date_From / 100) % 100 - 22801, (Date_From % 100) - 1) AS Date_From,
			DATEADD(MONTH, 12 * (Date_To / 10000) + (Date_To / 100) % 100 - 22801, (Date_To % 100) - 1) AS Date_To,
			DATEADD(DAY, -1, Date_Current) AS Date_Current
		FROM	dbo.MIS_System_Parameters
		WHERE	Run_Type = 'SELECTED'
	) AS d

E 12°55'05.25"
N 56°04'39.16"

ismailc
Constraint Violating Yak Guru

290 Posts

Posted - 2008-03-13 : 10:39:24

Thank You Peso

I have seen the styles but i'm not good at this data type conversions.
All i have is the style codes, your qry looks great but i'm afraid a bit confusing for me.

Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources