en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
יול13

Written by: ronen ariely
13/07/2012 11:58 RssIcon

תקציר

לא אחת עלינו לעבוד עם נתון של תאריך בפורמט של שרשרת או הפוך. כאשר השרשרת שרוצים לקבל או ליצור היא בפורמט המוכר על ידי שרת ה SQL כפורמט של תאריך אנחנו יכולים לעבוד ישירות עם אחת מהפונקציות המובנות שיש לנו להמרה CONVERT או CAST. אבל אם מדובר בפורמט מעט שונה?

הערה: בשרת SQL בגרסה 2012 נוספה לנו פונקציה חדשה מבוססת על CLR בשם FORMAT. פונקציה זו יעילה מאוד (מבוססת כאמור על CLR) ובאפשרותה ניתן לבצע את כל שיש במדריך זה בעקרון. מדריך זה נועד לעבודה בגרסאות קודמות. עם זה כדי לא להשאיר את הדברים באוויר נראה בסופו של המדריך כיצד ניתן לעבוד עם גרסת 2012 ופונקצית FORMAT.

ניווט מהיר

  • הבעיה=>
  • הפתרון =>
    • הפתרון הראשון ==>
      • הסבר מלא כולל לוגיקה בה נעזר גם בפתרון השני
      • הערות, הארות ונקודות למחשבה
    • הפתרון השני ==>
    •  SQL 2012 ==>
    • הפונקציה ההפוכה Text To Date
  • סיכום ==> עצלנים יכולים לעבור לסיכום, בו תוכלו לראות מתי להשתמש בכל אחת מהפונקציות, ולהורדת הפונקציות המוכנות.
  • קישורים להורדה
    • פונקציה ראשונה ===>
    • פונקציה שנייה ==>

הבעיה

כאשר השרשרת שרוצים לקבל או ליצור היא בפורמט המוכר על ידי שרת ה SQL כפורמט של תאריך אז אנחנו יכולים לעבוד ישירות עם אחת מהפונקציות המובנות שיש לנו להמרה CONVERT או CAST. דוגמה טובה ניתן לראות בקישור זה ==>. לדוגמה אם נרצה לפרמט את התאריך 27/02/2013 בשעה 22:34:12 לפורמט טקסטואלי מהצורה 27/02/2013 נוכל להיעזר בפונקצית CONVERT ישירות מפני שפורמט זה מוכר בשרת SQL.

declare @DT as datetime = '20130227 22:34:12'
SELECT convert(varchar, @DT, 103) -- 27/02/2013
GO

אבל מה אם נרצה לעבוד עם פורמט מעט יותר מורכב או "סתם" פורמט ייחודי שלנו כמו 27$02$2013 או אפילו פורמט הכולל טקסט כמו:

My next birthday will be on the 27 of 02 this year 2013

במקרה זה נוכל לבצע כמובן את הפעולה בשלבים על ידי חיבור של חלקי טקסט למשל על ידי:

declare @DT as datetime = '20130227 22:34:12'
select
    'My next birthday will be on the '
    + CONVERT(nvarchar(2),DATEPART(day,@DT))
    + ' of '
    + CONVERT(nvarchar(2),DATEPART(MM,@DT))
    + ' this year '
    + CONVERT(nvarchar(4),DATEPART(YYYY,@DT)) + ''
GO

מורכב מאוד לרישום אבל עדיין סביר.

הבעיה האמיתית מתחילה כאשר הפורמט של הטקסט שלנו אינו קבוע ולכן איננו יכולים לבצע פעולת ניתוח מבנה הטקסט (parsing) בצורה כזו ועלינו למצוא דרך דינאמית לביצוע הפעולה. למשל אם יש לנו טבלה עם טור של פורמטים מורכבים של טקסטים הכוללים נתוני תאריכים משולבים בהם, ואנחנו רוצים להחזיר את התאריך של היום בכל הפורמטים הנ"ל.

הפתרון

