NULL datetime field. Is it possible?

Question

NULL datetime field. Is it possible?

SQLWannabe

SSCrazy

Points: 2842
More actions
July 1, 2013 at 12:24 pm

#295222

When I query a datetime field, the result is a date and time.
SELECT SentDate FROM MyTable.
Result = '2013-07-01 12:32:47.000', which is fine
When I use the following to return blank/NULL for all values:
SELECT '' AS SentDate FROM MyTable
Result = ''
The field then turns into a varchar field.
My real question...........Is there a way to not return the '1900-01-01 00:00:00.000' in the result and still make the field a datetime field?

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

SQLWannabe (7/1/2013)
When I query a datetime field, the result is a date and time.
SELECT SentDate FROM MyTable.
Result = '2013-07-01 12:32:47.000', which is fine
When I use the following to return blank/NULL for all values:
SELECT '' AS SentDate FROM MyTable
Result = ''
The field then turns into a varchar field.
My real question...........Is there a way to not return the '1900-01-01 00:00:00.000' in the result and still make the field a datetime field?

Your select statement is using the constant ''. This is a varchar datatype so that is what it will return. If you want to return a null as a datetime you will have to cast it.

select CAST(null as datetime) as SentDate

from MyTable

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steven Willis SSCrazy Eights Points: 9893 More actions · Answer 2

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[SentDate] DATETIME NULL,

PRIMARY KEY (ID))

INSERT INTO #TempTable

SELECT RandomDate

FROM (

SELECT TOP 100

RandomDate = DATEADD(second,ABS(CHECKSUM(NEWID()))%36000, DATEADD(day,ABS(CHECKSUM(NEWID()))%3653+36524,'1901-01-01 00:00:01'))

FROM Master.dbo.SysColumns t1

)d

-- Create some null and blank values just for testing

UPDATE #TempTable

SET SentDate = NULL

WHERE ID-7*(ID/7) = 0

UPDATE #TempTable

SET SentDate = ''

WHERE ID-9*(ID/9) = 0

--Returns nulls as null and blanks as '1900-01-01 00:00:00.000'

SELECT SentDate FROM #TempTable AS tt

--Returns nulls as null and blanks as '1900-01-01 00:00:00.000' (same result!)

SELECT CAST(SentDate AS DATETIME) FROM #TempTable AS tt

--Returns both nulls and blanks as null

SELECT NULLIF(SentDate,'') FROM #TempTable AS tt

--Or set the nulls to some other date

SELECT ISNULL(NULLIF(SentDate,''),'2025-01-01') FROM #TempTable AS tt

SQLWannabe SSCrazy Points: 2842 More actions · Answer 3

Sean,

Thanks for your Reply.

Can you picture me doing a face plant? I don't know how I didn't think of that within about 15 nanoseconds.

Sometimes I have that moment of non-clarity.

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

SQLWannabe (7/2/2013)
Sean,
Thanks for your Reply.
Can you picture me doing a face plant? I don't know how I didn't think of that within about 15 nanoseconds.
Sometimes I have that moment of non-clarity.

We all have those moments. 😛

Glad I was able to help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/