June 4, 2013 at 11:05 am
And the date column is showing up like this in Excel:
BDATE
380629
300517
670520
660402
470228
860421
550102
410322
491101
430612
Which, next to the Age column, makes sense that it's YY/MM/DD. So I run this:
select bdate, convert (datetime,convert(char(8),BDATE)) as [ConvDate], age
from mpw
And I get this:
bdate ConvDate age
3806292038-06-29 00:00:00.00075
3005172030-05-17 00:00:00.00083
6705201967-05-20 00:00:00.00046
6604021966-04-02 00:00:00.00047
4702282047-02-28 00:00:00.00066
8604211986-04-21 00:00:00.00027
5501021955-01-02 00:00:00.00058
4103222041-03-22 00:00:00.00072
4911012049-11-01 00:00:00.00064
4306122043-06-12 00:00:00.00070
Where it's reading some dates as being in the future, and some dates as being in the past. How do i Get it so all dates reflect the past?
Thanks
June 4, 2013 at 11:31 am
What is the 2 digit year cutoff in your server properties or sp_configure?
June 4, 2013 at 11:35 am
Temporary fix, right click on the server instance in Object Explorer, select properties. Go to the Advanced page and change Two Digit Year Cutoff from 2049 to 2020. Save the change by clicking OK. Run your query.
I recommend that you create a datetime column for this data and insert the converted data into that column for future use.
After you do that you can reset the value back to 2049.
June 4, 2013 at 11:53 am
Lynn Pettis (6/4/2013)
Temporary fix, right click on the server instance in Object Explorer, select properties. Go to the Advanced page and change Two Digit Year Cutoff from 2049 to 2020. Save the change by clicking OK. Run your query.I recommend that you create a datetime column for this data and insert the converted data into that column for future use.
After you do that you can reset the value back to 2049.
Thanks. That worked.
This is just a temp table that I quick loaded the file to, to mess with the date column. When changing the column format in Excel didn't budge it, I knew it was going to be trouble. Also, in between my post and your reply, I converted the column to datetime, and it had the same future date issue.
If it's too complicated to get into, just tell me, but: is there a way to convert and not have the dates jump ahead? I get that there's a longer query that could be written to correct it after the conversion didn't work. Just trying to wrap my head around the conversion itself.
June 5, 2013 at 12:53 pm
If you manually prepend the 19 or 20 to make it a 4 digit year you can set any cutoff. The code below makes it work setting the current year (2013) as the cutoff for 1900 versus 2000.
SELECT CONVERT(DATETIME,
CASE WHEN LEFT(mpw.BDATE, 2) >
RIGHT(CONVERT(CHAR(4), YEAR(GETDATE())),2)
THEN '19'
ELSE '20'
END + CONVERT(CHAR(8), BDATE))
from mpw
June 5, 2013 at 1:05 pm
pambrian (6/5/2013)
If you manually prepend the 19 or 20 to make it a 4 digit year you can set any cutoff. The code below makes it work setting the current year (2013) as the cutoff for 1900 versus 2000.
SELECT CONVERT(DATETIME,CASE WHEN LEFT(mpw.BDATE, 2) >
RIGHT(CONVERT(CHAR(4), YEAR(GETDATE())),2)
THEN '19'
ELSE '20'
END + CONVERT(CHAR(8), BDATE))
from mpw
Since it was all people of voting age, I did something similar and just added a 19 to the beginning of the string. Then, when the specs were finalized, they decided to just use the Age (in years) column, and not bring the DOB in. Heh.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply