Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Missing Century in Date Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, July 31, 2014 11:00 AM
 SSC Veteran Group: General Forum Members Last Login: 2 days ago @ 2:52 PM Points: 227, Visits: 1,232
 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.
Post #1598390
 Posted Thursday, July 31, 2014 11:08 AM
 Hall of Fame Group: General Forum Members Last Login: Monday, April 20, 2015 1:28 AM Points: 3,158, Visits: 11,768
 `select [Date] = convert(date,'7/26/29')`Results:`Date----------2029-07-26`
Post #1598396
 Posted Thursday, July 31, 2014 11:11 AM
 SSC Veteran Group: General Forum Members Last Login: 2 days ago @ 2:52 PM Points: 227, Visits: 1,232
 Yes but the birthday is 1929. That is the problem.
Post #1598398
 Posted Thursday, July 31, 2014 11:11 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Thursday, January 15, 2015 10:36 AM Points: 598, Visits: 941
 You can configure your two digit year cutoff so that it displays the correct century. http://msdn.microsoft.com/en-us/library/ms191004.aspx Microsoft Certified Master - SQL Server 2008Follow me on twitter: @keith_tateForum Etiquette: How to post data/code on a forum to get the best help
Post #1598399
 Posted Thursday, July 31, 2014 11:19 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 10:24 AM Points: 3,833, Visits: 9,935
 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`
Post #1598401
 Posted Thursday, July 31, 2014 11:27 AM
 SSC Veteran Group: General Forum Members Last Login: 2 days ago @ 2:52 PM Points: 227, Visits: 1,232
 Gents - the birthday is 1929 not 2029. I would prefer not to update the server because I don't now the impact.
Post #1598403
 Posted Thursday, July 31, 2014 11:32 AM
 Hall of Fame Group: General Forum Members Last Login: Today @ 10:24 AM Points: 3,833, Visits: 9,935
 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_id name value minimum maximum value_in_use description is_dynamic is_advanced1127 two digit year cutoff 2049 1753 9999 2049 two digit year cutoff 1 1`, 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... 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.
Post #1598407
 Posted Thursday, July 31, 2014 11:38 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 10:37 AM Points: 21,537, Visits: 34,087
 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?
Post #1598411
 Posted Thursday, July 31, 2014 11:48 AM This worked for the OP
 Hall of Fame Group: General Forum Members Last Login: Today @ 10:24 AM Points: 3,833, Visits: 9,935
 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`
Post #1598416
 Posted Thursday, July 31, 2014 1:32 PM
 SSC Veteran Group: General Forum Members Last Login: 2 days ago @ 2:52 PM Points: 227, Visits: 1,232
 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)
Post #1598459

 Permissions