locked
Nvarchar to datetime format RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Dear experts,

    We have a nvarchar string - '1970-01-01 21:00:00:00' and we are storing this to a datetime format.

    Now the string is coming in YYYY-MM-DD format always , my question is : is it ever possible that the storage engine might change the order like YYYY-DD-MM while storing it in a datetime format ?

    Thanks,

    Abhisek

    Abhisek Mondal

    Tuesday, April 11, 2017 10:12 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Date time values stored in SQL Server don't have any format information, internally it is stored as a numeric value; it's always the client (frontend), who formats the date value.

    To convert a string to date time you should use the CONVERT function with Format Parameter.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by SaravanaC Tuesday, April 11, 2017 11:21 AM
    • Marked as answer by Abhisek Mondal Tuesday, April 11, 2017 1:56 PM
    Tuesday, April 11, 2017 10:24 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    By default SQL stores the datetime in YYYY-MM-DD

    Please mark as asnwer if this post helped you

    Tuesday, April 11, 2017 10:19 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Date time values stored in SQL Server don't have any format information, internally it is stored as a numeric value; it's always the client (frontend), who formats the date value.

    To convert a string to date time you should use the CONVERT function with Format Parameter.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by SaravanaC Tuesday, April 11, 2017 11:21 AM
    • Marked as answer by Abhisek Mondal Tuesday, April 11, 2017 1:56 PM
    Tuesday, April 11, 2017 10:24 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    I didn't know that the dates where stored in a numeric format. Is the same if you use date or datetime?
    Tuesday, April 11, 2017 10:28 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    As suggested by Olaf, datetime don't have any format information  it stores as int

    in this case assuming you are querying data using SSMS

    use below code to convert nvarchar to datetime in the given format

    select convert(datetime,'2017-04-11 16:52:52.103',121) -- yyyy-mm-dd hh:mi:ss.mmm(24h)



    Thanks Saravana Kumar C

    Tuesday, April 11, 2017 11:27 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Not exactly numeric, but rather a binary representation that has no meaning to the human eye. That goes for all types, with a possible exception for the string types (depemding on how you want to look at things..).

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, April 11, 2017 11:48 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks!

    Abhisek Mondal

    Tuesday, April 11, 2017 1:57 PM