آموزش جامع SQL Server (جلسه ۳۳: توابع سیستمی – بخش۲)
1398/04/29 10:36 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه ۳۳: توابع سیستمی – بخش۲)

در جلسه قبل وارد مبحث توابع سیستمی در SQL Server شدیم. برای پوشش دقیق‌تر و بهتر و مفصل‌تر تمام توابع سیستمی، قرار بر این شد که مطالب این مبحث در دو جلسه پوشش داده شوند.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

در جلسه قبل توابع ISNUMERIC، ISNULL، CHOOSE، CONVERT، CAST و IIF پوشش داده شدند و بقیه مباحث نیز در این جلسه توضیح داده خواهند شد.

مطالب این مبحث به این شرح هستند:

بخش۱

  • CAST – یک مقدار با یک نوع را به یک نوع دیگر cast (یعنی تبدیل صریح)می‌کند.

  • CONVERT – یک مقدار با یک نوع را به یک نوع دیگر convert (یعنی تبدیل ضمنی) می‌کند.

  • CHOOSE – یکی از دو مقدار را بر اساس نتیجه اولین آرگومان بازمی‌گرداند.

  • ISNULL – مقدار NULL را با یک مقدار مشخص شده جایگزین می‌کند.

  • ISNUMERIC – بررسی می‌کند که آیا یک عبارت از نوع عددی معتبر هست یا نه.

  • IIF – منطق if-else را به یک query اضافه می‌کند.

بخش۲

  • TRY-CAST – یک مقدار با یک نوع را به یک نوع دیگر cast می‌کند و اگر cast موفقیت آمیز نبود، مقدار NULL بازمی‌گرداند.

  • TRY-CONVERT – یک مقدار با یک نوع را به یک نوع دیگر convert می‌کند و مقداری را بازمی‌گرداند که به یک نوع خاص ترجمه شده است. اگر cast یا تبدیل موفقیت‌آمیز نباشد، مقدار NULL را بازمی‌گرداند.

  • TRY-PARSE – یک رشته را به یک date/time یا یک عدد تبدیل می‌کند و اگر تبدیل موفقیت‌آمیز نباشد، مقدار NULL بازمی‌گرداند.

  • تبدیل Datetime به string – چگونگی تبدیل یک مقدار datetime به یک رشته با یک فرمت خاص را نشان می‌دهد.

  • تبدیل رشته به datetime – چگونگی تبدیل یک رشته به یک مقدار datetime را نشان می‌دهد.

  • تبدیل datetime به date – یک Datetime را به date تبدیل می‌کند.

در ادامه به توضیح بقیه مطالب خواهیم پرداخت. با ما همراه باشید!

تابع TRY_CAST در SQL Server

تابع ()TRY_CAST یک مقدار از یک نوع را به یک نوع دیگر تبدیل می‌کند. اگر تبدیل موفقیت آمیز نباشد، مقدار NULL بازگردانده می‌شود.

کد زیر، syntax تابع ()TRY_CAST را نشان می‌دهد:

TRY_CAST ( expression AS data_type [ ( length ) ] )  

تابع ()TRY_CAST دو آرگومان می‌پذیرد:

  • Data_type می‌تواند هر نوع داده‌ای معتبری که تابع بتواند expression را به آن تبدیل کند، باشد.

  • Expression یک مقدار برای تبدیل یا Cast شدن است.

تابع ()TRY_CAST مقدار ورودی را می‌گیرد و سعی می‌کند آن را به یک مقدار با نوع‌ داده‌ای مشخص شده تبدیلش کند. اگر تبدیل یا Cast با موفقیت انجام شود، تابع مقدار دارای نوع داده‌ای مشخص شده را بازمی‌گرداند. در غیر این صورت، مقدار NULL بازگردانده می‌شود.

اما، اگر به تغییری نیاز داشته باشید که به صورت صریح قابل انجام نباشد، آنگاه تابع ()TRY_CAST با یک ارور نشان می‌دهد که عملیات موفقیت آمیز نبود.

()TRY_CAST در برابر ()CAST

اگر تبدیل با موفقیت انجام نشود، تابع ()TRY_CAST مقدار NULL را بازمی‌گرداند، در حالی که تابع ()CAST ارور می‌دهد.

برای کنترل نتیجه تابع ()TRY_CAST در صورتی که تبدیل با موفقیت انجام نشود، می‌توانید از توابع یا عبارات کنترل NULL مانند COALESCE، ()ISNULL یا CASE برای کنترل نتیجه تابع ()TRY_CAST استفاده کنید.

از طرف دیگر، اگر تبدیل یا cast موفق نباشد، می‌توانید از کد TRY…CATCH برای کنترل نتیجه تابع ()CAST استفاده کنید.

مثال‌های تابع ()TRY_CAST در SQL Server

اجازه دهید چند مثال برای استفاده از تابع ()TRY_CAST حل کنیم.

الف) مثال بازگرداندن NULL توسط تابع ()TRY_CAST

مثال زیر، چگونگی بازگرداندن مقدار NULL توسط تابع ()TRY_CAST در زمانی که تبدیل موفقیت آمیز نیست را نشان می‌دهد:

SELECT 
    CASE
        WHEN TRY_CAST('test' AS INT) IS NULL
        THEN 'Cast failed'
        ELSE 'Cast succeeded'
    END AS Result;

خروجی به این شکل است:

Result
-----------
Cast failed
 
(1 row affected)
ب) مثال ارور دادن تابع ()TRY_CAST

این مثال یک ارور بازمی‌گرداند، چون یک عدد نمی‌تواند به یک نوع داده XML تبدیل شود:

SELECT 
    TRY_CAST(30.5 AS XML);

ارور به این شکل است:

Explicit conversion from data type numeric to xml is not allowed.
پ) مثال‌های استفاده از تابع ()TRY_CAST برای تبدیل رشته به Decimal

مثال زیر، از تابع ()TRY_CAST برای تبدیل یک رشته به یک decimal استفاده می‌کند:

SELECT 
    TRY_CAST('12.34' AS DECIMAL(4, 2)) Result

خروجی به این ترتیب است:

Result
----------
12.34

یک مثال دیگر به این شکل است:

SELECT 
    TRY_CAST('12.345' AS DECIMAL(4,2))  Result;

نتیجه به سمت بالای عدد 12.35 گرد می‌شود:

Result
-----------
12.35
 
(1 row affected)

این مثال مقدار NULL بازمی‌گرداند، چون تابع ()TRY_CAST نمی‌تواند رشته ‘1234.5’ را به یک (DECIMAL(4, 2 تبدیل کند:

SELECT 
    TRY_CAST('1234.5' AS DECIMAL(4, 2)) Result;

 خروجی به این شکل است:

Result
-----------
NULL
 
(1 row affected)
ت) مثال‌های استفاده از تابع ()TRY_CAST برای تبدیل رشته به integer

مثال زیر از تابع ()TRY_CAST برای تبدیل یک رشته به یک integer استفاده می‌کند:

SELECT 
    TRY_CAST('100' AS INT) Result;

مجموعه خروجی به این ترتیب است:

Result
-----------
100
 
(1 row affected)

این مثال مقدار NULL بازمی‌گرداند چون در آن تبدیل شکست می‌خورد:

SELECT 
    TRY_CAST('100.5' AS INT) Result;

خروجی به این صورت است:

Result
-----------
NULL
 
(1 row affected)  
ث) مثال زیر از تابع ()TRY_CAST برای تبدیل تاریخ و زمان سیستم فعلی به یک مقدار date استفاده می‌کند:
SELECT 
 TRY_CAST(GETDATE() AS DATE) Result;

خروجی به این ترتیب است:

Result
----------
2019-04-28
 
(1 row affected)

همچنین، می‌توانید از تابع ()TRY_CAST برای تبدیل تاریخ و زمان سیستم فعلی به یک مقدار time استفاده کنید:

SELECT 
 TRY_CAST(GETDATE() AS TIME) Result;

کد زیر، خروجی را نشان می‌دهد:

Result
----------------
17:36:37.5900000
 
(1 row affected)

در نتیجه، چگونگی استفاده از تابع ()TRY_CAST برای تبدیل یک مقدار از یک نوع به یک نوع دیگر را آموختید.

تابع TRY_CONVERT در SQL Server

