date issue

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  October 27, 2010 at 5:16 am

  #142313

  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?

• steveb.

  SSC-Forever

  Points: 46733

  More actions

  October 27, 2010 at 6:22 am

  #1242809

  '' 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

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 27, 2010 at 6:24 am

  #1242811

  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!

• WayneS

  SSC Guru

  Points: 95461

  More actions

  October 27, 2010 at 8:16 am

  #1242916

  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

• BeginnerBug

  Hall of Fame

  Points: 3539

  More actions

  October 29, 2010 at 3:56 am

  #1244038

  thanks a lot for ur valuable ideas.