Solved

Add serial date field, sql

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

758 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

23 Experts available now in Live!

Get 1:1 Help Now