Date stored as integer in data we have received

[addy]

Hi, I hope this is the correct forum to ask this question!

We have received a SQL backup file of data which we are transferring from an existing database into a new database.

There are several 'date' fields in the existing data which have the date stored as an integer. The format of this information is a 7-digit integer, with the first 4 digits being the year, and the remaining 3 digits being the number of days into that year. So, examples include:

1987278 is actually 05/10/1987 (October 5th is 278 days into 1987)
2004112 is actually 21/04/2004 (April 21st is 112 days into 2004)

etc. etc.

In the new database we are transferring this information into, we want these fields to be proper DateTime field types, so the above examples would be stored as the actual date.

How would I go about this?

Many Thanks in advance.

 

[nice95gle]

Use The DATEADD statment to and the number of days to the first day of the year

Ex:

declare @s datetime
set @s='1/1/1987'
select dateadd(dd,278,@s)

if you need more help on how to do it, let me know.

Well Done is better than well said
- Ben Franklin

 

[disord3r]

Logic would dictate that you would simply set the data type on that column within the destination table to match that of the origin, but I imagine you have already tried this?

I know from experience that the way data is actually stored in the database and the way it APPEARS to be stored in the database (via Management Studio or Query Analyzer, for example) are not always the same, so there may not actually be a "problem"; it might import just fine, even though it appears to be wrong.

Alternatively, depending on the format of the backup and how you are "restoring" this backup to your destination database, you may have an opportunity to do some scripting to convert that integer to a common date format before inserting. It's a fairly predictable format, and with some string functions or date functions, you should be able to get it into a text format which can be easily converted to a date format with the Convert() function.

 

[gmmastros]

You'll need to convert the number in to a date. I think the following formula should help you out.

Declare @D Int
Set @D = 1987278
Set @D = 2004112

Select DateAdd(Day, @D % 1000 - 1, DateAdd(Year, @D / 1000 - 1900, 0))

This is a rather complicated formula, so if you would like me to explain any part of it, just let me know.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

How do you store jan 1 2004?

200401 or 2004001?

here is mere mortal code that will work with both 20041 and 2004001

Declare @D Int
Set @D = 2004001
Set @D = 20041



select dateadd(dd,convert(int,right(@d,len(@d) -4))-1,convert(datetime,(left(@d,4) +'0101')))

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[addy]

Alternatively, depending on the format of the backup and how you are "restoring" this backup to your destination database, you may have an opportunity to do some scripting to convert that integer to a common date format before inserting.

We received a SQL backup file of the existing database which we have restored 'as is' to a database file.

We have a database application which this data needs to be integrated into, so there is a data mapping exercise invovled.

We have approximately 220,000 records in the backup we received and there are 6 or 7 fields which have dates stored in them in this format, so we are looking at changing approximately 1.2 to 1.5 million values.

This is a rather complicated formula, so if you would like me to explain any part of it, just let me know.

Thanks for the help - is there anyway of running this over a table of information, rather than inputting the values as you have done in your example?

 

[gmmastros]

This is a 1 time data load, right? If so, then performance is not critical. 220,000 records really isn't a lot. Given that, I would suggest you create a user defined function. It should take an integer parameter and return a DateTime value.

[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] dbo.ConvertToDate
	(@IntegerDate [COLOR=blue]Int[/color])
Returns [COLOR=#FF00FF]DateTime[/color]
[COLOR=blue]AS[/color]
  [COLOR=blue]Begin[/color]
    [COLOR=blue]Return[/color] [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Day[/color], @IntegerDate % 1000 - 1, [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Year[/color], @IntegerDate / 1000 - 1900, 0))
  [COLOR=blue]End[/color]

Now that it's a function, you can use it anywhere, and easily too.

Select Col1, Col2, [!]dbo.ConvertToDate(DateCol)[/!]
From   SomeTable

I would suggest that you modify the function so that it validates the input. For example... if there are invalid dates, it should not convert them. Ex: 2007[!]400[/!]

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[addy]

George

Thanks for the help so far - it's beginning to look promising!

I'll confess, I am a complete beginner when it comes to SQL programming. Virtually all database work I do is based on the data being in SQL, but I do any programming I need in Access (connected to the SQL tables).

So, given you are dealing with an amateur, I hope you will excuse the following (probably obvious or stupid) questions:

1) Will this function actually convert the data within the table into the format I require? i.e. where before I had a record with 1987278 in a field will I now have 05/10/1987?

2) "Select Col1, Col2, dbo.ConvertToDate(DateCol)
From SomeTable"

I'm a bit stumped on this - what is (DateCol)?

Apologies for all this - i'm out of my depth ;-)

 

[gmmastros]

Before I answer your question, I have one of my own.

You stated that there are several date columns in the database. What is the data type of these date columns? Is it integer, varchar, etc... If it's a varchar, what is the size?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[addy]

George

Looking at the table in design view in Enterprise Manager the data type is 'int' for all these fields.

Many thanks for your help (and patience!)

 

[gmmastros]

Well then, you have a problem. If the data type is int, you cannot store a date in that column. Therefore, you will not be able to update it the way you want.

I would suggest..

1. create a new column in the table with a DateTime data type.
2. copy the data from the integer column to this new column.
3. verify that the data is correct.
4. drop the integer column
5. rename the new column to be the same name as the original int column.

Most of this can be done in Enterprise Manager. In fact, everything except step 2, which is where the user defined function will be helpful. For step 2, you'll want to use Query Analyzer.

In Enterprise Manager, click tools -> 'SQL Query Analyzer'.
Make sure you are in the correct database.
Then, run the following query AFTER changing the table and column names.

Update [!]TableName[/!]
Set    [!]NewDateColumn[/!] = dbo.ConvertToDate([!]ExistingIntColumn[/!])

Does this make sense? If not, let me know and I will explain more.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[addy]

George

Thanks again for the help.

I have found a primitive fix for it - slightly similar to what you have suggested above.

I created a new field and gave it datetime data type. So, for example, my initial column is called Rev_Date, I have a new column called Rev_Date_New and I am using the following:

UPDATE TableName

SET Rev_Date_New = (SELECT dateadd(year, (Rev_Date/1000)-1900, 0) + (Rev_Date % 1000 - 1))

This seems to do the trick, but I am then going to have to run it for each new column, changing the names in my query each time.

I am sure your method is simpler and more effective but I am struggling a bit with it.

I don't quite understand this bit:

Update TableName
Set NewDateColumn = dbo.ConvertToDate(ExistingIntColumn)

If I have created a new column, dropped the old one and renamed my new column to the same name as the old one (steps 4 and 5 in your process above), why do I have two different variables - NewDateColumn and ExistingIntColumn. Are these now not one and the same?

 

[gmmastros]

Those column names were just examples (since you never stated what your actual column names are).

The process I described is the same as the process you are doing. The only difference is that I used the User Defined Function from a previous post, and you used the actual formula.

You query can be simplified a little. To this...

UPDATE TableName
SET Rev_Date_New = dateadd(year, (Rev_Date/1000)-1900, 0) + (Rev_Date % 1000 - 1)

It really doesn't make much difference though. And yeah... you're right, this is a bit time consuming and will have to be done for each column.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom