Comparing a real datetime to a constructed datetime

I have the following SQL:

select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a

where right(term,4) always returns a string which constitutes a 4 digit year eg '1999','2004',etc.

The SQL above returns

2004-04-20 00:00:00.000 Deposit ...

Which makes me think that it is able to successfully construct the datetime object inline. But then when I try and do:

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

I get the following error:

Syntax error converting datetime from character string.

Given that it executes the innermost SQL just fine and seems to convert the string to a datetime object, I don't see why subsequently trying to USE that datetime object for something (in this case comparison with submit_date which is a datetime in the table a) should screw it up. Help!!! Thanks...What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?|||What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?

If I do:

SELECT top 10 right(term,4) FROM dbo.status_view

I get:

2003
2002
2003
2002
2003
2003
2001
2002
2003
2001

But if I do:

SELECT top 10 right(term,4) FROM dbo.status_view
WHERE
isnumeric(right(term,4)) =0 and

It returns no rows! I think you're on to something. But the weirdest part is if I do:

SELECT top 10 cast(right(term,4) as int) FROM dbo.status_view

I still get:

2003
2002
2003
2002
2003
2003
2001
2002
2003
2001

Ack!|||What does:

SELECT * FROM status_view
WHERE ISNUMERIC(RIGHT(Term,4))=0

return for you?

Oh wait, I was doing one and not zero. If I do:

SELECT count(1) FROM dbo.status_view
WHERE
and isnumeric(right(term,4)) = 0

it returns zero. So I guess thats not it...|||Well that just means it doesn't see anything that it doesn't think that a number...

What about...

SELECT * FROM status_view
WHERE CONVERT(int,RIGHT(Term,4))<1784|||Well that just means it doesn't see anything that it doesn't think that a number...

What about...

SELECT * FROM status_view
WHERE CONVERT(int,RIGHT(Term,4))<1784

This works fine. It returns no rows, but no errors. This is driving me insane!|||This is your problem, right?

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) where dt >= a.submit_date

Which is syntactically incorrect...

what is a.submit_date? I assume it's from select *

Plus you need ro give the derived table a name

select * from
(
select convert(datetime,'04-20-' + right(term,4)) as dt,
'Deposit' as type, a.* from
dbo.status_view a
) AS XXX
where dt >= submit_date

Does that run?

Compare nvarchar(10) with nvarchar(1000)

I had this question for quite a long time.

It seems the latter one don't take any extra storage space than the previous one.

As long as the real string length is less than 10.

Is that mean the latter one not cost anything?

I once heard the different is when they are in memory. But not sure of it.

Can anyone explain it and provide some official reference on it?

Thank.

Yes. That’s what Var(rying)char means. It is not a fixed length value. Whatever your value length for the same length only the memory occupied.

That means there is no impact when you use nvarchar(1000) where you only have nvarchar(10) values.

|||

And there's a little note on it in Books Online

http://msdn2.microsoft.com/en-us/library/ms186939.aspx

"The storage size, in bytes, is two times the number of characters entered..."

On a slight tangent though, it does also mention that a varying length column should add an extra 2 bytes to storage though when i store the value 'RICHARD' in a nvarchar(10) column, running DATALENGTH only shows 14bytes stored.

Where are these missing 2 bytes?

|||

NOTE:

Datalength and Storage Size are different. It need not to be same. Storage Size of NVarchar is

Char Length(value) * 2 + 2

or

Datalength(value) + 2

For varchar,

Char Length(value)+ 2

Or

Datalength(value) + 2

Why additional 2 bytes on storage?

To identity the length of the value. Bcs the values will be stored as binary(or bytes). It will first fetch the length of the value which will guide how many bytes to read for the current value.

|||Cheers Mani.

Should i be able to see these extra bytes if i delve into DBCC PAGE?
|||Hi Mani,

Is that mean there is no performance impact when use nvarchar(1000) vs. nvarchar(10), even in memory?

If this is the case, then why should we choose N carefully?

Thanks
|||

While retriving the Varchar definition size wont affect your performance.

Compare nvarchar(10) with nvarchar(1000)

I had this question for quite a long time.

It seems the latter one don't take any extra storage space than the previous one.

As long as the real string length is less than 10.

Is that mean the latter one not cost anything?

I once heard the different is when they are in memory. But not sure of it.

Can anyone explain it and provide some official reference on it?

Thank.

Yes. That’s what Var(rying)char means. It is not a fixed length value. Whatever your value length for the same length only the memory occupied.

That means there is no impact when you use nvarchar(1000) where you only have nvarchar(10) values.

|||

And there's a little note on it in Books Online

http://msdn2.microsoft.com/en-us/library/ms186939.aspx

"The storage size, in bytes, is two times the number of characters entered..."

On a slight tangent though, it does also mention that a varying length column should add an extra 2 bytes to storage though when i store the value 'RICHARD' in a nvarchar(10) column, running DATALENGTH only shows 14bytes stored.

Where are these missing 2 bytes?

|||

NOTE:

Datalength and Storage Size are different. It need not to be same. Storage Size of NVarchar is

Char Length(value) * 2 + 2

or

Datalength(value) + 2

For varchar,

Char Length(value)+ 2

Or

Datalength(value) + 2

Why additional 2 bytes on storage?

To identity the length of the value. Bcs the values will be stored as binary(or bytes). It will first fetch the length of the value which will guide how many bytes to read for the current value.

|||Cheers Mani.

Should i be able to see these extra bytes if i delve into DBCC PAGE?
|||Hi Mani,

Is that mean there is no performance impact when use nvarchar(1000) vs. nvarchar(10), even in memory?

If this is the case, then why should we choose N carefully?

Thanks
|||

While retriving the Varchar definition size wont affect your performance.