Convert Dates for XML

Question

Convert Dates for XML

Steve Jones - SSC Editor

SSC Guru

Points: 735513
More actions
May 1, 2023 at 12:00 am

#4172410

Comments posted to this topic are about the item Convert Dates for XML

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004151 More actions · Answer 1

I know what you were looking for but there's actually no correct answer because of the VARCHAR(20) thing. The correct format of the correct answer is 23 characters long and that's what the VARCHAR needs to be if you want to include the milliseconds or only 19 if you do not. Using 20 will truncate the format and leave the decimal point as the last character.

As a bit of a sidebar, people tend to go a bit sideways when I tell them I use T-SQL for generate the HTML for my automatic emailed and nicely formatted "Morning Jobs Report" but they never seem to even raise a finger when it comes to formatting stuff for XML. 🙁

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ZZartin SSC-Dedicated Points: 30911 More actions · Answer 2

This entirely depends on what the system consuming the XML is expecting.

Steve Jones - SSC Editor SSC Guru Points: 735513 More actions · Answer 3

Corrected to varchar(30), and the target system always depends, but there is a format designed to convert cleanly to XML. Not all formats do this.

Jeffrey Williams SSC Guru Points: 90285 More actions · Answer 4

Steve Jones - SSC Editor wrote:

Corrected to varchar(30), and the target system always depends, but there is a format designed to convert cleanly to XML. Not all formats do this.

I find it interesting that people often use a VARCHAR sized larger than it ever needs to be to avoid truncation issues, instead of defining a CHAR at the exact length needed.

Why is VARCHAR(30) a better choice than CHAR(23)? The date format returned from CONVERT will always be 23 characters - it will never be less than 23 nor will it be more than 23 for the format specified.

Even when you want to truncate the milliseconds - it will always be 19 characters and will never vary from that length. So why use VARCHAR(19) vs CHAR(19)?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ZZartin SSC-Dedicated Points: 30911 More actions · Answer 5

Jeffrey Williams wrote:

Steve Jones - SSC Editor wrote:
Corrected to varchar(30), and the target system always depends, but there is a format designed to convert cleanly to XML. Not all formats do this.
I find it interesting that people often use a VARCHAR sized larger than it ever needs to be to avoid truncation issues, instead of defining a CHAR at the exact length needed.
Why is VARCHAR(30) a better choice than CHAR(23)? The date format returned from CONVERT will always be 23 characters - it will never be less than 23 nor will it be more than 23 for the format specified.
Even when you want to truncate the milliseconds - it will always be 19 characters and will never vary from that length. So why use VARCHAR(19) vs CHAR(19)?

Unless you're actually storing the result in a database field is there any performance difference between varchar or char? If you are just for example returning the result set or using it to generate an XML result?

Jeffrey Williams SSC Guru Points: 90285 More actions · Answer 6

ZZartin wrote:

Unless you're actually storing the result in a database field is there any performance difference between varchar or char? If you are just for example returning the result set or using it to generate an XML result?

Why would you code it differently because you are not storing the data? Is it somehow easier to use VARCHAR(23) instead of CHAR(23) because you are outputting the data to XML? What is the benefit of using varchar vs char when you know the data has a fixed length and will always have that fixed length?

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ZZartin SSC-Dedicated Points: 30911 More actions · Answer 7

Jeffrey Williams wrote:

ZZartin wrote:
Unless you're actually storing the result in a database field is there any performance difference between varchar or char? If you are just for example returning the result set or using it to generate an XML result?
Why would you code it differently because you are not storing the data? Is it somehow easier to use VARCHAR(23) instead of CHAR(23) because you are outputting the data to XML? What is the benefit of using varchar vs char when you know the data has a fixed length and will always have that fixed length?

Right that was precisely my point why would I code it as char(23) when normally I would just do varchar(30), like what the answer was actually changed to. Now sure if there's an explicit reason to use char(23) that's fine but that doesn't seem to be the case here. Which is why I asked if there was there was any value in using char(23) if you weren't storing it in the DB and it made sense to do that(like why are you just storing it as a date time).

This reply was modified 1 year, 4 months ago by ZZartin.