date issue

  • declare @to_date datetime

    set @to_date= ' '

    print @to_date

    it will return 1st jan 1900....why?... any help?.. we cant assign this '' value to a datetime variable?

  • '' would be character data so it would not be compatiable with datetime,.

    SQL server uses 1 jan 1900 instead, various other systems use other dates to act as a 'placeholder' for missing dates

  • MonsterRocks (10/27/2010)


    declare @to_date datetime

    set @to_date= ' '

    print @to_date

    it will return 1st jan 1900....why?... any help?.. we cant assign this '' value to a datetime variable?

    sql will do an implicity conversion of empty string to zero...and zero is the integer value of the first date in SQL server: 1900-01-01 00:00:00.000

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • It sounds to me that what you want is a NULL. If so, just leave it undefined.

    Or, if the value is being passed in:

    declare @to_date datetime;

    set @to_date= NullIF(' ', ' ');

    print @to_date;

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • thanks a lot for ur valuable ideas.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply