July 31, 2014 at 11:00 am
Does anyone have a script handy to fill in a century into a date string. Right now, I'm getting dates in the following format:
7/26/29 = converts to 2029.
I'm looking for a SQL statement that will now to put a 19 or 20 in the century.
July 31, 2014 at 11:08 am
select [Date] = convert(date,'7/26/29')
Results:
Date
----------
2029-07-26
July 31, 2014 at 11:11 am
Yes but the birthday is 1929. That is the problem.
July 31, 2014 at 11:11 am
You can configure your two digit year cutoff so that it displays the correct century. http://msdn.microsoft.com/en-us/library/ms191004.aspx
July 31, 2014 at 11:19 am
Further on Michael's excellent advice, the CONVERT function takes the third optional parameter [style], controls the style of the input/output.
😎
select [Date] = CONVERT(VARCHAR(10),convert(date,'7/26/29',1),101)
Results
07/26/2029
European
select [Date] = CONVERT(VARCHAR(10),convert(date,'7/26/29',1),103)
Result
26/07/2029
July 31, 2014 at 11:27 am
Gents - the birthday is 1929 not 2029. I would prefer not to update the server because I don't now the impact.
July 31, 2014 at 11:32 am
SQLSeTTeR (7/31/2014)
Gents - the birthday is 1929 not 2029. I would prefer not to update the server because I don't now the impact.
Sorry, didn't notice the century before, looks like something's wrong there, my settings where a ran the code is
configuration_idnamevalueminimummaximumvalue_in_usedescriptionis_dynamicis_advanced
1127two digit year cutoff2049175399992049two digit year cutoff11
, still 7/26/29 results in 7/26/2029.
😎
Run this code to check your settings
exec sp_configure 'show advanced options',1;RECONFIGURE;
select * from sys.configurations where name = 'two digit year cutoff';
exec sp_configure 'show advanced options',0;RECONFIGURE;
Edit: Ouch...:blush:
I'm being silly here, anything before the setting is interpreted as this century, anything after is last century.
You will have to assess the impact carefully before changing the settings.
😎
July 31, 2014 at 11:38 am
SQLSeTTeR (7/31/2014)
Does anyone have a script handy to fill in a century into a date string. Right now, I'm getting dates in the following format:7/26/29 = converts to 2029.
I'm looking for a SQL statement that will now to put a 19 or 20 in the century.
First, SQL Servers default configuration is that when given a 2 digit century anything less than 50 is considered 20+ and anything greater than or equal to 50 is 19+.
Showing us just the date 7/26/29 really tells us nothing. You later posts tell us this is a birthday, 7/26/1929. How were we to know?
Still, you have given use little information to go on to really help. There is no magical function to add 20 or 19 to a date. Without a consistent rule to write such a function again, not much we can do to help.
So what is the criteria for determining if the year belongs in the 1900's or 2000's?
July 31, 2014 at 11:48 am
Quick fix for your update
😎
declare @mycutoff date = '01/01/2020';
select [Date] = CONVERT(VARCHAR(10),CASE WHEN convert(datetime,'7/26/29',1) >= @mycutoff THEN DATEADD(YEAR,-100,convert(datetime,'7/26/29',1)) ELSE convert(datetime,'7/26/29',1) END,103)
Result
26/07/1929
July 31, 2014 at 1:32 pm
This is exactly what I needed. Thank you!
declare @mycutoff date = '01/01/2020';
select [Date] = CONVERT(VARCHAR(10),CASE WHEN convert(datetime,'7/26/29',1) >= @mycutoff THEN DATEADD(YEAR,-100,convert(datetime,'7/26/29',1)) ELSE convert(datetime,'7/26/29',1) END,103)
July 31, 2014 at 2:04 pm
I think the best solution is to enforce 4 digit year on the input and in dates coming into the system.
Sure somebody could not have been born yet in 2029. And if you know the column name is known to be of a specific date type (e.g. birthdate, employmentdate, deathdate, order date) then you can throw logic at it. But then you have further complications with maintaining that code and ensuring somebody doesn't change something on you.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 2:32 pm
I wish that was the case. We do our best to get a 4 digit (CCYY). It doesn't always work out like that.
July 31, 2014 at 2:35 pm
Nice answer Eirikur
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
July 31, 2014 at 5:38 pm
Agreed, nice code.
But if it's a birthday, wouldn't it be reasonable to use the current date + 1 as the cutoff, since there won't be future birthdates but someone could conceivably be 90+ years old?
declare @mycutoff date
set @mycutoff = dateadd(day, 1, getdate())
--yes, I know you can just use getdate()+1, but I prefer to explicitly use DATEADD
...
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 31, 2014 at 7:39 pm
Eirikur worked perfect. It did exactly what I needed it to do. Thanks again! 😀
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply