using parameter and not using parameter , the result is different

Question

using parameter and not using parameter , the result is different

892717952

SSC Eights!

Points: 843
More actions
October 12, 2020 at 1:59 pm
Go to Answer
#3796351

declare @YYMMDD DATETIME
set @YYMMDD='2020-09-23 00:00:00.000'
select CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112) , CONVERT(VARCHAR(6), @YYMMDD, 112)
same value, when we use parameter to pass the value, and not using parameter, the result is not same, what is the reason ? thanks!
Ant-Green

SSC Guru

Points: 113507
More actions
October 12, 2020 at 2:40 pm
Answer
#3796385
Because your using string literals to represent a datetime format.
```
CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112)
```
The above is taking a string and converting it into a 6 character string.
Datetimes are represented as an integer value positive or minus the default date representing how much time has elapsed since the default, not strings.
If you convert the string to DATETIME, then to VARCHAR(6) you see you get the same as using the parameter
```
declare @YYMMDD DATETIME

set @YYMMDD='2020-09-23 00:00:00.000'

select
CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112) ,
CONVERT(VARCHAR(6), CONVERT(DATETIME,'2020-09-23 00:00:00.000'), 112) ,
CONVERT(VARCHAR(6), @YYMMDD, 112)
```

Viewing 6 posts - 1 through 5 (of 5 total)

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

892717952 SSC Eights! Points: 843 More actions · Answer 1

Ant-Green wrote:

Because your using string literals to represent a datetime format.
CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112)
The above is taking a string and converting it into a 6 character string.
Datetimes are represented as an integer value positive or minus the default date representing how much time has elapsed since the default, not strings.
If you convert the string to DATETIME, then to VARCHAR(6) you see you get the same as using the parameter
declare @YYMMDD DATETIME

set @YYMMDD='2020-09-23 00:00:00.000'

select
CONVERT(VARCHAR(6), '2020-09-23 00:00:00.000', 112) ,
CONVERT(VARCHAR(6), CONVERT(DATETIME,'2020-09-23 00:00:00.000'), 112) ,
CONVERT(VARCHAR(6), @YYMMDD, 112)

noted , thanks you for your great help!

frederico_fonseca SSCoach Points: 16154 More actions · Answer 2

and 112 is wrong format for that date representation.

112 = YYYYMMDD

121 = YYYY-MM-DD HH:MI:SS.mmm

o.schoen SSC Veteran Points: 211 More actions · Answer 3

And maybe for the task on hand the code 12 would be correct for ISO "yymmdd" format:

DECLARE @YYMMDD DATETIME
SET @YYMMDD='2020-09-23 00:00:00.000' /* converts literal string to DATETIME */
SELECT CONVERT(VARCHAR(6), @YYMMDD, 12) /* converts DATETIME back to VARCHAR */

This results in the '200923' output as probably intended.

892717952 SSC Eights! Points: 843 More actions · Answer 4

892717952

SSC Eights!

Points: 843

October 23, 2020 at 1:19 am

#3800280

thank you for your kind help!