Solved

Add serial date field, sql

Posted on 2014-10-02
4
250 Views
Last Modified: 2014-10-02
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
Comment
Question by:spudmcc
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40356747
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40356997
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40357513
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
 

Author Closing Comment

by:spudmcc
ID: 40358437
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question