Declare date problem - 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

Declare date problem

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-13 : 07:08:24

Hey guys

Sorry to bother you today

i am having a complete nightmare in regards to query building today
i need help with a declare function

My table is as follows

Select
[Open_Date]
FROM [FDMS].[dbo].[Dim_Outlet]

my open date is displayed at the following within the dim outlet
20030521
20090709
20090709
20090709

My declare is

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(open_date))
from dbo.Dim_Outlet.open_date)

However i am getting the following error msg

Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Dim_Outlet.open_date'.

any ideas ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-13 : 07:17:51

quote:
Originally posted by masond

Hey guys

Sorry to bother you today

i am having a complete nightmare in regards to query building today
i need help with a declare function

My table is as follows

Select
[Open_Date]
FROM [FDMS].[dbo].[Dim_Outlet]

my open date is displayed at the following within the dim outlet
20030521
20090709
20090709
20090709

My declare is

Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(open_date))
from dbo.Dim_Outlet~~.open_date~~)

However i am getting the following error msg

Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.Dim_Outlet~~.open_date~~'.

any ideas ?

In FROM you need to give the table WITHOUT the column name.

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-13 : 07:27:26

Hi webfred

Such a simple error and it was confusing the hell out of me :)

Thank you for your support

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-13 : 07:48:05

Hi webfred

I need yoru assistance again

My opendate is displayed as YYYYMMDD

how can i change that to be displayed as YYYY-MM-DD ?

i know it be along the lines of

DateTime.ParseExact(dateString, "yyyyMMdd", null).ToString("yyyy-MM-dd");

but where would i put that in my declare statement ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-13 : 08:01:05

To give useful help we need to know first the datatype of that column (open_date).
Then we need to know where you want to display the result and if it is possible to do the formatting in the front-end-application.

Too old to Rock'n'Roll too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-13 : 08:02:31

DateTime.ParseExact(dateString, "yyyyMMdd", null).ToString("yyyy-MM-dd");

That isn't T-SQL - what is it? Java?

Too old to Rock'n'Roll too young to die.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-09-13 : 08:23:58

Whoops

I am getting confused myself

open_date is a varchar(8)
it is displayed as YYYYMMDD

my declare is
Declare @date varchar(10)
set @Date = (select dateadd(MM,-2,max(open_date))
from FDMS.dbo.Dim_Outlet

Would my declare work, if my declare is displayed as YYYYMMDD

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-09-14 : 14:03:09

Why don't you use it as a date instead of character?


Declare @MyDate date;
Select @MyDate = dateadd(month, -2, max(open_date))
From FDMS.dbo.Dim_Outlet;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources