What's the SQL Server 2012 data type for '2014-06-30T11:00:00Z' ?

Posted on 2014-08-01
Last Modified: 2014-08-04
Hi guys

I'm working with a file that has dates formatted like '2014-06-30T11:00:00Z'.  
Is there a specific data type that holds these values, or do I just CAST it as a char, remove the T and Z, and cast that as a datetime?

Question by:Jim Horn
    LVL 34

    Expert Comment

    by:Brian Crowe
    That looks like a DATETIME to me.  I don't see a timezone offset so I would just use STUFF to replace the "T" with a space and REPLACE the "Z" with an empty string and CONVERT to DATETIME

    BTW the CONVERT style you would want to use would be 120
    LVL 10

    Expert Comment

    select cast(replace(replace('2014-06-30T11:00:00Z','T',' '),'Z','') as datetime)
    LVL 75

    Expert Comment

    by:Anthony Perkins
    That would be the datetimeoffset data type as it combines the date, time and zone offset.

    Incidentally the author is using the ISO 8601 date format which includes the time zone.
    LVL 14

    Expert Comment

    by:Vikas Garg

    no need to worry it will work as datetime format.

    You dont need to cast or convert .

    The same will be stored in sql as datetime
    LVL 75

    Accepted Solution

    no need to worry it will work as datetime format.
    The datetime data type does not support time zones.  So if they need this, as the example in the question clearly shows, then they need to use the datetimeoffset data type and not datetime.
    LVL 68

    Expert Comment

    If you want to reformat it, remove the dashes and Z, and replace the T with a space:

    SELECT REPLACE(REPLACE(REPLACE(datetimeoffset_string, '-', ''), 'T', ' '), 'Z', '')
    LVL 65

    Author Closing Comment

    by:Jim Horn
    Thanks Tony.

    All:  Tony's answer was the only one that defined the T and Z, which is what I was looking for, and not just to strip them and use a datetime.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Suggested Solutions

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now