تابع ()TRY_CONVERT یک مقدار از یک نوع را به یک نوع دیگر تبدیل می‌کند. اگر تبدیل موفقیت آمیز نباشد، مقدار NULL را بازمی‌گرداند.

کد زیر، syntax تابع ()TRY_CONVERT را نشان می‌دهد:

TRY_CONVERT (
    data_type[(length)], 
    expression 
    [,style]
)

تابع ()TRY_CONVERT سه آرگومان می‌پذیرد:

  • Data_type یک نوع داده‌ای معتبر است که تابع expression را به آن تبدیل می‌کند.

  • Expression مقداری است که باید تبدیل شود.

  • Style یک عدد است که مشخص می‌کند تابع چگونه عبارت را ترجمه می‌کند.

تابع ()TRY_CONVERT سعی می‌کند مقدار ارسال شده به آن را به یک نوع داده‌ای مشخص شده تبدیل کند. اگر تبدیل موفقیت آمیز باشد، مقداری که به آن ارسال شده را بازمی‌گرداند. در غیر این صورت، NULL بازمی‌گرداند.

به هر حال، اگر به تبدیلی نیاز داشته باشید که به صورت صریح قابل انجام و مجاز نباشد، آنگاه تابع ()TRY_CONVERT  با یک ارور مشخص می‌کند که عملیات موفقیت آمیز نبوده است.

()TRY_CONVERT در برابر ()CONVERT

اگر تبدیل موفق نباشد، تابع ()TRY_CONVERT مقدار NULL را بازمی‌گرداند، در حالی که تابع ()CONVERT ارور می‌دهد. این تفاوت اصلی بین این دو تابع است.

اگر تبدیل موفقیت آمیز نبود، می‌توانید توابع یا عبارات کنترل کننده NULL مانند ()ISNULL و COALESCE برای کنترل نتیجه تابع ()TRY_CONVERT استفاده کنید.

برای کنترل نتیجه تابع ()CONVERT در صورت موفقیت آمیز نبودن تبدیل، می‌توانید از دستور TRY…CATCH استفاده کنید.

 مثال‌های تابع ()TRY_CONVERT در SQL Server

بیایید چند مثال برای استفاده از تابع ()TRY_CONVERT حل کنیم.

الف) مثال بازگرداندن NULL توسط تابع ()TRY_CONVERT

این مثال نشان می‌دهد که وقتی عملیات تبدیل شکست می‌خورد، تابع ()TRY_CONVERT چگونه یک NULL بازمی‌گرداند:

SELECT 
    CASE
        WHEN TRY_CONVERT( INT, 'test') IS NULL
        THEN 'Cast failed'
        ELSE 'Cast succeeded'
    END AS Result;

خروجی به این شکل است:

Result
-----------
Cast failed
 
(1 row affected)
ب) مثال ارور دادن تابع ()TRY_CONVERT

مثال زیر یک ارور بازمی‌گرداند، چون integer نمی‌تواند به یک نوع داده XML تبدیل شود:

SELECT 
    TRY_CONVERT( XML, 20);

ارور به این شکل است:

Explicit conversion from data type int to xml is not allowed.
پ) مثال‌های استفاده از تابع ()TRY_CONVERT برای تبدیل رشته به Decimal

این مثال از تابع ()TRY_CONVERT برای تبدیل یک رشته به decimal استفاده می‌کند:

SELECT 
    TRY_CONVERT(DECIMAL(4,2), '12.34');

خروجی به این ترتیب است:

Result
----------
12.34

کد زیر، یک مثال دیگر را نشان می‌دهد:

SELECT 
    TRY_CONVERT(DECIMAL(4,2), '12.345')  Result;

نتیجه گرده شده است:

Result
-----------
12.35
 
(1 row affected)

مثال زیر مقدار NULL بازمی‌گرداند، چون تابع ()TRY_CONVERT نمی‌تواند رشته ‘1234.5’ را به یک (DECIMAL(4, 2 تبدیل کند:

SELECT 
    TRY_CONVERT( DECIMAL(4, 2), '1234.5') Result;

خروجی به این ترتیب است:

Result
-----------
NULL
 
(1 row affected)
ت) مثال‌های استفاده از تابع ()TRY_CONVERT برای تبدیل رشته به integer

این مثال از تابع ()TRY_CONVERT برای تبدیل یک رشته به یک integer استفاده می‌کند:

SELECT 
    TRY_CONVERT( INT, '100') Result;

خروجی به این صورت است:

Result
-----------
100
 
(1 row affected)

از طرف دیگر، این مثال مقدار NULL را بازمی‌گرداند، چون تبدیل شکست می‌خورد:

SELECT 
    TRY_CONVERT( INT, '100.5') Result;

خروجی:

Result
-----------
NULL
 
(1 row affected)  
ث) مثال استفاده از تابع ()TRY_CONVERT برای تبدیل Datetime به Date یا time

این مثال از تابع ()TRY_CONVERT برای تبدیل تاریخ و زمان فعلی سیستم به یک مقدار date استفاده می‌کند:

SELECT 
 TRY_CONVERT( DATE, GETDATE()) Result;

خروجی به این شکل است:

Result
----------
2019-04-28
 
(1 row affected)

به طور مشابه، می‌توانید از تابع ()TRY_CONVERT برای تبدیل تاریخ و زمان فعلی سیستم به یک مقدار time استفاده کنید:

SELECT 
 TRY_CONVERT( TIME, GETDATE()) Result;

کدهای زیر، خروجی را نشان می‌دهند:

Result
----------------
17:10:19.1700000
 
(1 row affected)

به این شکل، چگونگی استفاده از تابع ()TRY_CONVERT برای تبدیل یک مقدار از یک نوع به یک نوع دیگر را آموختید.

تابع TRY_PARSE در SQL Server

تابع ()TRY_PARSE برای ترجمه نتیجه یک عبارت به یک نوع داده درخواست شده، استفاده می‌کند. اگر تبدیل شکست بخورد، مقدار NULL بازگشت داده می‌شود.

Syntax تابع  ()TRY_PARSE به این صورت است:

TRY_PARSE ( expression AS data_type [ USING culture ] )  

در این Syntax:

  • Expression دارای یک مقدار رشته با نوع (NVARCHAR(4000 است.

  • Data_type نشان دهنده نوع داده درخواستی برای نتیجه است.

  • Culture یک رشته اختیاری است که رفتار و نحوه‌ای که Expression قرار است بر اساس آن فرمت شود را مشخص می‌کند. به صورت پیش‌فرض مقدار آن روی زبان کد نویسی فعلی تنظیم شده است. توجه کنید که culture به رفتارهایی که توسط SQL پشتیبانی می‌شوند محدود نیست، بلکه می‌تواند هر رفتاری که توسط NET Freamework. پشتیبانی می‌شود را بپذیرد.

مثال‌های تابع  ()TRY_PARSE در SQL Server

بیایید چند مثال برای استفاده از تابع  ()TRY_PARSE حل کنیم.

الف) استفاده از تابع  ()TRY_PARSE برای تبدیل یک رشته به یک Date

این مثال از تابع  ()TRY_PARSE برای تبدیل رشته ’14 April 2019’ به یک تاریخ استفاده می‌کند:

SELECT 
    TRY_PARSE('14 April 2019' AS date) result;

خروجی به این ترتیب است:

result
----------
2019-04-14
 
(1 row affected)
ب) استفاده از تابع  ()TRY_PARSE برای تبدیل یک رشته به یک عدد

مثال زیر از تابع  ()TRY_PARSE برای تبدیل رشته ‘1250-‘ به یک integer استفاده می‌کند:

SELECT 
    TRY_PARSE('-1250' AS INT) result;

کد زیر، خروجی را نشان می‌دهد:

result
-----------
-1250
 
(1 row affected)

این کد NULL بازمی‌گرداند چون تابع  ()TRY_PARSE نمی‌تواند رشته ‘ABC’ را به یک Decimal تبدیل کند.

SELECT 
    TRY_PARSE('ABC' AS DEC) result;

خروجی به این ترتیب است:

result
-----------
NULL
 
(1 row affected)
پ) استفاده از تابع  ()TRY_PARSE همراه با عبارت CASE

این مثال از تابع  ()TRY_PARSE همراه با CASE استفاده می‌کند تا عبارت را تست کند و اگر تبدیل شکست بخورد یا موفق انجام شود، پیغام مرتبط با آن را چاپ می‌کند.

