Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Smile Unanswered: 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.

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Try this:
    DECLARE @DATE DATETIME

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


    Harshal.
    Last edited by harshal_in; 05-12-04 at 06:00.

  3. #3
    Join Date
    Apr 2004
    Posts
    49

    Smile

    Hi,

    Thanks For Your Timely Help,the Problem Got Sorted Out In A Jiffy.

  4. #4
    Join Date
    Apr 2004
    Location
    The Netherlands
    Posts
    29
    And what about this approach:

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

    Greetz,
    DePrins

  5. #5
    Join Date
    Apr 2004
    Posts
    49

    Cool

    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.

  6. #6
    Join Date
    Apr 2003
    Posts
    6

    Cool

    Quote Originally Posted by C_BHANDARY
    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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    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

  8. #8
    Join Date
    Apr 2004
    Posts
    49

    Smile

    Hi,

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

    Thanks Once Again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •