Solved

Add serial date field, sql

Posted on 2014-10-02
4
242 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 142

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

896 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now