נעזר בפונקציה שתבצע את הפעולה של ניתוח תבנית הטקסט שלנו בצורה דינאמית. ז"א נעזר בפונקציה שמקבלת נתון של התאריך + נתון של תבנית הטקסט שלנו (נתונים שיכולים להגיע מטורים בטבלה למשל). במדריך זה אציג פונקציה בסיסית שכתבתי לביצוע פעולה זו. יש לשים לב שהפונקציה הראשונה שנציג אינה מיטבית והיא עושה שימוש ב TSQL נקי. בהמשך המדריך אדון בפונקציה זו ובדרכים למטב אותה לשם עבודה פרקטית (כולל שימוש ב CLR לקבלת ביצועיים מיטביים).

FORMAT using the function Ariely_FormatDate2String_01

USE [_ArielyAccessoriesDB]
GO
/****** Object:  UserDefinedFunction [dbo].[Ariely_FormatDate2String]    Script Date: 07/10/2012 19:16:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
-- =============================================
-- Author:                  Ariely Ronen
-- Create date:             20/10/2011
-- Last Modified date:      11/07/2012
-- Description:
-- =============
-- This function takes a date as first parameter and return a string formated using the second parameter as formating pattern.
-- the pattern string can containing any parts of date at different locations in the string
-- and any string between the date parts.
-- According to the pattern format the function identifies the parts of the date
-- and replace these parts with the date parts from the first parameter.
-- since some string might look like date parts we can use the third parameter to pass words separated by commas
-- function will not identifies as date parts these words. See examples
--
-- This function is written in pure TSQL as i was asked to.
-- it is not optimal!!!
-- In fact personally I work with a similar function which the part of the loop is executed using CLR SPLIT,
-- without using any loop
-- =============================================
CREATE FUNCTION [dbo].[Ariely_FormatDate2String_01] (
    @Datetime DATETIME
    , @FormatMask NVARCHAR(100)
    , @Exclude varchar(1000) = ''
)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @StringDate NVARCHAR(100)
    SET @StringDate = @FormatMask
      
      
    /*******************************************************/
    DECLARE @StartWord int
    DECLARE @EndWord int
    DECLARE @ThisWord nvarchar(100)
    DECLARE @Loop int
      
    set @Loop = 0
    SET @Exclude = @Exclude + ','
    set @StartWord = 0
    set @EndWord =  charindex(',',@Exclude,@StartWord)
      
    WHILE (@EndWord < len(@Exclude))
    BEGIN
        set @Loop = @Loop + 1
        SET @ThisWord = substring(@Exclude,@StartWord + 1,@EndWord - @StartWord - 1)
        --print '@ThisWord: ' + @ThisWord
          
        SET @StringDate =
            replace(
                @StringDate
                ,@ThisWord
                ,'[@#' + convert(varchar(10),@Loop) + '#@]'
            )
          
        set @StartWord = @EndWord
        --print '@StartWord: ' + convert(varchar(10),@StartWord)
        set @EndWord = charindex(',',@Exclude,@EndWord+1)
        --print '@EndWord: ' + convert(varchar(10),@EndWord)
    END
    set @Loop = @Loop + 1
    SET @ThisWord = substring(@Exclude,@StartWord + 1,@EndWord - @StartWord - 1)
    --print '@ThisWord: ' + @ThisWord
    SET @StringDate =
        replace(
            @StringDate
            ,@ThisWord
            ,'[@#' + convert(varchar(10),@Loop) + '#@]'
        )
    /*******************************************************/
      
      
    DECLARE @Time time
    IF (CHARINDEX ('HH:MM:SS:MMM',@FormatMask) > 0)
        BEGIN
            SET @StringDate = REPLACE(@StringDate, 'HH:MM:SS:MMM',left(convert(nvarchar(100),convert(time,@Datetime)) + '000000000000',12))
            SET @FormatMask = replace('HH:MM:SS:MMM','**:**:**:***',@FormatMask)
        END
    IF (CHARINDEX ('HH:MM:SS',@FormatMask) > 0)
        BEGIN
            SET @StringDate = REPLACE(@StringDate, 'HH:MM:SS',left(convert(nvarchar(100),convert(time,@Datetime)) + '00000000',8))
            SET @FormatMask = replace('HH:MM:SS','**:**:**',@FormatMask)
        END
      
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'YYYY',DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'YY',RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'Month',DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
        SET @StringDate = REPLACE(@StringDate, 'MON',LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'Mon',LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'MM',RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('M',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'M',CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX ('DD',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'DD',RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX ('D',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'D',DATENAME(DD, @Datetime))
      
      
    /*******************************************************/
    set @Loop = 0
    --SET @Exclude = @Exclude + ','
    set @StartWord = 0
    set @EndWord =  charindex(',',@Exclude,@StartWord)
      
    WHILE (@EndWord < len(@Exclude))
    BEGIN
        set @Loop = @Loop + 1
        SET @ThisWord = substring(@Exclude,@StartWord + 1,@EndWord - @StartWord - 1)
        --print '@ThisWord: ' + @ThisWord
          
        SET @StringDate =
            replace(
                @StringDate
                ,'[@#' + convert(varchar(10),@Loop) + '#@]'
                ,@ThisWord
            )
          
        set @StartWord = @EndWord
        --print '@StartWord: ' + convert(varchar(10),@StartWord)
        set @EndWord = charindex(',',@Exclude,@EndWord+1)
        --print '@EndWord: ' + convert(varchar(10),@EndWord)
    END
    set @Loop = @Loop + 1
    SET @ThisWord = substring(@Exclude,@StartWord + 1,@EndWord - @StartWord - 1)
    --print '@ThisWord: ' + @ThisWord
    SET @StringDate =
        replace(
            @StringDate
            ,'[@#' + convert(varchar(10),@Loop) + '#@]'
            ,@ThisWord
        )
    /*******************************************************/
      
    RETURN @StringDate
END

דוגמה לשימוש בפונקציה

select
    getdate()
    ,_ArielyAccessoriesDB.dbo.Ariely_FormatDate2String(
        getdate()
        ,'day: DD; Mounth: MM; Year:YYYY; Time: HH:MM:SS'
        ,'day,Mounth,Year,Time'
    )

הסבר על הפונקציה

ניתן למצוא באינטרנט פונקציות רבות וטובות לביצוע המרה של תאריך לפורמט טקסט אבל שום פונקציה אחרת שמצאתי באינטרנט אינה מאפשרת המרה מלאה לפורמט הכולל טקסט חופשי כמו פונקציה זו. הסיבה היא שההמרה לפורמט טקסט מבוצע תמיד על ידי ביצוע פעולות REPLACE למקטעים של טקסט בתבנית שלנו.

למשל אם יש לנו תבנית מהצורה dd/mm/yyyy אז מה שקורא בפועל בכל הפונקציות שמוצאים היום באינטרנט זה פעולה פשוטה של REPLACE לטקסטים מהצורה dd לחלק של היום בתאריך המקור שלנו, החלפה של mm לחודש של תאריך המקור שלנו והחלפה של yyyy לשנה של תאריך המקור שלנו. עד כאן הכל נראה טוב ולכן התוצאה שנקבל תהיה כמצופה: 27/02/2013

הערה: ישנם פונקציות יותר מורכבות המאפשרות החלפות של פורמטים נוספים כמו שעה, חודש, שנייה, אלפיות שנייה ועוד. ישנם אפילו פונקציות המאפשרות החלפה של פרמרטים שונים כמו m לציון חודש עם ספרה אחת ו mm לציון חודש עם 2 ספרות (אפס מקדים). כאמור האפשרות הן בילתי מוגבלות אבל תמיד בכל הפונקציות מדובר על ביצוע REPALCE פשוט של הטקסט המתאים לחלק מהתאריך שלנו בהתאם לתבנית שלנו.

בכל הפונקציות הנ"ל אם התבנית שלנו כוללת טקסט הזהה לזה שמוחלף על ידי הפונקציה הרי שטקסט זה יוחלף גם כן. למשל אם הפונקציה שלנו מחליפה סימון m במספר החודש ואנו רוצים לבנות תבנית מהצורה my mounth is m, הרי שהפונקציה תחליף לנו א כל המקומות בהם מופיע האות m במספר החודש. אבל אנחנו לא רוצים להמיר את המילה my למשל לחודש + האות y. זו היא מילה שמורה השייכת לתבנית שלנו

זה הסוד של הפונקציה שלי. בפונקציה זו אנחנו מכניסים פרמטר נוסף הכולל מילים שמורות. מילים אלו לא יעברו המרה לחלקי טקסט וישארו בתוצאה זהים לפורמט של התבנית. למשל בדוגמה מעל עלינו להעביר אל הפונקציה את המילה my מכיוון שהיא מיכלה m שהיא אות ייצוד לחודש, כמו כן נעביר את המילה mounth מסיבה זו.

הערה: ניתן למצוא באינטרנט פונקציות נוספות ומורכבות יותר לביצוע ההמרות של חלקי טקסט לחלקי תאריך. לכל הפונקציות הנ"ל ניתן לשלב את הלוגיקה שאני מביא כאן בצורה פשוטה על ידי העתקה של החלק הראשון והחלק האחרון של הפונקציה. כמו כן ניתן כמובן לשפר פוקציה זו ולהוסיף בחלק האמצעי של ההמרות כל המרה וכל פורמט נוסף שרוצים כמו למשל QQ שמצייג את מספר הרבעון בשנה. הפונקציה המובאת כאן היא רק הבסיס הראשוני

הפרמטרים

פונקציה זו מקבלת 3 פרמטרים:

  1. התאריך שרוצים להמיר לטקסט (יכול להגיע מטור בטבלה כמובן)
  2. תבנית הטקסט אליה רוצים להמיר את התאריך (יכול להגיע מטור בטבלה כמובן)
  3. מילים שמורות בתבנית שלנו שאנו רוצים לוודא שלא יעבורו המרה (וכמובן שוב... יכול להגיע מטור בטבלה כמובן). מילים אלו מגיעות בהפרדת פסיק

אז מה יש לנו בגדול?

הפונקציה מכילה 3 מקטעים:

  1. אנו מבצעים פעולת SPLIT לפרמטר השלישי שלנו
    1. לכל מילה שאנו מוצאים אנו מבצעיפם החלפה של המילה השמורה בסימון שישאר איתנו עד לסיום תהליך המרת התאריך
  2. אנו מצבעים המרה של התאריך לטקסט כפי שמצבעות כל הפונקציות הנפוצות (אבל אצלנו כאמור לא תבוצע פעולה זו על המילים השמורות מפני שהן עבור הסבה לפרמטר זמני שאינו מכיל אותיות בעיתיות)
    1. פעולה זו ניתן להחליף בכל פונקציה אחרת שמוצאים באינטרנט במצעת המרה מתאריך לטקסט או לחילופין להוסיף בחלק זה כל המרה נוספת שרוצים כמו למשל QQ שלא קיים בפונקציה מעל כרגע.
  3. המרה הפוכל של הסימונים שלנו אל המילים השמורות שלנו בהתאמה.

.

הערות, הארות ונקודות למחשבה

* הפונקציה המובאת כאן מבצעת פעולה מאוד כבדה של SPLIT תוך שימוש ב TSQL נקי. פעולה זו אינה סבירה ואינה טובה במסד נתונים פעיל. מומלץ מאוד להחליף חלק זה בפונקציית SPLIT של CLR

* הפונקציה מבצעת פעולה כפולה של לולאה זהה. היה נחמד מאוד לחסוף את הפעולה הכפולה המיותר בפעולה בודדת של SPLIT. אחרי הכל למה לבצע פעמיים את אותה לולאה. הדרך הפשוטה היא למשל בזמן ה SPLIT הראשון שלנו כבר לייצר את הפעולות שצריך לבצע בפעם השנייה. למשל היינו יכולים תיאורטית ליצור שרשרת של שאילתה בחלק ב SPLIT הראשון שלנו ואז במקום להריץ SPLIT נוסף פשוט להריץ שאילתה דינאמית בעזרת (למשל עם sp_executesql). מעשית לצערי מישהו במייקרוסופט חשב שיותר בטוח לא לסמוך על ה DBA ולכן לא לאפשר לנו להריץ שאילתות דינאמיות בתוך פונקציה ולכן בשרתי SQL הדבר אינו אפשרי בצורה זו. לשמחתי הפתרון פשוט בעיקר אם עברנו לעבוד עם CLR. אין בעיה להריץ פונקציות CLR בתוך פונקציה ואין בעיה ליצור שרשרת של שאילתה שנריץ אותה בעזרת CLR בחלק האחרון.

* מומלץ להימנע משימוש בפרמטר השלישי (ניתן להכניס בו NULL או שרשרת ריקה) או להקטין ככל האפשר את מספר המילים השמורות בתבנית שלנו. פעולה זו היא הפעולה הכבדה בכל הפונקציה.

* יש חשיבות לסדר המילים שמכניסים בפרמטר השלישי!

אם יש לנו מילה שמכילה מילה אחרת עלינו להכניס את המילה הדולה יותר ראשונה! למשל אם יש לנו abcdefg וגם את המילה cde ואיינו יכולים לצמצם למילה שמורה אחת בלבד אז בשרשרת המילים המשורות יופיעו המילים בדר abcdefg,cde. הסיבה היא שאנו מבצעים את ההחלפות לפי הסדר בו הן מופיעות בשרשרת המילים השמורות. אם נכתוב את המילים השמורות הפוך הרי שקודם תוחלף לנו המילה הקטנה cde ולכן המילה הגדולה בכלל לא תמצא בטקסט (הרי אין יותר cde ולכן אין יותר abcdefg)

* אם התבנית שלנו קבועה אז לפעמים אין הגיון לבצע את הפעולה של הפונקציה כל פעם מחדש עבור כל רשומה. הרבה יותר יעיל יהיה לפעמים לבצע את הפעולות בנפרד בעזרת פרוצדורה שמורה

מיטוב ופרקטיקה

ראינו אם כן שהבעיה המרכזית שלנו היא פעולות ה SPLIT ולכן עם מעט מחשבה ניתן לוותר עליהן כליל. הפונקציה הבאה עובדת בלוגיקה דומה אבל בצורה קשיחה יותר. במקום להעביר פרמטר שלישי של מילים שמורות נעזר בשיטה של שפות כמו JS ו C# ועוד לאיתור טקסטים מיוחדים. כאשר אנו מעבירים כתבנית טקסט את האות d למשל הפונקציה ממירה אותה ליום בתאריך. אם נרצה להעביר לפונקציה בתבנית את האות d ולא לבצע עליה המרה נוסיף לאות סימון מקובל של \ לפני האות וככה הפונקציה "תדע" לא לבצע עליה את הפעולה של ההחלפה.

הערה: בצורה ו גם עובדת פונקיית FORMAT שנוספה בשרת SQL 2012. אחרי הכל מדובר שם בפונקציית CLR פשוטה.

עבודה בשיטה הרבה הרבה הרבה יותר יעילה מצד אחד אבל אינה גמישה כמו בשיטה הקודם. עדין זו השיטה המועדת והמומלצת כל עוד היא מכסה את הצרכים (כל החסרונות של FORMAT נכללים בשיטה זו כמובן שמתבססת על הכנסת תבנית הכוללת סמנים מיוחדים)

FORMAT using the function Ariely_FormatDate2String_02

USE [_ArielyAccessoriesDB]
GO
/****** Object:  UserDefinedFunction [dbo].[Ariely_FormatDate2String]    Script Date: 07/10/2012 19:16:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
-- =============================================
-- Author:                  Ariely Ronen
-- Create date:             20/10/2011
-- Last Modified date:      13/07/2012
-- Description:
-- =============
-- This function takes a date as first parameter and return a string formated using the second parameter as formating pattern.
-- the pattern string can containing any parts of date at different locations in the string
-- and any string between the date parts.
-- According to the pattern format the function identifies the parts of the date
-- and replace these parts with the date parts from the first parameter.
-- since some string might look like date parts we can use the third parameter to pass words separated by commas
-- function will not identifies as date parts these words. See examples
--
-- This function is written in pure TSQL as i was asked to.
-- it is not optimal!!!
-- In fact personally I work with a similar function which the part of the loop is executed using CLR SPLIT,
-- without using any loop
-- =============================================
CREATE FUNCTION [dbo].[Ariely_FormatDate2String_02] (
    @Datetime DATETIME
    , @FormatMask NVARCHAR(100)
)
RETURNS NVARCHAR(100)
AS
BEGIN
    DECLARE @StringDate NVARCHAR(100)
    SET @StringDate = @FormatMask
      
    /*******************************************************/
    SET @StringDate = REPLACE(@StringDate, '\Y','[@#1#@]')
    SET @StringDate = REPLACE(@StringDate, '\M','[@#2#@]')
    SET @StringDate = REPLACE(@StringDate, '\D','[@#3#@]')
    /*******************************************************/
      
    DECLARE @Time time
    IF (CHARINDEX ('HH:MM:SS:MMM',@FormatMask) > 0)
        BEGIN
            SET @StringDate = REPLACE(@StringDate, 'HH:MM:SS:MMM',left(convert(nvarchar(100),convert(time,@Datetime)) + '000000000000',12))
            SET @FormatMask = replace('HH:MM:SS:MMM','**:**:**:***',@FormatMask)
        END
    IF (CHARINDEX ('HH:MM:SS',@FormatMask) > 0)
        BEGIN
            SET @StringDate = REPLACE(@StringDate, 'HH:MM:SS',left(convert(nvarchar(100),convert(time,@Datetime)) + '00000000',8))
            SET @FormatMask = replace('HH:MM:SS','**:**:**',@FormatMask)
        END
      
    IF (CHARINDEX ('YYYY',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'YYYY',DATENAME(YY, @Datetime))
    IF (CHARINDEX ('YY',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'YY',RIGHT(DATENAME(YY, @Datetime),2))
    IF (CHARINDEX ('Month',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'Month',DATENAME(MM, @Datetime))
    IF (CHARINDEX ('MON',@StringDate COLLATE SQL_Latin1_General_CP1_CS_AS)>0)
        SET @StringDate = REPLACE(@StringDate, 'MON',LEFT(UPPER(DATENAME(MM, @Datetime)),3))
    IF (CHARINDEX ('Mon',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'Mon',LEFT(DATENAME(MM, @Datetime),3))
    IF (CHARINDEX ('MM',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'MM',RIGHT('0'+CONVERT(VARCHAR,DATEPART(MM, @Datetime)),2))
    IF (CHARINDEX ('M',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'M',CONVERT(VARCHAR,DATEPART(MM, @Datetime)))
    IF (CHARINDEX ('DD',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'DD',RIGHT('0'+DATENAME(DD, @Datetime),2))
    IF (CHARINDEX ('D',@StringDate) > 0)
        SET @StringDate = REPLACE(@StringDate, 'D',DATENAME(DD, @Datetime))
      
      
    /*******************************************************/
    SET @StringDate = REPLACE(@StringDate,'[@#1#@]', 'Y')
    SET @StringDate = REPLACE(@StringDate,'[@#2#@]', 'M')
    SET @StringDate = REPLACE(@StringDate,'[@#3#@]', 'D')
    /*******************************************************/
  
    RETURN @StringDate
END
  
/*
  
*/
GO

SQL 2012

כל מה שיש במדריך זה לא שימושי בגרסת 2012. בגרסה 2012 יש פתרון פשוט לשמוש בפונקציה בשם FORMAT כולל הכנסת טקסט מכל צורה שרוצים. עם זה כאשר שולפים נתון ממסד הנתונים שכולל טקסט לפעמים השיטה המובאת כאן עדיין עדיפה מפני שעדיין עלינו לסמן מילים בעייתיות.

ב 2012 סימון המילים הבעייתיות יכול להיות הרבה יותר פשוט על ידי הוספת סימון \ (לפני כל אות כדי ללכת על בטוח או אם מתעצלים אז לפני אותיות בעייתיות בלבד כמו d). במקרה זה לא יהיה צורך בביצוע הניקוי בשלב שלישי, מפני שאנחנו לא נעזרים במילים שמורות שלנו שלנו לסימון אלא פשוט מגדירים ישירות בפונקציה. ז"א כל מה שיהיה עלינו לבצע זה פונקציה שונה לחלוטין של בניית שרשרת מתוקן משרשרת נוכחית על ידי הוספת סימונים בכל תוו או פשוט המשתמש יבצע את זה ידנית (מדובר בתבנית קבועה) ואז בכלל אין צורך בפרמטר השלישי.

דוגמאות לשימוש בפונקציית FORMAT בשרת SQL 2012 מהן אפשר להבין את הפתרון לבעיה שמוצגת כאן וכן שימוש כללי:

DECLARE @date DATETIME = '12/21/2011 12:01:22:113';
SELECT FORMAT ( @date, 'D', 'en-US' ) AS FormattedDate;
SELECT FORMAT ( @date, 'm', 'en-US' ) AS FormattedDate;
SELECT FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss t','en-US' ) AS FormattedDate;
SELECT FORMAT ( @date, 'h\:m\:ss\.ffffff', 'en-US' ) AS FormattedDate;
SELECT FORMAT ( @date, 'yyyy/MM/dd hh:mm:ss tt', 'en-US' ) AS FormattedDate;
SELECT FORMAT ( @date, 'yes i can yyyy/MM/dd hh:mm:ss tt', 'en-US' ) AS FormattedDate; --------------> בעיה!!!
SELECT FORMAT ( @date, '\ye\s i can yyyy/MM/dd hh:mm:ss tt', 'en-US' ) AS FormattedDate; --------------> פתרון

הפונקציה ההפוכה - הפיכת טקסט לתאריך

כדי לא להשאיר את הקורא באוויר הנה דוגמה פשוטה פונקציה הפוכה בה מפרמטים טקסט לתאריך. גם פונהקציה זו ניתן להרחיב בהתאם למוסבר במדריך זה כמובן

USE [_ArielyAccessoriesDB]
GO
/****** Object:  UserDefinedFunction [dbo].[Ariely_FormatString2Date]    Script Date: 07/10/2012 18:51:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  
-- =============================================
-- Author:                  Ariely Ronen
-- Create date:             20/10/2011
-- Last Modified date:      11/07/2012
-- Description:
-- =============
-- This function takes a string containing parts of date at different locations in the string.
-- According to the input format passed to the function, the function identifies the parts of the date
-- and returns a date which is built from these parts
--
-- =============================================
CREATE FUNCTION [dbo].[Ariely_FormatString2Date] (@StringDate VARCHAR (100), @FormatMask VARCHAR(100))
RETURNS DATETIME
AS
BEGIN
    DECLARE @Datetime DATETIME = GETDATE()
    DECLARE @Year int = 1900
    DECLARE @Month int = 1
    DECLARE @Day int = 1
    DECLARE @Time varchar(12) = ''
      
    IF (CHARINDEX ('HH:MM:SS:MMM',@FormatMask) > 0)
        BEGIN
            SET @Time = SUBSTRING(
                @StringDate,
                CHARINDEX('HH:MM:SS:MMM',@FormatMask),
                12
                )
            SET @FormatMask = replace('HH:MM:SS:MMM','**:**:**:***',@FormatMask)
        END
    ELSE IF (CHARINDEX ('HH:MM:SS',@FormatMask) > 0)
        BEGIN
            SET @Time = SUBSTRING(
                @StringDate,
                CHARINDEX('HH:MM:SS',@FormatMask),
                8
                )
            SET @FormatMask = replace('HH:MM:SS','**:**:**',@FormatMask)
        END
      
    IF (CHARINDEX ('YYYY',@FormatMask) > 0)
        SET @Year = SUBSTRING(
                              @StringDate,
                              CHARINDEX('YYYY',@FormatMask),
                              4
                              )
    ELSE IF (CHARINDEX ('YY',@FormatMask) > 0)
        SET @Year = SUBSTRING(
                              @StringDate,
                              CHARINDEX('YY',@FormatMask),
                              2
                              ) + 2000
      
    IF (CHARINDEX ('MM',@FormatMask) > 0)
        SET @Month = SUBSTRING(
                              @StringDate,
                              CHARINDEX('MM',@FormatMask),
                              2
                              )
    ELSE IF (CHARINDEX ('M',@FormatMask) > 0)
        BEGIN
            IF (
                    ISNUMERIC(SUBSTRING(@StringDate,CHARINDEX('M',@FormatMask),2)) = 1
                )
                BEGIN
                    SET @Month = SUBSTRING(@StringDate,CHARINDEX('M',@FormatMask),2)
                    SET @FormatMask = REPLACE(@FormatMask,'M','MM')
                END
            ELSE
                SET @Month = SUBSTRING(@StringDate,CHARINDEX('M',@FormatMask),1)
        END
  
    IF (CHARINDEX ('DD',@FormatMask) > 0)
        SET @Day = SUBSTRING(
                              @StringDate,
                              CHARINDEX('DD',@FormatMask),
                              2
                              )
    ELSE IF (CHARINDEX ('D',@FormatMask) > 0)
        BEGIN
            IF (
                    ISNUMERIC(SUBSTRING(@StringDate,CHARINDEX('D',@FormatMask),2)) = 1
                )
                SET @Day = SUBSTRING(@StringDate,CHARINDEX('D',@FormatMask),2)
            ELSE
                SET @Day = SUBSTRING(@StringDate,CHARINDEX('D',@FormatMask),1)
        END
      
    SET @Datetime = CONVERT(
                        DATETIME,
                        RIGHT('0000' + CONVERT(NVARCHAR(4),@Year),4) +
                        RIGHT('00'   + CONVERT(NVARCHAR(4),@Month),2) +
                        RIGHT('00'   + CONVERT(NVARCHAR(4),@Day  ),2) +
                        ' ' +
                        @Time
                        )
      
    RETURN @Datetime
END

סיכום

אז מה היה לנו כאן? הראיתי 2 פונקציות שונות לביצוע המרה של תאריך לטקסט על פי תבנית כלשהי.

מתי נבחר בכל אחת מהפונקציות? פונקציה שנייה תהיה תמיד ברירת המחדל מכיוון שהיא הרבה יותר מיטבית. בפונקציה ראשונה נעשה שימוש כאשר איננו יכולים לפרמט את תבנית הטקסט שלנו תוך הוספת סימוני ה \ בתווים בעייתיים. במקרה זה הפונקציה הראשונה מאפשרת לנו לעבוד עם תבנית טקסט נקי ולהעביר אל הפונקציה בנפרדת רשימה של מילים שמורות.

אני מקווה שקוד זה יעזור לכם, ובעיקר שההסברים יוכלו לעזור בפיתוחים נוספים. אם שיפרתם פונקציה זו אני אשמח לתגובות וקוד משופר. פרטי התקשרות נמצאים בעמוד "צור קשר". שאלות אנא לא לשלוח אלי ישירות אלא להעזר באחד הפורומים בהם אני תומך. כבררית מחדל אני נגד כל מגבלה של רשיון על קוד שנועד להפצה בחינם כקוד פתוח. לכן אני מפיץ את קודים כאלה (כמו כאן) בהתאם לרשיון הגמיש ביותר שאני מוצא - MIT או BSD (לבחירתכם). בגדול רשיון זה אומר: תעשו מה שאתם רוצים אבל אל תבואו אלי בתלונות ואין לי אחריות על כלום :-), עם זה אם מצאתם לנכון אתם מוזמנים להשאיר את השם שלי בפונקציה ולהגיע בעתיד לבדוק אם פירסמתי עדכון

.

.