T-sql 2012 date 1900-01-01

Transact-SQL
1

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?

2

You can't store space value in datatype datetime.
But you can store null in it (if you allow null).
In the "alter column" line, use the same datatype (datetime/datetime2).
Skip the entire "alter" section, if null is already allowed.

alter table yourtable
   alter column yourdatetimefield datetime null
;

update yourtable
   set yourdatetimefield=cast(null as datetime)
 where yourdatetimefield=cast('1900-01-01' as datetime)
;
1 Like
3

Curious why you use the CAST there? I would just write that as:

set yourdatetimefield = NULL

and I would use a string constant (without CAST) for the WHERE clause to, except I would use '19000101' (no punctuation) as being UNambiguous to SQL. I always wonder if that makes SQL work harder at Runtime, compared to Compile time though. Same sort of thing when I use a String GUID (without CAST)

1 Like
4

My mistake. I just have a habit of typecasting everywhere - in this case, it's (obviously) not needed. Thanks for correcting me :slight_smile:

Regarding punctuation in date, I read somewhere what the ISO standard is YYYY-MM-DD. If i were to port my queries to another db engine, I would hope to follow that particular db engines syntax, so this is why.

5

That wasn't my intention! There are plenty of situations where I CAST, but the implicit CAST would work just-fine, but I think that using CAST makes my intention clear to anyone else who will look at my code in the future.

Works fine for the newer DATE type (i.e. the DOCs say that it is unambiguous). However, for DateTime I think the DOCs say that you need 'yyyymmdd'. The question came up the other day and I tried various settings to see if I could get SQL to parse 'yyyy-mm-dd' (as a dateTIME, not as a DATE) "wrongly" and I didn't manage to do so, so maybe 'yyyy-mm-dd' is also UNambiguous for dateTIME?

For DateTime the ISO format 'yyyy-mm-ddThh:mm:ss' is definitely unambiguous

I don't know of any country / language that uses 'yyyy-DD-mm' :smiley: so maybe there is no situation where a Language or Locale setting could accidentally parse 'yyyy-mm-dd' wrongly.

SET DATEFORMAT YDM

can definitely change the casting of 'yyyy-mm-dd' To DateTime (but NOT that of 'yyyymmdd'), but that is so obviously a deliberate act that I'm not sure it is worth bothering about. I could not find a way to use DATEFORMAT to disrupt the Cast of 'yyyy-mm-dd' To Date datatype.


SET DATEFORMAT YDM
PRINT 'SET DATEFORMAT YDM'
-- DateTime CAST Tests:
SELECT	CAST('2016-01-02' AS DateTime) AS [2016-01-02-Wrong],
	CAST('20160102' AS DateTime) AS [20160102-ok],
	CAST('2016-01-02T01:02:03' AS DateTime) AS [2016-01-02T01:02:03-ok]

-- Date CAST Tests:
SELECT	CAST('2016/01/02' AS Date) AS [2016/01/02-ok],
	CAST('2016-01-02' AS Date) AS [2016-01-02-ok],
	CAST('20160102' AS Date) AS [20160102-ok],
	CAST('2016-01-02T01:02:03' AS Date) AS [2016-01-02T01:02:03-ok]

SET DATEFORMAT DMY
PRINT 'SET DATEFORMAT DMY'
SELECT	CAST('01/02/2016' AS Date) AS [01/02/2016-ok],
	CAST('01-02-2016' AS Date) AS [01-02-2016-ok]

SET DATEFORMAT MDY
PRINT 'SET DATEFORMAT MDY'
SELECT	CAST('02/01/2016' AS Date) AS [02/01/2016-ok],
	CAST('02-01-2016' AS Date) AS [02-01-2016-ok]

SET DATEFORMAT DMY	-- Reset this to YOUR default before doing anything else !!!
1 Like