0

I've just been setting my dates as VARCHARs instead of dates because they need to maintain a MMDDYYYY format and I was wondering it there was a better way to be doing this. The standard date seems to just do it as YYYYMMDD

My Code Made Me Suicidal
  • 514
  • 1
  • 6
  • 20
  • 1
    Why you're storing `DATE`s as strings in the first place? – Ilyes Sep 11 '19 at 18:28
  • Use the standard. Only convert to another format when you are outputting data. Databases provide lost of functionality for `date` data types. You should be taking advantage of that. – Gordon Linoff Sep 11 '19 at 18:28
  • Because I need them to stay in the MMDDYYYY format and the default date format stores them as YYYYMMDD. – My Code Made Me Suicidal Sep 11 '19 at 18:29
  • 3
    Wow.....date as a string. It's a very very very bad idea. – Eric Sep 11 '19 at 18:32
  • Your requirement isn't to `store` dates in a different format. They are stored as long integers. Your requirement is to `format` the dates upon presentation (on-screen, report, etc.). – HardCode Sep 11 '19 at 18:34
  • 1
    I believe you’re confusing output format with storage. A date is simply a number, stored in sql as an integer. How you choose to display the date in a query, for example, is entirely dependent upon what technology you’re using on the front end. – theMayer Sep 11 '19 at 18:35
  • Re-tagged with `sql-server` based on the accepted answer. –  Sep 11 '19 at 18:54
  • `DATE / DATETIME` is stored as binary data, and doesn't have a format, you can format the dates in the user interface instead, or by using `CONVERT() / FORMAT()` functions – Ilyes Sep 11 '19 at 18:57
  • Why do they have to be stored in MMDDYYYY format? that's not a date. It's a formatted string based on a date. The risk of storing in MMDDYYYY format as varchar is that you could allow bad dates in such as 13012019. The "Better" way is to store dates as dates, strings as strings and anything that needs to be summed or aggregrated or otherwise have math done on it as numeric. – xQbert Sep 11 '19 at 18:57

1 Answers1

3

Store dates as dates (or datetime) in the database. You should be formatting dates in the presentation layer (on-screen, reports, etc.). That is the point where you use your tools' formatting capabilities to display dates in the desired format. Remember, the dates in most DBMS are stored as long integers. You're query tools are actually formatting those values into a formatted date that is displayed.

Here is a quick query to display today as a numeric:

SELECT 'Yesterday', CAST(DATEADD(DAY, -1, @Today) AS DECIMAL(32,20))
UNION
SELECT 'Today', CAST(@Today AS DECIMAL(32,20))
UNION
SELECT 'Tomorrow', CAST(DATEADD(DAY, 1, @Today) AS DECIMAL(32,20))

Output is as follows (the fraction should represent the time element, if I'm correct):

Today       43717.61447052469200000000
Tomorrow    43718.61447052469200000000
Yesterday   43716.61447052469200000000

Here is a query-level method of converting the presentation of GETDATE() in SQL Server:

SELECT CONVERT(VARCHAR, GETDATE(), 110)

Output:

09-11-2019
HardCode
  • 6,497
  • 4
  • 31
  • 54
  • But is it possible to store them in another date format? – My Code Made Me Suicidal Sep 11 '19 at 18:31
  • The point is, you don't `need to`. You're eyes are being tricked by your query tools; dates are stored as long integers. You're query tool is `formatting` that long integer into a date format. You should do the same in your presentation layer. – HardCode Sep 11 '19 at 18:32
  • @SvetlinZarev "TIMESTAMP" has nothing to do with dates or times. And it's deprecated and should not be used. DATE, TIME, DATETIME, and DATETIME2 are the valid date/time formats. – pmbAustin Sep 11 '19 at 19:25
  • @pmbAustin You are correct, but I'm pretty sure the tag was not "sql-serevr" when I wrote my comment – Svetlin Zarev Sep 12 '19 at 04:05