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
spudmccAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.