Missing Century in Date

  • 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.

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

    Results:

    Date

    ----------

    2029-07-26

  • Yes but the birthday is 1929. That is the problem.

  • 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[/url]

  • 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

  • Gents - the birthday is 1929 not 2029. I would prefer not to update the server because I don't now the impact.

  • 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.

    😎

  • 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?

  • 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

  • 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)

  • 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

  • I wish that was the case. We do our best to get a 4 digit (CCYY). It doesn't always work out like that.

  • 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

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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