Converting number to date

[arodri]

Hello,

I'm not sure what the best way to go about doing this would be, so I was hopinh for some suggestions of syntax I could use to get the correct date. I have a date column with five digit numbers, the two last numbers represent the month, and the first three numbers represent the year, except they're missing a "0" after the first number, and all of the dates in the field are in the first day of that month.

For example, in the table I have: 21201
-Last two #'s are the month: 01 (January)
-First three are the year (w/ "0" missing after the first number): 212 (2012)
-The day is just the 1st.

Another example would be: 21112 (December 01, 2011)

I need to convert these numbers to their proper date because I'm using them in a nested query to compared to another table that actually has correct dates in it.

Any ideas?

Thanks!

 

[gmmastros]

What is the data type of your existing column? Is it integer or string (varchar)?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

If your data is an integer, then you should probably do math to make it look like a date. Something like this:

Select Convert(DateTime, Convert(VarChar(10), (YourColumn / 10000 * 100000 + YourColumn % 10000) * 100 + 1))
From YourTable

If you are storing your data as a string (varchar), then you should do string manipulation instead. Something like this:

Select Convert(DateTime, Stuff(YourColumn, 2, 0, '0') + '01')
From   YourTable

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[arodri]

It's coming from a view on a linked server that I don't have access to but I believe it's an integer

 

[arodri]

Thank you so much! I really appreciate it

 

[arodri]

What does the % do in the formula?

 

[gmmastros]

The percent symbol is the modulus operator. Basically it returns the remainder from doing integer division.

11 / 4 = 2 3/4

The modulus operator would return 3 in this case. Ex:

[tt]Select 11 % 4[/tt]



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[arodri]

That makes sense - thanks!

I have a test table in SQL that I've been using to test your formula and it works perfectly, but when I break down the math and try to do it manually, or in excel, I'm not getting the right number. Can you explain how the formula works, please?

I may be way off, but for the 21112 integer, the number before you convert to Varchar should be 20120101, right? I'm not sure if I'm doing the math in the right order but I get a number that seems way off.

Thanks so much!

 

[gmmastros]

The formula is:

[tt]Convert(DateTime, Convert(VarChar(10), (YourColumn / 10000 * 100000 + YourColumn % 10000) * 100 + 1))[/tt]

Let's remove the 2 converts:

[tt]
(YourColumn / 10000 * 100000 + YourColumn % 10000) * 100 + 1
[/tt]

The first step is to divide by 10,000. This is probably where you are having a problem in Excel. In SQL Server, when you divide 2 numbers, SQL Server will first look at the data types. SQL Server will "convert" the hard coded value 10,000 to an integer. If the data type for YourColumn is integer, then SQL server will do integer math. So:

21112 / 10000 = 2 (not 2.1112).
2 * 100000 = 200000.

The 2000000 is then added to YourColumn % 10000:
200000 + 1112 = 201112
This is multiplied by 100
20111200
This is added to 1.
20111201
When you convert this to a varchar and then a date, you end up with "2011-12-01 00:00:00.000"

If you want to reproduce this calculation in Excel, you will need to use the Floor function to get the same functionality as integer division, so:

=(FLOOR(A1/10000, 1) * 100000 + MOD(A1,10000)) * 100 +1

In this case, A1 would represent the cell with the value 21112.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[arodri]

21112 / 10000 = 2 (not 2.1112) --> That's the step that was throwing off the entire calculation for me - I was using 2.1112 instead of 2.

Thank you so much for explaining that!