[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

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

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?

Jim Horn
Jim Horn
1 Solution
Brian CroweCommented:
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
HuaMinChenBusiness AnalystCommented:
select cast(replace(replace('2014-06-30T11:00:00Z','T',' '),'Z','') as datetime)
Anthony PerkinsCommented:
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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Vikas GargBusiness Intelligence DeveloperCommented:

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
Anthony PerkinsCommented:
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.
Scott PletcherSenior DBACommented:
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', '')
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorAuthor Commented:
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now