Date vs Datetime recommendation

Question

Date vs Datetime recommendation

usererror

Ten Centuries

Points: 1148
More actions
August 3, 2022 at 5:09 pm

#4072104

I'm looking for some advice:
I'm taking in files that have birth dates, and other date fields that do not require any time portion. I was using datetime for all date related fields and tried to switch them all to DATE. When I then exported the data to Text files I noticed all the fields that did not have a date were switched to 1900-01-01. An example of this would be the Death Date field. I then learned DATE cannot have any blanks, they must be set to null to avoid the 1900 issue.
My questions are:
1. Would you stick with DateTime to avoid the 1900 issue and just format the date as needed for exporting purpose to only have the date (ex DOB)?
2. Or would you during the ETL process set any blank date fields to NULL to avoid the 1900 so you're only working with DATE.
Thanks!
-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.

Viewing 7 posts - 1 through 6 (of 6 total)

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

2. If the value is a date only, use a date data type, period.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Using the following code, please note that the DATE and DATETIME datatypes behave exactly the same way when it comes to blanks and NULLs. Run the following code and see.

 SELECT  CONVERT(DATETIME,' '), CONVERT(DATETIME,NULL)
        ,CONVERT(DATE    ,' '), CONVERT(DATE    ,NULL)
;

This is not a datatype issue. It's a data issue and may also have an additional issue with how the code that's exporting the data works on blank and NULL dates.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usererror Ten Centuries Points: 1148 More actions · Answer 3

Thanks, Jeff and Scott.

Jeff - I swear I had read somewhere else that the reason for the 1900 showing up on my exports was because of the DATE datatype. Your example clearly shows that is incorrect when I run your code on my server. I appreciate your guidance. I will go back and review my codes. I am sure now my problem is caused by one of the CONVERT queries during ETL. I import everything as varchar and then when inserting the data to its new table I am converting the text to dates when appropriate, so that is where my 1900 is coming from.

This reply was modified 2 years, 3 months ago by usererror.

-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 4

@usererror ,

I actually wouldn't be surprised that someone posted such a thing somewhere. 😀 That's why I posted the code to prove what it actually does. It was just easier than digging through the MS documentation to prove it.

And, thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

usererror Ten Centuries Points: 1148 More actions · Answer 5

@jeff Moden,

I updated my queries and now there are no 1900 dates for where death date does not exist. I added a new bit of code to the ETL queries to set all imported death dates that did not have a date to set those to NULL.

Thank you, again!

I also noticed an article emailed out about dates and times using FORMAT and why that should not be used. I can say I haven't tried doing that yet, and now I won't ever. (haha)

-Mark
MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

usererror wrote:

@Jeff Moden,
I updated my queries and now there are no 1900 dates for where death date does not exist. I added a new bit of code to the ETL queries to set all imported death dates that did not have a date to set those to NULL.
Thank you, again!
I also noticed an article emailed out about dates and times using FORMAT and why that should not be used. I can say I haven't tried doing that yet, and now I won't ever. (haha)

Awesome. Thank you for taking the time to post what you ended up doing. That'll help others that read this post.

Heh... and thanks for the feedback on the FORMAT article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)