Post reply

Bizarre behavior around a Varchar (19) - SSMS is treating it like a date

  • March 25, 2019 at 5:01 pm

    #2031616

    Hi All,

    I ran across a situation today I have never seen before.   I have been working on an XML import for a new product integration.  I have several fields that I need to import , store and use as varchar(19).  The problem is - SSMS keeps treating this field as a DateTime. I need to maintain this as a string for Auditing/comparing  purposes.  Whenever I grab, work with , export etc., this string is implicitly parsed to a DateTime format/datatype - or at least as far as I can tell.  (If it is just the editor, I need to change whatever option is doing this so I can display the data in the format as it was received, imported and stored.) 

     Has anyone seen this before?

    @@VERSION:  Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64)

    I receive the data in XML.  I use an Insert Into a solid table with the following method.

    Insert into dbo.[MyTable]  (TheStringDate)
        Select    x.MyDateColumn.value('(TheStringDate)[1]', 'varchar(19)')
           FROM @XmlFile.nodes('ROWSET/ROW') x(MyXMLFile) 

    [MyTable ].[TheStringDate]  is a varchar(19) 

    I have triple checked both the import script and the table.  I am quite puzzled by this behavior.

  • March 26, 2019 at 3:02 pm

    #2031831

    Why do you believe that SSMS is treating it as a date time?  I see no reason that it should.  It would help if you can give us some sample data to illustrate the issue.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • March 27, 2019 at 2:15 am

    #2031860

    You're explicitly defining the value from your xml as a varchar(19), and you're stating that the column is a varchar as well. Perhaps there is a trigger on your table dbo.MyTable that converts the string to a date? If your column is a string representation of a date (with a name like "TheStringDate" it suggests it is) why are you not storing the value as a date(time)? Storing dates as a string has many problems attached to it, and no benefits (formatting isn't a benefit, your presentation layer can do that better than your RDBMS).

    Like Drew said, otherwise an example that replicates the issue would be really useful here.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply