Skip to content

Add missing datatypes + variations to SQL_VARIANT_PROPERTY #4218

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
ktoliver merged 1 commit into from
Apr 1, 2020
Merged

Add missing datatypes + variations to SQL_VARIANT_PROPERTY #4218

ktoliver merged 1 commit into from
Apr 1, 2020

Conversation

@srutzky
Copy link
Contributor

@srutzky srutzky commented Feb 26, 2020

This PR fixes Issue #4215

Test script proving these additions can be found at:
https://pastebin.com/3RAFxLbH

Under BaseType:

  1. BIT

Under Precision:

  1. DATE
  2. DATETIME2(s)
  3. DATETIMEOFFSET
  4. DATETIMEOFFSET(s)
  5. DECIMAL / NUMERIC
  6. TIME
  7. TIME(s)

Under Scale:

  1. DATETIME2(s)
  2. DATETIMEOFFSET
  3. DATETIMEOFFSET(s)
  4. DECIMAL / NUMERIC
  5. TIME
  6. TIME(s)

Main test queries (for all test queries and more details, please see full script, linked at the top, on PasteBin):

DECLARE @Thing date = GETDATE();
SELECT 'date' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO


DECLARE @Thing datetime2(5) = GETDATE();
SELECT 'datetime2(5)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetime2(1) = GETDATE();
SELECT 'datetime2(1)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetime2(0) = GETDATE();
SELECT 'datetime2(0)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO

 
DECLARE @Thing datetimeoffset = GETDATE();
SELECT 'datetimeoffset' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetimeoffset(5) = GETDATE();
SELECT 'datetimeoffset(5)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetimeoffset(1) = GETDATE();
SELECT 'datetimeoffset(1)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing datetimeoffset(0) = GETDATE();
SELECT 'datetimeoffset(0)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO

 
DECLARE @Thing decimal = 1;
SELECT 'decimal' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO


DECLARE @Thing time = GETDATE();
SELECT 'time' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing time(5) = GETDATE();
SELECT 'time(5)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing time(1) = GETDATE();
SELECT 'time(1)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO
 
DECLARE @Thing time(0) = GETDATE();
SELECT 'time(0)' AS [DataType],
       SQL_VARIANT_PROPERTY(@Thing, 'Scale') AS [Scale],
       SQL_VARIANT_PROPERTY(@Thing, 'Precision') AS [Precision];
GO

And:

DECLARE @Table TABLE (c1 BIT)
INSERT INTO @Table (c1) VALUES (1);
SELECT SQL_VARIANT_PROPERTY(tmp.c1, 'BaseType') AS [Bit]
FROM   @Table tmp;

Take care,
Solomon...
https://SqlQuantumLift.com/
https://SqlQuantumLeap.com/
https://SQLsharp.com/

@srutzky
Add missing datatypes + variations to SQL_VARIANT_PROPERTY
01c82f7 
Test script proving these additions can be found at:
https://pastebin.com/3RAFxLbH

Under BaseType:
BIT

Under Precision:
DATE
DATETIME2(s)
DATETIMEOFFSET
DATETIMEOFFSET(s)
DECIMAL / NUMERIC
TIME
TIME(s)

Under Scale:
DATETIME2(s)
DATETIMEOFFSET
DATETIMEOFFSET(s)
DECIMAL / NUMERIC
TIME
TIME(s)
@PRMerger13
Copy link
Contributor

PRMerger13 commented Feb 26, 2020

@srutzky : Thanks for your contribution! The author(s) have been notified to review your proposed change.

@ktoliver ktoliver added the aq-pr-triaged tracking label for the PR review team label Feb 26, 2020
@MikeRayMSFT
Copy link
Contributor

MikeRayMSFT commented Apr 1, 2020

Thank you @srutzky - processing.

@pmasl
Copy link
Contributor

pmasl commented Apr 1, 2020

Thanks @srutzky .

#sign-off

@PRMerger16
Copy link
Contributor

PRMerger16 commented Apr 1, 2020

@pmasl: I'm sorry - only the author of this article, @MikeRayMSFT, can sign off on your changes. But we do have an exception process - if you are on the Microsoft content or product team for this product area, you can ask the PR review team to review and merge it by sending mail to the techdocprs alias.

@MikeRayMSFT
Copy link
Contributor

MikeRayMSFT commented Apr 1, 2020

#sign-off

@ktoliver ktoliver merged commit 0a89373 into MicrosoftDocs:live Apr 1, 2020
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@MikeRayMSFT MikeRayMSFT Awaiting requested review from MikeRayMSFT

Assignees

@MikeRayMSFT MikeRayMSFT

Labels

aq-pr-triaged tracking label for the PR review team Change sent to author ready-to-merge sql/prod t-sql/tech

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

7 participants

@srutzky @PRMerger13 @MikeRayMSFT @pmasl @PRMerger16 @ktoliver @PRMerger18