Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    10

    Unanswered: forcing data format mask without modifting code

    I have statement which is comparing a smalldatetime column to literal string as follows:

    sales_date ='21-9-2004 0:0:0.000'

    when I run the statement in query analyzer it bombs out with:

    Server: Msg 296, Level 16, State 3, Line 1
    The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

    If I alter the format of the date literal to '2004-09-21 00:00:00' the statement works.

    Is there anyway of forcing the statement to treat '21-9-2004 0:0:0.000' as '2004-09-21 00:00:00' without modifying the statement itself ?

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    There might be a global setting for how datetime fields are treated by default, I've never been tempted to go look for it. I'd rather do a CONVERT instead. There's also a 'SET DATEFORMAT' that might work for you.

    What's wrong with changing the statement?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Where is the data coming from? Is it always in that format? Can you use SUBSTRING?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    May 2004
    Posts
    10
    Unfortunately the data is in a liternal string 'DD-MM-YYYY' when in fact I require it be in 'MM-DD-YYYY' format.

  5. #5
    Join Date
    Sep 2004
    Location
    CA
    Posts
    41
    Couldn't you do something like...

    cast(day(sales_date()) as varchar(2)) + '-' +
    cast(month(sales_date()) as varchar(2)) + '-' +
    cast(year(sales_date()) as char(4)) + ' 0:0:0.000'

    ??
    Last edited by phikappa; 10-14-04 at 18:58.

Posting Permissions

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