problem converting date on procedure.

Question

Post reply

problem converting date on procedure.

Viewing 15 posts - 16 through 30 (of 36 total)

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

astrid 69000 Hall of Fame Points: 3208 More actions · Answer 1

shame on me shame on me, my problem was a math problem, i didnt write the the date i wanted to get :doze:

thanks everyone for all the help!!!

Sergiy SSC Guru Points: 110208 More actions · Answer 2

drew.allen (6/29/2016)
Sergiy (6/28/2016)
Your problem is here:
' where RequestDate >= ''' + CAST(@RequestDate as nvarchar(23)) + ''''
1. Never use CAST is SQL, especially for date-time related data types.
I disagree. CAST is ANSI standard, whereas CONVERT is T-SQL specific.

So, it makes perfect sense to use T-SQL specific CONVERT when you write T-SQL code and ANSI standard CAST when you write ANSI code.

🙂

I always use CAST unless I need to deal with a specific format that is different from the ANSI standard formats (especially for dates).
Drew

Simple test.

I run the following script on one of my SQL Server instances:

DECLARE @TheDate DATETIME

SET @TheDate = '20160630'

SELECT cast (@TheDate AS NVARCHAR(20))

Now tell me - what is the output I see on my screen?

Can you?

What is the point of using a function which returns unpredictable results?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 3

DECLARE @TheDateTime DATETIME

SET @TheDateTime = '20160630 20:21:22'

SELECT CASE WHEN @TheDateTime = CAST(CAST (@TheDateTime AS NVARCHAR(50)) AS datetime) THEN 'All Correct' ELSE 'Fail!' END [Cast performance],

CASE WHEN @TheDateTime = CONVERT(DATETIME, CONVERT (NVARCHAR(50), @TheDateTime, 109), 109) THEN 'All Correct' ELSE 'Fail!' END [Convert performance]

_____________
Code for TallyGenerator

drew.allen SSC Guru Points: 76997 More actions · Answer 4

Sergiy (6/29/2016)
drew.allen (6/29/2016)
Sergiy (6/28/2016)
Your problem is here:
' where RequestDate >= ''' + CAST(@RequestDate as nvarchar(23)) + ''''
1. Never use CAST is SQL, especially for date-time related data types.
I disagree. CAST is ANSI standard, whereas CONVERT is T-SQL specific.
So, it makes perfect sense to use T-SQL specific CONVERT when you write T-SQL code and ANSI standard CAST when you write ANSI code.
🙂
I always use CAST unless I need to deal with a specific format that is different from the ANSI standard formats (especially for dates).
Drew
Simple test.
I run the following script on one of my SQL Server instances:
DECLARE @TheDate DATETIME
SET @TheDate = '20160630'
SELECT cast (@TheDate AS NVARCHAR(20))
Now tell me - what is the output I see on my screen?
Can you?
What is the point of using a function which returns unpredictable results?

I've already acknowledged that datetime data was an exception. That does not invalidate the fact that CAST works perfectly well with most other data types and you have not provided any rationale for using CONVERT in those cases.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Sergiy SSC Guru Points: 110208 More actions · Answer 5

drew.allen (7/6/2016)
I've already acknowledged that datetime data was an exception.

There are more.

You can easily filnd them if you look up for the article "CASR and CONVERT" in BOL.

That does not invalidate the fact that CAST works perfectly well with most other data types and you have not provided any rationale for using CONVERT in those cases.
Drew

I actually did.

Failure of a tool in some cases would be a good reason to ditch that tool for any professional, if there is another tool available which does not fail in those and all other cases managed but that first tool.

It's actually another way around - it's you who did not provide any rationale for using CAST rather than CONVERT in any cases.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Sergiy (7/6/2016)
drew.allen (7/6/2016)
I've already acknowledged that datetime data was an exception.
There are more.
You can easily filnd them if you look up for the article "CASR and CONVERT" in BOL.
That does not invalidate the fact that CAST works perfectly well with most other data types and you have not provided any rationale for using CONVERT in those cases.
Drew
I actually did.
Failure of a tool in some cases would be a good reason to ditch that tool for any professional, if there is another tool available which does not fail in those and all other cases managed but that first tool.
It's actually another way around - it's you who did not provide any rationale for using CAST rather than CONVERT in any cases.

How about a page from Mr. Celko's book (none of which I have or will read), because CAST is the ANSI standard and CONVERT isn't.

You should always write standard SQL according to Mr. Celko.

Sergiy SSC Guru Points: 110208 More actions · Answer 7

Lynn Pettis (7/6/2016)
How about a page from Mr. Celko's book (none of which I have or will read), because CAST is the ANSI standard and CONVERT isn't.
You should always write standard SQL according to Mr. Celko.

Well, Mr. Celko has his right to have an opinion.

As well as me or you.

Or people working for ANSI.

Any of those opinions are not rationales by themselves.

Rationales are based on some other, more solid grounds.

And can you please remind me what ANSI stands for?

Especially 1st 2 letters?

They clearly say that the standard is totally irrelevant to me or any other one who does not reside in USA or carry an American passport.

American standard says to put month in front of date.

So what?

Who follows that standard apart from Americans themselves?

So, following an ANSI standard can hardly be referred as any kind of rationale.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Sergiy (7/6/2016)
Lynn Pettis (7/6/2016)
How about a page from Mr. Celko's book (none of which I have or will read), because CAST is the ANSI standard and CONVERT isn't.
You should always write standard SQL according to Mr. Celko.
Well, Mr. Celko has his right to have an opinion.
As well as me or you.
Or people working for ANSI.
Any of those opinions are not rationales by themselves.
Rationales are based on some other, more solid grounds.
And can you please remind me what ANSI stands for?
Especially 1st 2 letters?
They clearly say that the standard is totally irrelevant to me or any other one who does not reside in USA or carry an American passport.
American standard says to put month in front of date.
So what?
Who follows that standard apart from Americans themselves?
So, following an ANSI standard can hardly be referred as any kind of rationale.

Really? You couldn't see the sarcasm just dripping from my statements? Sad, just sad.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 9

Lynn Pettis (7/6/2016)
Sergiy (7/6/2016)
Lynn Pettis (7/6/2016)
How about a page from Mr. Celko's book (none of which I have or will read), because CAST is the ANSI standard and CONVERT isn't.
You should always write standard SQL according to Mr. Celko.
Well, Mr. Celko has his right to have an opinion.
As well as me or you.
Or people working for ANSI.
Any of those opinions are not rationales by themselves.
Rationales are based on some other, more solid grounds.
And can you please remind me what ANSI stands for?
Especially 1st 2 letters?
They clearly say that the standard is totally irrelevant to me or any other one who does not reside in USA or carry an American passport.
American standard says to put month in front of date.
So what?
Who follows that standard apart from Americans themselves?
So, following an ANSI standard can hardly be referred as any kind of rationale.
Really? You couldn't see the sarcasm just dripping from my statements? Sad, just sad.

Nah... not sad at all. You were using ANSI Standard Sarcasm with an esoteric kicker. Easy for many to miss. 😀

--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)

Sergiy SSC Guru Points: 110208 More actions · Answer 10

Lynn Pettis (7/6/2016)
Really? You couldn't see the sarcasm just dripping from my statements? Sad, just sad.

Well, would not say "dripping".

none of which I have or will read

- nah, rather not dripping. 🙂

Sorry, apparently my English skills happened to be not fine enough for my attempt to play along.

_____________
Code for TallyGenerator

drew.allen SSC Guru Points: 76997 More actions · Answer 11

Sergiy (7/6/2016)
It's actually another way around - it's you who did not provide any rationale for using CAST rather than CONVERT in any cases.

Actually, I did provide my rationale, but I'll repeat it for you. CAST is ANSI standard, CONVERT is T-SQL specific. I tend to use the ANSI standard in cases where the results are the same.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 12

Just my humble opinion... ignore it if you want but it's worked wonders for me...

The ANSI and ISO Standards quickly limit what you can actually do. For example, write any stored procedure in T-SQL that accepts parameters. Promote it to an Oracle database. It won't do the job for two reasons... the variable naming convention is different and the method for returning a result set to the GUI is quite different.

Then there's garbage like the relatively new "FORMAT" function, which has been tested to be 44 times slower than CONVERT and sometimes slower.

The "need" for ANSI and ISO compliant code is supported by the myth that code written to such standards is portable. And, is Hekaton or File Stream ANSI / ISO compliant? How about BULK INSERT?

Such standards make great guidelines but because of the myth of portability and the fact that they don't include some of the wonderfully powerful extensions that the various engines have, they tend to limit what can be done in code and tend to limit performance, sometimes to an extreme degree. My recommendation would be to use all the goodies of any engine and employ proper encapsulation instead.

--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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Jeff Moden (7/7/2016)
Just my humble opinion... ignore it if you want but it's worked wonders for me...
The ANSI and ISO Standards quickly limit what you can actually do. For example, write any stored procedure in T-SQL that accepts parameters. Promote it to an Oracle database. It won't do the job for two reasons... the variable naming convention is different and the method for returning a result set to the GUI is quite different.
Then there's garbage like the relatively new "FORMAT" function, which has been tested to be 44 times slower than CONVERT and sometimes slower.
The "need" for ANSI and ISO compliant code is supported by the myth that code written to such standards is portable. And, is Hekaton or File Stream ANSI / ISO compliant? How about BULK INSERT?
Such standards make great guidelines but because of the myth of portability and the fact that they don't include some of the wonderfully powerful extensions that the various engines have, they tend to limit what can be done in code and tend to limit performance, sometimes to an extreme degree. My recommendation would be to use all the goodies of any engine and employ proper encapsulation instead.

And I fully agree with you Jeff. Use the capabilities of the engine you are using. If the code needs to be ported to a different engine, then it needs to be reworked to take advantage of that engines features. I don't write standard SQL, I write T-SQL code.

Sergiy SSC Guru Points: 110208 More actions · Answer 14

drew.allen (7/7/2016)
Sergiy (7/6/2016)
It's actually another way around - it's you who did not provide any rationale for using CAST rather than CONVERT in any cases.
Actually, I did provide my rationale, but I'll repeat it for you. CAST is ANSI standard, CONVERT is T-SQL specific. I tend to use the ANSI standard in cases where the results are the same.
Drew

What is the rational reason for using ANSI standard commands?

I honestly don't see any.

In what way they are any better then non-ANSI standard ones?

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

Sergiy (7/7/2016)
drew.allen (7/7/2016)
Sergiy (7/6/2016)
It's actually another way around - it's you who did not provide any rationale for using CAST rather than CONVERT in any cases.
Actually, I did provide my rationale, but I'll repeat it for you. CAST is ANSI standard, CONVERT is T-SQL specific. I tend to use the ANSI standard in cases where the results are the same.
Drew
What is the rational reason for using ANSI standard commands?
I honestly don't see any.
In what way they are any better then non-ANSI standard ones?

I use both cast and convert, it depends what I am doing. If I need the format control of convert, I use convert. If I don't and cast does what I need, I use cast.

Really don't care if you don't agree with this or not. You are entitled to your opinion and I am entitled to mine.