Help With Variable Containing Datetime

HI,

I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.

DECLARE @.DATE NVARCHAR(100)
SET @.DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @.DATE)

WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.

BUT,

WHEN I RUN A VARIABLE CONTAINING DATETIME,

DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + @.DATE)

IT GIVES ME AN ERROR :-

"Line 1: Incorrect syntax near '12'. "

IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.Try this:
DECLARE @.DATE DATETIME

SET @.DATE = GETDATE()
EXEC ('SELECT ' + ''''+@.DATE+'''')

Harshal.|||Hi,

Thanks For Your Timely Help,the Problem Got Sorted Out In A Jiffy.|||And what about this approach:

declare @.date datetime
set @.date = getdate()
select @.date

Greetz,
DePrins
;)|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.

Try this:
DECLARE @.DATE DATETIME,
@.Dt_Dsc Varchar(50),
@.SQL varchar(200)

SET @.DATE = GETDATE()
Set @.Dt_Ddc = Replace(Cast(Left(@.Date , 11) AS varchar(50)),' ','_')
Set @.SQL = 'Select ' + @.Dt_Dsc

Exec (@.SQL)

Gil|||In order to prevent you from tearing out your hair later, I'd like to strongly suggest that you format your dates differently and use them as a prefix rather than a suffix on your table names. If you format the prefix as E20040720 instead of 20_july2004, you won't have problems with 12_dec2000 sorting between 04_jul2004 and 20_may2010! If you use a prefix instead of a suffix, all of your extract tables will sort together by date of extract when you display table names sorted alphabetically. You can use:DECLARE @.prefix VARCHAR(10)
SET @.prefix = 'E' + Replace(Convert(VARCHAR(10), GetDate(), 120), '-', '')Note that I added a letter before the digits, just to make it easier to work with the tables going forward. Sometimes it gets messy trying to cope with table names that start with a digit.

-PatP|||Hi,

Thanks Pat And Glubstein That Was Wonderful Solved Many Of My Problems And Saved Many Headaches.

Thanks Once Again