SELECT 
    CASE
        WHEN TRY_PARSE('Last year' AS DATE) IS NULL
        THEN 'Cast failed'
        ELSE 'Cast succeeded'
    END AS result;

کد زیر خروجی را نشان می‌دهد:

result
--------------------
Cast failed
 
(1 row affected)

به این شکل، چگونگی استفاده از تابع ()TRY_PARSE برای تبدیل یک رشته به یک Date یا time و نوع‌های عددی را آموختید.

تبدیل Datetime به رشته

برای تبدیل یک datetime به یک رشته، می‌توانید از تابع ()CONVERT به این صورت استفاده کنید:

CONVERT(VARCHAR, datetime [,style])

در این syntax:

  • VARCHAR اولین آرگومان است که نشان‌ دهنده نوع رشته است.

  • Datetime یک عبارت است که مقدار آن یک Date یا datetime است که می‌خواهید آن را به یک رشته تبدیل کنید.

  • Style فرمت تاریخ یا date را مشخص می‌کند. مقدار style یک عدد از پیش تعریف شده توسط SQL Server است. پارامتر style اختیاری است.

جدول زیر، styleهای معتبر و فرمت متناظر با Datetime پس از تبدیل به یک رشته را نشان می‌دهد.

بدون عدد قرن (yy)

با عدد قرن (yy)

استاندارد

فرمت

0 or 100

پیش‌فرض برای datetime و smalldatetime

mon dd yyyy hh:miAM (or PM)

1

101

U.S.

1 = mm/dd/yy
101 = mm/dd/yyyy

2

102

ANSI

2 = yy.mm.dd
102 = yyyy.mm.dd

3

103

British/French

3 = dd/mm/yy
103 = dd/mm/yyyy

4

104

German

4 = dd.mm.yy
104 = dd.mm.yyyy

5

105

Italian

5 = dd-mm-yy
105 = dd-mm-yyyy

6

106

6 = dd mon yy
106 = dd mon yyyy

7

107

7 = Mon dd, yy
107 = Mon dd, yyyy

8

108

hh:mi:ss

9 or 109

پیش‌فرض + میلی‌ثانیه

mon dd yyyy hh:mi:ss:mmmAM (or PM)

10

110

USA

10 = mm-dd-yy
110 = mm-dd-yyyy

11

111

JAPAN

11 = yy/mm/dd
111 = yyyy/mm/dd

12

112

ISO

12 = yymmdd
112 = yyyymmdd

13 or 113

پیش‌فرض اروپا + میلی‌ثانیه‌ها

dd mon yyyy hh:mi:ss:mmm(24h)

14

114

hh:mi:ss:mmm(24h)

20 or 120

استانداردODBC

yyyy-mm-dd hh:mi:ss(24h)

21 or 121

استاندارد ODBC(با میلی‌ثانیه‌ها)  پیش‌فرض برای time, date, datetime2, and datetimeoffset

yyyy-mm-dd hh:mi:ss.mmm(24h)

126

ISO8601

yyyy-mm-ddThh:mi:ss.mmm (no spaces)

127

ISO8601 با منطقه Z

yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)

130

هجری

dd mon yyyy hh:mi:ss:mmmAM

131

هجری

dd/mm/yyyy hh:mi:ss:mmmAM

مثال‌های تبدیل یک datetime به یک رشته

۱) تبدیل Datetime به رشته در فرمت (mon dd yyyy hh:miAM (or PM
DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';
 
SELECT 
    CONVERT(VARCHAR(20),@dt,0) s1,
    CONVERT(VARCHAR(20),@dt,100) s2;

خروجی به این شکل است:

s1                   s2
-------------------- --------------------
Dec 31 2019  2:43PM  Dec 31 2019  2:43PM
 
(1 row affected)
۲) تبدیل datetime به رشته در فرمت‌های mm/dd/yy و mm/dd/yyyy
DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';
SELECT 
    CONVERT(VARCHAR(10),@dt,1) s1,
    CONVERT(VARCHAR(10),@dt,101) s2;

خروجی به این ترتیب است:

s1         s2
---------- ----------
12/31/19   12/31/2019
 
(1 row affected)
۳) تبدیل Datetime به رشته در فرمت dd/mm/yy و dd/mm/yyyy
DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';
 
SELECT 
    CONVERT(VARCHAR(10),@dt,3) s1,
    CONVERT(VARCHAR(10),@dt,103) s2;

خروجی به این ترتیب است:

s1         s2
---------- ----------
31/12/19   31/12/2019
 
(1 row affected)
۴) تبدیل datetime به رشته در فرمت‌های dd.mm.yy و dd.mm.yyyy
DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';
 
SELECT 
    CONVERT(VARCHAR(10),@dt,4) s1,
    CONVERT(VARCHAR(10),@dt,104) s2;

خروجی به این ترتیب است:

s1         s2
---------- ----------
31.12.19   31.12.2019
 
(1 row affected)

۵) تبدیل datetime به رشته در فرمت‌های dd-mm-yy و dd-mm-yyyy

DECLARE @dt DATETIME = '2019-12-31 14:43:35.863';
 
SELECT 
    CONVERT(VARCHAR(10),@dt,5) s1,
    CONVERT(VARCHAR(10),@dt,105) s2;

خروجی به این شکل است:

s1         s2
---------- ----------
31-12-19   31-12-2019
 
(1 row affected)
۶) تبدیل Datetime به رشته در فرمت‌های dd mon yy و dd mon yyyy
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
 
SELECT 
    CONVERT(VARCHAR(10), @dt, 6) s1, 
    CONVERT(VARCHAR(10), @dt, 106) s2;

خروجی:

s1         s2
---------- ----------
31 Dec 19  31 Dec 201
 
(1 row affected)       

۷) تبدیل datetime به رشته در فرمت‌های mon dd, yy و mon dd, yyyy

DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 7) s1, 
       CONVERT(VARCHAR(10), @dt, 107) s2;

خروجی:

s1         s2
---------- ----------
Dec 31, 19 Dec 31, 20
 
(1 row affected)
۸) تبدیل datetime به رشته در فرمت hh:mi:ss
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 108) s1;

خروجی:

s1
----------
14:43:35
 
(1 row affected) 
۹) تبدیل datetime به رشته در فرمت (mon dd yyyy hh:mi:ss:mmmAM (or PM
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT 
    CONVERT(VARCHAR(30), @dt, 109) s1; 

خروجی:

s1
------------------------------
Dec 31 2019  2:43:35:863PM
 
(1 row affected)
۱۰) تبدیل datetime به رشته در فرمت‌های mm-dd-yy و mm-dd-yyyyy
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 10) s1, 
    CONVERT(VARCHAR(10), @dt, 110) s2;

خروجی عبارت است از:

s1         s2
---------- ----------
12-31-19   12-31-2019
 
(1 row affected)
۱۱) تبدیل Datetime به رشته در فرمت‌های yy/mm/dd و yyyy/mm/dd
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 11) s1, 
    CONVERT(VARCHAR(10), @dt, 111) s2;

خروجی به این ترتیب است:

s1         s2
---------- ----------
19/12/31   2019/12/31
 
(1 row affected)
۱۲) تبدیل datetime به رشته در فرمت‌های yymmdd و yyyymmdd
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 12) s1, 
    CONVERT(VARCHAR(10), @dt, 112) s2;

خروجی عبارت است از:

s1         s2
---------- ----------
191231     20191231
 
(1 row affected)
۱۳) تبدیل Datetime به رشته در فرمت (dd mm yyyy hh:mi:ss:mmm(24h
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(10), @dt, 113) s1;

خروجی به این ترتیب است:

s1
----------
31 Dec 201
 
(1 row affected)
۱۴) تبدیل datetime به رشته در فرمت (hh:mi:ss:mmm(24h
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(20), @dt, 114) s1;

خروجی به این ترتیب است:

s1
--------------------
14:43:35:863
 
(1 row affected)
۱۵) تبدیل Datetime به رشته در فرمت (yyyy-mm-dd hh:mi:ss(24h
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(20), @dt, 120) s1;

خروجی عبارت است از:

s1
--------------------
2019-12-31 14:43:35
 
(1 row affected)
۱۶) تبدیل datetime به رشته در فرمت (yyyy-mm-dd hh:mi:ss.mmm(24h
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(30), @dt, 121) s1;

خروجی به این ترتیب است:

s1
------------------------------
2019-12-31 14:43:35.863
 
(1 row affected)
۱۷) تبدیل Datetime به رشته در فرمت yyyy-mm-ddThh:mi:ss.mmm
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(25), @dt, 126) s1;

خروجی به این شکل است:

s1
-------------------------
2019-12-31T14:43:35.863
 
(1 row affected)
۱۸) تبدیل Datetime به رشته در فرمت yyyy-m-ddThh:mi:ss.mmmZ
DECLARE @dt DATETIME= '2019-12-31 14:43:35.863';
SELECT CONVERT(VARCHAR(25), @dt, 127) s1;

خروجی به این ترتیب است:

s1
-------------------------
2019-12-31T14:43:35.863
 
(1 row affected)

در نتیجه، چگونگی استفاده از روش تبدیل یک تاریخ به یک رشته با استفاده از تابع ()CONVERT آشنا شدید.

تبدیل رشته به datetime

باز هم از تابع ()CONVERT برای تبدیل یک مقدار از یک نوع به یک نوع دیگر استفاده می‌کنیم و syntax آن محض یادآوری به شکل زیر است:

CONVERT(target_type, expression [, style])

علاوه بر تابع ()CONVERT، همچنین می‌توانید از تابع ()TRY_CONVERT نیز استفاده کنید:

TRY_CONVERT(target_type, expression [, style])

همان‌طور که قبلاً گفته شد، تفاوت اصلی بین ()CONVERT و ()TRY_CONVERT این است که اگر تبدیل شکست بخرد، تابع ()CONVERT ارور می‌دهد، در حالی که تابع ()TRY_CONVERT مقدار NULL بازمی‌گرداند.

این مثال از تابع ()CONVERT برای تبدیل یک رشته در فرمت تاریخ ANSI به یک datetime استفاده می‌کند:

SELECT 
    CONVERT(DATETIME, '2019-08-15', 102) result;

خروجی به این ترتیب است:

result
-----------------------
2019-08-15 00:00:00.000
 
(1 row affected)

اگر تبدیل شکست بخورد، تابع ()CONVERT ارور می‌دهد:

SELECT 
    CONVERT(DATETIME, '2019-18-15', 102) result;

خط زیر، ارور را نشان می‌دهد:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

از طرف دیگر، تابع ()TRY_CONVERT در صورت شکست تبدیل، به جای ارور دادن، مقدار NULL بازمی‌گرداند:

SELECT 
    TRY_CONVERT(DATETIME, '2019-18-15', 102) result;

خروجی به این شکل است:

result
-----------------------
NULL
 
(1 row affected)

تبدیل یک رشته در فرمت تاریخ ANSI/ISO و US به یک datetime

هر دو توابع ()CONVERT و ()TRY_CONVERT می‌توانند فرمت‌های ANSI/ISO و US را با فاصله‌ها و حائل‌های مختلف پیش‌فرض تشخیص دهند، بنابراین لازم نیست پارامتر style را اضافه کنید.

این مثال چگونگی استفاده از تابع ()CONVERT برای تبدیل رشته‌ها با فرمت تاریخ ISO به مقادیر Datetime را نشان می‌دهد:

SELECT CONVERT(DATETIME, '2019-09-25');
SELECT CONVERT(DATETIME, '2019/09/25');
SELECT CONVERT(DATETIME, '2019.09.25');
SELECT CONVERT(DATETIME, '2019-09-25 12:11');
SELECT CONVERT(DATETIME, '2019-09-25 12:11:09');
SELECT CONVERT(DATETIME, '2019-09-25 12:11:09.555');
SELECT CONVERT(DATETIME, '2019/09/25 12:11:09.555');
SELECT CONVERT(DATETIME, '2019.09.25 12:11:09.555');

توجه کنید که تابع ()CONVERT همچنین می‌تواند یک رشته تاریخ ISO را بدون حائل و فاصله به یک مقدار date تبدیل کند که در کد زیر مشاهده می‌کنید:

SELECT 
    CONVERT(DATETIME, '20190731') result;

خروجی به این صورت است:

result
-----------------------
2019-07-31 00:00:00.000
 
(1 row affected)

توابع ()CONVERT و ()TRY_CONVERT می‌توانند به صورت پیش‌فرض فرمت datetime ایالات متحده (یعنی month, day, year and time) را نیز تبدیل کنند، بنابراین  لازم نیست استایل 101 را حتماً مشخص کنید:

SELECT TRY_CONVERT( DATETIME, '12-31-2019');
SELECT TRY_CONVERT( DATETIME, '12/31/2019');
SELECT TRY_CONVERT( DATETIME, '12.31.2019');
SELECT TRY_CONVERT( DATETIME, '12-31-2019 12:15');
SELECT TRY_CONVERT( DATETIME, '12/31/2019 12:15:10');
SELECT TRY_CONVERT( DATETIME, '12.31.2019 12:15:10.333');

به این صورت، چگونگی تبدیل یک رشته به یک datetime با استفاده از توابع ()CONVERT و ()TRY_CONVERT را نیز آموختید.

تبدیل datetime به date

برای تبدیل یک datetime به یک date یا تاریخ، می‌توانید از توابع ()TRY_CONVERT، ()CONVERT یا ()CAST استفاده کنید.

تبدیل datetime به Date با استفاده از تابع ()CONVERT

این کد، از تابع ()CONVERT برای تبدیل یک Datetime به یک date استفاده می‌کند:

CONVERT(DATE, datetime_expression)

در این syntax، کد datetime_expression می‌تواند هر عبارتی که مقدار آن یک datetime معتبر است، باشد. اگر تبدیل شکست بخورد، تابع ()CONVERT ارور می‌دهد.

مثال زیر از تابع ()CONVERT برای تبدیل یک Datetime به یک date استفاده می‌کند:

SELECT 
    CONVERT(DATE, GETDATE()) date;

خروجی به این شکل است:

date
----------
2019-04-23
 
(1 row affected)

توجه کنید که تابع ()GETDATE مقدار Datetime فعلی سرور پایگاه داده را بازمی‌گرداند.

تبدیل datetime به date با استفاده از تابع ()TRY_CONVERT

به طور مشابه، تابع ()TRY_CONVERT نیز می‌تواند datetime را به date تبدیل کند:

TRY_CONVERT(DATE, datetime_expression)

بر خلاف تابع ()CONVERT، تابع ()TRY_CONVERT در صورت موفق نبودن فرآیند تبدیل، مقدار NULL بازمی‌گرداند.

این مثال، از تابع ()TRY_CONVERT برای تبدیل Datetime فعلی به یک Date استفاده می‌کند:

SELECT
    TRY_CONVERT(DATE,GETDATE());

کد زیر، خروجی را نشان می‌دهد:

date
----------
2019-04-23
 
(1 row affected)

تبدیل datetime به date با استفاده از تابع ()CAST

کد زیر، یک مقدار Datetime را با استفاده از تابع ()CAST به یک Date تبدیل می‌کند:

CAST(datetime_expression AS DATE)

این مثال از تابع ()CAST برای تبدیل Datetime فعلی به یک مقدار date استفاده می‌کند:

SELECT 
    CAST(GETDATE() AS DATE) date;

خروجی به این شکل است:

date
----------
2019-04-23
 
(1 row affected)

در نتیجه، چگونگی تبدیل یک Datetime به یک Date با استفاده از توابع ()TRY_CONVERT، ()CONVERT و ()CAST را هم آموختید.

خلاصه

در این دو جلسه اخیر مبحث توابع سیستمی در SQL Server را پوشش دادیم و تک‌تک این توابع و روش‌های استفاده از آن‌ها را همراه با مثال به صورت کامل توضیح دادیم.

در این جلسه روش‌های تبدیل Datetime به رشته، تبدیل رشته به Datetime و تبدیل Datetime به Date و توابع TRY_CONVERT، TRY_CAST و TRY_PARSE توضیح داده شدند.

در جلسه آینده وارد آخرین مبحث دوره آموزش جامع SQL Server، یعنی توابع پنجره‌ای یا Window خواهیم شد و طی دو جلسه این آموزش را به پایان خواهیم برد.

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات