Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Add serial date field, sql

Hi Experts!

I am very new to SQL and honestly not very good at it yet.  I depend on the kindness of others sharing their knowledge like yourselves when faced with an issue.  

I need to add a field to a couple of tables in SQL that contain a standard date (date format) or they maybe VARCHAR format so we may need two different solutions.  What I need to do is to take that date field and add a new field that would provide the date in serial format.  (example:  10/2/2014 would be 41914)  Again, some tables have the date in a varchar format so I guess that really isn't a date, at least as far as SQL is concerned so this would have to be converted to a date first then to a serial date.  Other tables have the date in a DATE format already.  

Any help would be greatly appreciated.  

A
0
spudmcc
Asked:
spudmcc
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please read this article:
http://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
the date serial is just a datediff(day, date1, date2), so it depends on your "date 0" value.
0
 
PortletPaulCommented:
Well it is great that you already recognize that a string that looks like a date isn't the same as a date data type! Bravo.

Please note that in SQL Server the calendar commences on Jan 1, 1900 (1900-01-01)
and the zero in this DATEDIFF(day, 0 , some_date)
is 1900-01-01

Here's a query that may assist:
select
      date_date
    , datediff(day,0,date_date) -- the zero is 1900-01-01

    , date_literal
    , convert(date, date_literal ,101)
    , datediff(day,0, convert(date, date_literal ,101) )


from (
       select '10/2/2014' as date_literal, convert(date,'10/2/2014',101) as date_date
     ) sample

Open in new window

The result of that is:
|  DATE_DATE | COLUMN_1 | DATE_LITERAL |   COLUMN_3 | COLUMN_4 |
|------------|----------|--------------|------------|----------|
| 2014-10-02 |    41912 |    10/2/2014 | 2014-10-02 |    41912 |

Open in new window

You can see that working here: http://sqlfiddle.com/#!3/cfab1/84

Note that when converting strings to date (or datetime) it is often necessary to specify the "style"
That's the "101" parameter in this is

convert(date, date_literal ,101)

Here's a list of those style numbers.

And; in addition to Guy's good article, this article may be helpful:
The ultimate guide to the datetime datatypes
0
 
Scott PletcherSenior DBACommented:
Add a computed column to the table to dynamically generate the "serial date" value.  You can use that column just like any other column, but it's not physically stored, so you don't have to worry about keeping its value in sync with the corresponding date column.

For example:

ALTER TABLE table_name
ADD date1_serial AS DATEDIFF(DAY, -2, CAST(date1 AS datetime))

Using "-2" causes date '20141002' to return 41914, as you indicated it should.

Btw, that's the other huge advantage of a computed column.  If you need to adjust the calculation, you do it in one place only, and it automatically returns the corrected value everywhere it's used.
0
 
spudmccAuthor Commented:
FANTASTIC!  This worked beautifully.  Thank you so much for your time and knowledge.  I can't tell you how much I appreciate it.

a
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now