May 14, 2016 at 5:39 pm
In a t-sql 2012 statement where values are inserted into a table, the data is stored in the date column has a value 1900-01-01. I know this is the default value of a column that is setup at a date field. In this field I want the value of spaces actually stored in the field or possibly a null value. Thus can you tell me what I can do to store spaces in a field that is setup as a datetime field? If this is not possible what other datatype should I use to store the values as spaces.
I want this value to be a date field since I want to be able to sort the date value in month\dd\year format so the value sorts correctly in an ssrs 2012 report. Are there other ways data can be stored in a field where the data can be sorted in month\dd\year and the null fields do not contain the values looking like 1900-01-01? If so, can show me the sql on how to accomplish my goal?
May 14, 2016 at 6:47 pm
You can't store "spaces" in a date. Period. Dates are stored as numbers in T-SQL, and you can't store spaces in a numeric field.
May 14, 2016 at 6:49 pm
you may have two possible issues on your table
column may have a default other than null and if so that is what you are getting
whatever method you are using to insert data into that column is specifying a value of space or 0 which SQL Server converts by default to '1900-01-01'
so in order to have the columns set to a null value when you don't have a date you need to
1 - set default on that column to null (so that when you do a insert into the table without specifying the column it has a value (null) set)
2 - ensure that any insert/update to that column either specifies a null or a valid date (and no a space or 0 is not a valid date. darn SQL Server and its defaults)
May 15, 2016 at 12:17 am
And to the that other part of your question about alternative data types, don't. Use the appropriate date/time data type for the date/time column in your table. You will be glad you did.
May 15, 2016 at 9:52 am
wendy elizabeth (5/14/2016)
In a t-sql 2012 statement where values are inserted into a table, the data is stored in the date column has a value 1900-01-01. I know this is the default value of a column that is setup at a date field. In this field I want the value of spaces actually stored in the field or possibly a null value. Thus can you tell me what I can do to store spaces in a field that is setup as a datetime field? If this is not possible what other datatype should I use to store the values as spaces.I want this value to be a date field since I want to be able to sort the date value in month\dd\year format so the value sorts correctly in an ssrs 2012 report. Are there other ways data can be stored in a field where the data can be sorted in month\dd\year and the null fields do not contain the values looking like 1900-01-01? If so, can show me the sql on how to accomplish my goal?
The value of 1900-01-01 isn't a "default" for the DATETIME or any other temporal datatype. Someone may have declared that "0" is default for a column in a table, which translates to 1900-01-01, which is the "0" date for dates and times in SQL Server.
For a dozen different reasons, I strongly recommend that you leave whatever exists in the tables alone and deal with spaces and the like at the presentation level. If you were to post and example table and populate it with some example data and an example of the query you'd like to handle the 1900-01-01 date with, I'm sure we could help you in that area.
Please see the article at the first link under "Helpful Links" in my signature line below for the best way to get help for such questions.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply