Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Missing Century in Date Expand / Collapse
Author
Message
Posted Thursday, July 31, 2014 11:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:26 PM
Points: 219, Visits: 1,156
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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:37 PM
Points: 3,136, Visits: 11,488

select [Date] = convert(date,'7/26/29')


Results:
Date
----------
2029-07-26
Post #1598396
Posted Thursday, July 31, 2014 11:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:26 PM
Points: 219, Visits: 1,156
Yes but the birthday is 1929. That is the problem.
Post #1598398
Posted Thursday, July 31, 2014 11:11 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:45 PM
Points: 590, Visits: 909
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 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1598399
Posted Thursday, July 31, 2014 11:19 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:59 PM
Points: 1,933, Visits: 5,037
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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:26 PM
Points: 219, Visits: 1,156
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
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:59 PM
Points: 1,933, Visits: 5,037
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_advanced
1127 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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:17 PM
Points: 23,394, Visits: 32,212
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1598411
Posted Thursday, July 31, 2014 11:48 AM This worked for the OP Answer marked as solution
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:59 PM
Points: 1,933, Visits: 5,037
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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 5:26 PM
Points: 219, Visits: 1,156
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
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse