date Conversion not working - 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 2005 Forums

Transact-SQL (2005)

date Conversion not working

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-05-23 : 09:58:51

Hey Guys, I can’t get this date conversion stuff to work. I have a variable that holds the current date -1 day (@Yesterday). The date that is on the table (DTE) is in the format yyyymmdd (i.e. 20021213), but I cannot get the date to convert.

I have tried:
BEGIN
DECLARE @Yesterday as nvarchar (8);

set @Yesterday = (replace(CONVERT(VarChar(10), DATEADD(d, -1, getdate()), 101), '/', ''))
PRINT @Yesterday

select * from tblxxx
where Replace(Convert(varchar (10), DTE, 101), '/', '') = @Yesterday
END

@Yesterday = 05222008
DTE = 20080522 (after conversion)

Why can’t I get DTE to convert to 05222008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 10:06:52

set @Yesterday = CONVERT(VarChar(8), DATEADD(d, -1, getdate()), 112)

select * from tblxxx
where DTE = @Yesterday

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

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2008-05-23 : 10:09:36

Well, your DTE is NOT a datetime, so your conversion routing is not doing anything: Prrof:

DECLARE @Input varchar(10)
DECLARE @Yesterday as nvarchar (8);
set @Input = '20080522'
set @Yesterday = (replace(CONVERT(VarChar(10), DATEADD(d, -1, getdate()), 101), '/', ''))
SELECT @Yesterday,
		replace(CONVERT(VarChar(10), @Input,101), '/', '')

RETURNS:
05222008 20080522

You can EITHER change your comparison on the column (not advised):


DECLARE @Input varchar(10)
DECLARE @Yesterday as nvarchar (8);
set @Input = '20080522'
set @Yesterday = (replace(CONVERT(VarChar(10), DATEADD(d, -1, getdate()), 101), '/', ''))
SELECT @Yesterday,
		replace(CONVERT(VarChar(10), convert(datetime,@Input),101), '/', '')

OR on the VARIABLE (advised):


DECLARE @Input varchar(10)
DECLARE @Yesterday as nvarchar (8);
set @Input = '20080522'
set @Yesterday = (replace(CONVERT(VarChar(10), DATEADD(d, -1, getdate()), 111), '/', ''))
SELECT @Yesterday, @Input

You'll need to add in your select form, I've just shown you variables for comparisons

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2008-05-23 : 10:10:57

Peso's is better - removes need for replaces...

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-23 : 10:11:16

The example above make it possible to utilize an present or existing index over DTE column.

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

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2008-05-23 : 10:18:49

quote:
Originally posted by Peso

The example above make it possible to utilize an present or existing index over DTE column.

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

Agreed, which was why I said the option using functions on the @INPUT (representing DTE) was NOT advised. Alround, your's is cleaner

*##* *##* *##* *##*
Chaos, Disorder and Panic ... my work is done here!

Trudye
Posting Yak Master

169 Posts

Posted - 2008-05-23 : 11:41:03

Thanx you to everyone who chimed in. Special thanx to Peso, your response was right on!

Have a great holdiay everyone, don'e eat too much (smile)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources