Changing the format for a date

Question

Post reply

Changing the format for a date

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
February 22, 2021 at 12:00 am

#3842699

Comments posted to this topic are about the item Changing the format for a date

Viewing 15 posts - 1 through 15 (of 15 total)

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

frederico_fonseca SSCoach Points: 16007 More actions · Answer 1

question is incorrect - but I guessed what you wanted.

ilyaioffe SSCrazy Points: 2284 More actions · Answer 2

ilyaioffe

SSCrazy

Points: 2284

February 22, 2021 at 1:17 am

#3848214

It should be:

select CONVERT(varchar(10), @d, 104)

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 3

DECLARE @d DATETIME = '2021/02/22'

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

That ain't going to work for all LANGUAGE 🙂

ilyaioffe wrote:

It should be:
select CONVERT(varchar(10), @d, 104)

Might be some debate about whether it should be CHAR ...

DECLARE @d DATETIME = '20210222'
SELECT CONVERT(CHAR(10), @d, 104)

eliassal Default port Points: 1467 More actions · Answer 4

eliassal

Default port

Points: 1467

February 22, 2021 at 7:46 am

#3848294

No response is correct, I agree with ilyaioffe

npapama1 SSC Rookie Points: 33 More actions · Answer 5

Good I' m not the only one that I noticed this 🙂

chris.mackenzie 149 SSC-Addicted Points: 449 More actions · Answer 6

Thank you people - thought I'd lost the plot for a minute there. Doesn't anyone review these questions before they are published?

At the end of most articles is a small blurb called a person's signature which exists to provide information about how to get in touch with the person posting, including their email address, phone number, address, or where they're located. Signatures have become the graffiti of computers. People put song lyrics, pictures, philosophical quotes, even advertisements in them. (Note, however, that advertising in your signature will more often than provoke negative responses until you take it out.)

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 7

chris.mackenzie 149 wrote:

Doesn't anyone review these questions before they are published?

Surely the point of questions like these is to deliberately raise issues that make people think and try things out and thereby increase their knowledge ... that's how I have learnt over the years

Toni-256719 SSCertifiable Points: 5812 More actions · Answer 8

If the question is wrong, how do you even pick an answer?!?

This reply was modified 3 years, 1 month ago by Toni-256719.

GaryV Hall of Fame Points: 3307 More actions · Answer 9

Given that CAST only has one parameter, I chose the CONVERT solution, even though once I tried it I found it doesn't give the desired result.

As others stated, the first parameter should be char(10) or varchar(10). In this instance using char or varchar without a length works as well, but we really shouldn't get into that bad habit.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 10

Toni-256719 wrote:

If the question is wrong, how do you even pick an answer?!?

I must be missing something because I cannot find fault with the question:

How can I get this in the format dd.mm.yyyy and returned to the client as a string?

The example data does not work for some LANGUAGE settings, but it is absolutely clear what the sample data is intended to be

"None of the above" was not an available answer ... but all the various proposed answers hint at what the correct solution is, so provide food-for-thought.

Toni-256719 SSCertifiable Points: 5812 More actions · Answer 11

You are correct. Sorry. I tried to remove my comment but was unable to.

This reply was modified 3 years, 1 month ago by Toni-256719.

Kristen-173977 SSCrazy Eights Points: 8687 More actions · Answer 12

GaryV wrote:

In this instance using ... varchar without a length works as well, but we really shouldn't get into that bad habit.

Definitely a bad habit 🙂 ... the resulting data without a SIZE is 30 characters. Of course that is big enough for a date formatted to be 10 characters 🙂 ... but ...

SELECT CONVERT(varchar, @d, 104)

... if that data is then further-processed the assigned width will be 30 char rather than 10. That sort of thing leads to bugs downstream which are then very hard-to-find.

varchar used, without size, in a DECLARE statement has size = 1 - although maybe that is likely to cause trouble immediately! and thus come to light, whereas a default CAST / CONVERT size of 30 for Date, Int, etc. is "big enough" to go unnoticed most of the time (but not GUID)

I think the fault here is that SQL doesn't have a "STRICT" parameter which would make it possible to "disallow" such shortcuts which are hangovers from legacy decades ago.

Here's another that I hate and would like to have the option of a STRICT setting to disallow:

SELECTCol1 AS MyCol1,
Col2 AS MyOtherColName,
Col3
Col4

largely because of the risk of accidentally typing that, the risk of which increases with very long lines, or multiple line statements, we use this style of formatting such that typo-errors are far more visible to the programmer

SELECTCol1 AS MyCol1
, Col2 AS MyOtherColName
, Col3
, Col4

Actually our preference (again because of the risk of an "AS" being at the end of a long / multi-line statement) is

SELECT[MyCol1] = Col1
, [MyOtherColName] = Col2
, Col3
, Col4

but of course code formatting is up to the user, all the formats are valid, my own advice would be "be 100% consistent"

Steve Jones - SSC Editor SSC Guru Points: 734418 More actions · Answer 13

Apologies. Editing the answer created a typo here. This has been corrected.

john-byrne SSCrazy Points: 2302 More actions · Answer 14

It is a much better practice to use FORMAT rather than CONVERT as you can specify the required format, i.e. 'dd.mm.yyyy' rather than some magic number that no one can remember what it converts too.