SQL Server Date Time Convert - fabulouscode

    • Tuesday, October 2, 2018

    Home SQL SQL SERVER SQL Server Date Time Convert

    SQL Server Date Time Convert

    CAST DATE TUME

    CAST(convert(varchar,'2023/02/20',106)AS DATE) DeliveryDate

    Ref :: https://stackoverflow.com/questions/4011696/convert-datetime-in-sql-server 

    Replace Date

    To get "25-Oct-2010"

    REPLACE(CONVERT(Varchar(11), DATEADD(dd, DATEDIFF(dd, 0, '2023/02/20'), 0),113), ' ', '-')

    Assuming the value is supplied as a string, not a DATETIME data type:

    SELECT REPLACE(CONVERT(VARCHAR, CAST('2010-10-25 11:13:36.700' AS DATETIME), 106), ' ', '-')

    See the CAST/CONVERT documentation for other formats, though the one you requested requires post-processing.

    To get "2010-10-25 00:00:00.000"

    The best performing means is to use DATEADD & DATEDIFF:

    SELECT DATEADD(d, DATEDIFF(dd, 0, '2010-10-25 11:13:36.700'), 0)


    ============================================================================

    Week Start date/ Month Start-End  Date/Year Start-End Date
       
    Select CONVERT(varchar(50), GETDATE(),105) 'GETDATE' ,
           CONVERT(varchar(50), DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)),105) [WeekStart],
    CONVERT(varchar(50),DATEADD(DAY, 8 - DATEPART(WEEKDAY, GETDATE()), CAST(GETDATE() AS DATE)) ,105)[WeekEnd],
    CONVERT(varchar(50),DATEADD(dd, -DAY(getdate()) + 1, getdate()),105) MonthStart,
    CONVERT(varchar(50),DATEADD(dd, -DAY(DATEADD(mm, 1, getdate())), DATEADD(mm, 1, getdate())),105) MonthStart,
    CONVERT(varchar(50), DATEADD(q, DATEDIFF(q, 0, GETDATE()), 0),105)  AS 'QStart Date',      
    CONVERT(varchar(50), DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, GETDATE()) + 1,0)),105) AS 'QEnd Date',
    CONVERT(varchar(50), CAST(CAST(((((MONTH(GETDATE()) - 1) / 6) * 6) + 1) AS VARCHAR) + '-1-' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME),105) StartOfHalfYear,
    CONVERT(varchar(50),  CAST(CAST(((((MONTH(GETDATE()) - 1) / 6) * 6) + 6) AS VARCHAR) + '-1-' + CAST(YEAR(GETDATE()) AS VARCHAR) AS DATETIME),105)EndOfHalfYear,
    CONVERT(varchar(50), DATEADD(yy, DATEDIFF(yy,0,getdate()), 0),105) AS StartOfYear,
    CONVERT(varchar(50), DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1),105) AS EndOfYear



    Replace Date
    =================================
    REPLACE(CONVERT(VARCHAR(11), RequestDate, 106), ' ', '-') RequestDate -- Example-- (10-Dec-2020)

    Current Month Last Date & Number Formate
    ==================================
    SELECT DATENAME(month, GETDATE()) AS 'Month Name'
    SELECT CAST(eomonth(GETDATE()) AS datetime) LastDate

    SELECT RIGHT('00' + RTRIM( CAST( DATEPART( DAY, eomonth(GETDATE()) ) AS varchar(2)) ) , 2) LastDateNumber

    Current Month in number
    ============================
    SELECT datepart(mm, getdate()) MONTH_NUMBER_FORMAT

    Only Date in GateDate

    Select Convert(date, getdate())

    Calculate time difference in minutes in SQL Server



     SELECT CASE WHEN (AB.Acttime - (AB.Acttime / 3600 * 3600)) / 60 > 60 THEN '60' ELSE CONVERT(varchar(2), (AB.Acttime - (AB.Acttime/ 3600 * 3600)) / 60) 

    END + ':' + CASE WHEN AB.Acttime- ((AB.Acttime/ 3600 * 3600) + (((AB.Acttime- (AB.Acttime / 3600 * 3600)) / 60) * 60)) > 60 THEN '60' ELSE CONVERT(varchar(2), 
    AB.Acttime - ((AB.Acttime / 3600 * 3600) + (((AB.Acttime - (AB.Acttime / 3600 * 3600)) / 60) * 60))) END AS TotalRunTimeHourMin
    FROM(
    SELECT DATEDIFF(MINUTE, B.ActualStartTime, B.ActualEndTime) Acttime
    FROM 
    dbo.tblScheduleMaintenance B
    WHERE B.SM_Id=16027) AB







    -- Last 3 months form gatedate()

    SELECT * 
FROM TABLE_NAME
WHERE Date_Column >= DATEADD(MONTH, -3, GETDATE())

    -- add 10 days form gatedate()
      Select Convert (datetime,convert(varchar,getdate(),23)) + 10
     Select Convert (datetime,convert(varchar,getdate(),23)) -10

    CAST(CONVERT(VARCHAR, '2018-02-28', 101) AS DATETIME) 

     WHERE        (CAST(CONVERT(VARCHAR, PM.StartDaetime, 101) AS DATETIME) BETWEEN CONVERT(DATETIME, '2018-02-28', 102) AND CONVERT(DATETIME, '2018-02-28', 102)))

    -- Convert string to date using style (format) numbers - sql dates format
    -- SQL convert text to date - SQL convert string to date / datetime
    SELECT convert(datetime,'15/03/18',3) -- 2018-03-15 00:00:00.000
    SELECT convert(datetime,'15.03.18',4) -- 2018-03-15 00:00:00.000

    -- Convert datetime to text style (format) list - sql time format
    -- SQL Server without century (YY) date styles (there are exceptions!)
    -- Generally adding 100 to style number results in century format CCYY / YYYY
    SELECT convert(varchar,getdate())    -- Mar 15 2018 10:35AM
    SELECT convert(varchar,getdate(),0)  -- Mar 15 2018 10:35AM
    SELECT convert(varchar,getdate(),1)  -- 03/15/18
    SELECT convert(varchar,getdate(),2)  -- 18.03.15
    SELECT convert(varchar,getdate(),3)  -- 15/03/18
    SELECT convert(varchar,getdate(),4)  -- 15.03.18
    SELECT convert(varchar,getdate(),5)  -- 15-03-18
    SELECT convert(varchar,getdate(),6)  -- 15 Mar 18
    SELECT convert(varchar,getdate(),7)  -- Mar 15, 18
    SELECT convert(varchar,getdate(),8)  -- 10:39:39
    SELECT convert(varchar,getdate(),9)  -- Mar 15 2018 10:39:48:373AM
    SELECT convert(varchar,getdate(),10) -- 03-15-18
    SELECT convert(varchar,getdate(),11) -- 18/03/15
    SELECT convert(varchar,getdate(),15) -- 180315
    SELECT convert(varchar,getdate(),13) -- 15 Mar 2018 10:41:07:590
    SELECT convert(varchar,getdate(),14) -- 10:41:25:903
    SELECT convert(varchar,getdate(),20) -- 2018-03-15 10:43:56
    SELECT convert(varchar,getdate(),21) -- 2018-03-15 10:44:04.950
    SELECT convert(varchar,getdate(),22) -- 03/15/18 10:44:50 AM
    SELECT convert(varchar,getdate(),23) -- 2018-03-15
    SELECT convert(varchar,getdate(),24) -- 10:45:45
    SELECT convert(varchar,getdate(),25) -- 2018-03-15 10:46:11.263

    -- T-SQL with century (YYYY or CCYY) datetime styles (formats)
    SELECT convert(varchar, getdate(), 100) -- Oct 23 2016 10:22AM (or PM)
     SELECT convert(varchar, getdate(), 101) -- 10/23/2016
    SELECT convert(varchar, getdate(), 102) -- 2016.10.23
    SELECT convert(varchar, getdate(), 103) -- 23/10/2016
    SELECT convert(varchar, getdate(), 104) -- 23.10.2016
    SELECT convert(varchar, getdate(), 105) -- 23-10-2016
    SELECT convert(varchar, getdate(), 106) -- 23 Oct 2016
    SELECT convert(varchar, getdate(), 107) -- Oct 23, 2016
    SELECT convert(varchar, getdate(), 108) -- 09:10:34
    SELECT convert(varchar, getdate(), 109) -- Oct 23 2016 11:10:33:993AM (or PM)
    SELECT convert(varchar, getdate(), 110) -- 10-23-2016
    SELECT convert(varchar, getdate(), 111) -- 2016/10/23
    SELECT convert(varchar, getdate(), 112) -- 20161023
    SELECT convert(varchar, getdate(), 113) -- 23 Oct 2016 06:10:55:383
    SELECT convert(varchar, getdate(), 114) -- 06:10:55:383(24h)
    SELECT convert(varchar, getdate(), 120) -- 2016-10-23 06:10:55(24h)
    SELECT convert(varchar, getdate(), 121) -- 2016-10-23 06:10:55.383
    SELECT convert(varchar, getdate(), 126) -- 2016-10-23T06:10:55.383
    GO
    -- SQL cast string to datetime - time part 0 - sql hh mm
    -- SQL Server cast string to DATE (SQL Server 2008 feature) - sql yyyy mm dd
    SELECT [Date] =     CAST('20120228' AS date)         -- 2012-02-28
    SELECT [Datetime] = CAST('20120228' AS datetime)      -- 2012-02-28 00:00:00.000
    SELECT [Datetime] = CAST('20120228' AS smalldatetime) -- 2012-02-28 00:00:00

    -- SQL convert string to datetime - time part 0
    -- SQL Server convert string to date - sql times format
    SELECT [Datetime] = CONVERT(datetime,'2010-02-28')
    SELECT [Datetime] = CONVERT(smalldatetime,'2010-02-28')

    SELECT [Datetime] = CAST('Mar 15, 2010' AS datetime)
    SELECT [Datetime] = CAST('Mar 15, 2010' AS smalldatetime)

    SELECT [Datetime] = CONVERT(datetime,'Mar 15, 2010')
    SELECT [Datetime] = CONVERT(smalldatetime,'Mar 15, 2010')

    SELECT [Datetime] = CAST('Mar 15, 2010 12:07:34.444' AS datetime)
    SELECT [Datetime] = CAST('Mar 15, 2010 12:07:34.444' AS smalldatetime)

    SELECT [Datetime] = CONVERT(datetime,'Mar 15, 2010 12:07:34.444')
    SELECT [Datetime] = CONVERT(smalldatetime,'Mar 15, 2010 12:07:34.444')

    SELECT [Datetime] = CAST('2010-02-28 12:07:34.444' AS datetime)
    SELECT [Datetime] = CAST('2010-02-28 12:07:34.444' AS smalldatetime)

    SELECT [Datetime] = CONVERT(datetime,'2010-02-28 12:07:34.444')
    SELECT [Datetime] = CONVERT(smalldatetime,'2010-02-28 12:07:34.444')

    -- Double conversion
    SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS datetime)
    SELECT [Datetime] = CAST(CAST(getdate() AS VARCHAR) AS smalldatetime)

    SELECT [Datetime] = CONVERT(datetime,convert(varchar,getdate()))
    SELECT [Datetime] = CONVERT(smalldatetime,convert(varchar,getdate()))
    ------------

    -- MSSQL convert date string to datetime - time is set to 00:00:00.000 or 12:00AM
    PRINT CONVERT(datetime,'07-10-2016',110)        -- Jul 10 2016 12:00AM
    PRINT CONVERT(datetime,'2016/07/10',111)        -- Jul 10 2016 12:00AM
    PRINT CONVERT(varchar,CONVERT(datetime,'20160710',  112),121)          
    -- 2016-07-10 00:00:00.000        
    ------------     

    -- Selected named date styles
    DECLARE @DateTimeValue varchar(32)

    -- US-Style
    -- Convert string to datetime sql - sql convert string to datetime
    SELECT @DateTimeValue = '10/23/2016'
    SELECT StringDate=@DateTimeValue,
    [US-Style] = CONVERT(datetime, @DatetimeValue)

    SELECT @DateTimeValue = '10/23/2016 23:01:05'
    SELECT StringDate = @DateTimeValue,
    [US-Style] = CONVERT(datetime, @DatetimeValue)

    -- UK-Style, British/French
    SELECT @DateTimeValue = '23/10/16 23:01:05'
    SELECT StringDate = @DateTimeValue,
    [UK-Style] = CONVERT(datetime, @DatetimeValue, 3)

    SELECT @DateTimeValue = '23/10/2016 04:01 PM'
    SELECT StringDate = @DateTimeValue,
    [UK-Style] = CONVERT(datetime, @DatetimeValue, 103)

    -- German-Style
    SELECT @DateTimeValue = '23.10.16 23:01:05'
    SELECT StringDate = @DateTimeValue,
    [German-Style] = CONVERT(datetime, @DatetimeValue, 4)


    SELECT @DateTimeValue = '23.10.2016 04:01 PM'
    SELECT StringDate = @DateTimeValue,
    [German-Style] = CONVERT(datetime, @DatetimeValue, 104)


    -- Double conversion to US-Style 107 with century: Oct 23, 2016
    SET @DateTimeValue='10/23/16'
    SELECT StringDate=@DateTimeValue,
    [US-Style] = CONVERT(varchar, CONVERT(datetime, @DateTimeValue),107)

    -- SQL dateformat setting
    USE AdventureWorks2008;
    SELECT convert(datetime,'14/05/08')
    /* Msg 242, Level 16, State 3, Line 1
    The conversion of a varchar data type to a datetime data type resulted
    in an out-of-range value.
    */
    SET DATEFORMAT ymd
    SELECT convert(datetime,'14/05/08')   -- 2014-05-08 00:00:00.000
    -- Setting DATEFORMAT to UK-Style
    SET DATEFORMAT dmy
    SELECT convert(datetime,'20/05/14')   -- 2014-05-20 00:00:00.000
    -- Setting DATEFORMAT to US-Style
    SET DATEFORMAT mdy
    SELECT convert(datetime,'05/20/14')   -- 2014-05-20 00:00:00.000
    SELECT convert(datetime,'05/20/2014') -- 2014-05-20 00:00:00.000
    GO

    ------------
    -- SQL date & time eliminating dividing characters
    ------------
    -- MSSQL replace string function
    -- T-SQL string concatenate (+)
    USE AdventureWorks2008;
    SELECT replace(convert(VARCHAR(10),getdate(),102),'.','')
    -- 20120315
    SELECT replace(convert(VARCHAR(10),getdate(),111),'/','')
    -- 20120315  
    -- SQL triple replace
    SELECT replace(replace(replace(convert(VARCHAR(25),
           getdate(),20),'-',''), ':',''),' ','')
    -- 20120529090427
    -- T-SQL concatenating from a date and a time conversion
    SELECT replace(convert(VARCHAR(10),getdate(),111),'/','') +
           replace(convert(VARCHAR(8),getdate(),108),':','')
    -- 20120315085654

    ------------
    -- Converting string dates from a table
    ------------

    -- Create and populate a test table with a string date
    USE tempdb;
    SELECT
          DepartmentID,
          LastUpdate=CONVERT(varchar,
                     dateadd(day, DepartmentID, ModifiedDate),100)
    INTO DeptInfo
    FROM AdventureWorks.HumanResources.Department

    SELECT * FROM DeptInfo
    /* Partial results

    DepartmentID      LastUpdate
    1                       Jun  2 1998 12:00AM
    2                       Jun  3 1998 12:00AM
    */

    -- Convert string date column to datetime
    SELECT
          DepartmentID,
          LastChangeDate=convert(datetime, LastUpdate)
    FROM DeptInfo
    /* Partial results

    DepartmentID            LastChangeDate
    1                       1998-06-02 00:00:00.000
    2                       1998-06-03 00:00:00.000
    */
    DROP TABLE DeptInfo
    GO

    -------------------------------------------------------
    -- Casting string date & time together and separately
    -------------------------------------------------------
    -- SQL cast string to datetime
    SELECT CAST('20100315 16:40:31' AS datetime)
    -- Result: 2010-03-15 16:40:31.000

    -- SQL cast string to date - time part 0  
    SELECT CAST('20100315' AS datetime)
    -- Result: 2010-03-15 00:00:00.000

    -- SQL cast string to time - date part 1900-01-01
    SELECT CAST('16:40:31' AS smalldatetime)
    -- Result: 1900-01-01 16:41:00
    ------------
    -- SQL DATEDIFF with string date
    ------------
    DECLARE @sDate varchar(10)
    SET @sDate = '2010/03/15'
    -- DATEDIFF (delta) between two dates in months
    SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), @sDate)
    SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CAST(@sDate as datetime))
    SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate))
    SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,111))
    -- Seme results for above: 2008-12-29 11:04:51.097    15

    -- SQL convert to datetime with wrong style (111 correct, 112 incorrect)
    SELECT GETDATE(), DATEDIFF (MONTH, GETDATE(), CONVERT(datetime,@sDate,112))
    /* ERROR

    Msg 241, Level 16, State 1, Line 11
    Conversion failed when converting date and/or time from character string.
    */
    ------------
    ------------
    -- SQL Server date string search guidelines - comparing dates
    ------------
    -- Date equal search
    DECLARE @Date1 datetime, @Date2 datetime, @Date3 datetime
    SET @Date1 = '2012-01-01'
    SET @Date2 = '2012-01-01 00:00:00.000'
    SET @Date3 = '2012-01-01 11:00'

    SELECT @Date1, @Date2, @Date3
    -- Date-only @Date1 is translated to datetime
    -- 2012-01-01 00:00:00.000    2012-01-01 00:00:00.000  2012-01-01 11:00:00.000

    -- The following is a datetime comparison, not a date-only comparison
    IF (@Date1 = @Date2) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'
    -- EQUAL

    -- Equal test fails because time parts are different
    IF (@Date1 = @Date3) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'
    -- NOT EQUAL
    -- The string date implicitly converted to datetime for the equal test
    IF ('2012-01-01' = @Date3) PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL'
    -- NOT EQUAL

    -- Safe way to search for a specific date
    SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
    WHERE '2004/02/01' = CONVERT(varchar, OrderDate,111)
    -- 244

    -- Equivalent to
    SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
    WHERE OrderDate BETWEEN '2004/02/01  00:00:00.000' AND '2004/02/01 23:59:59.997'
    -- 244


    -- Safe way to search for a specific date range
    SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
    WHERE CONVERT(varchar, OrderDate,111) BETWEEN '2004/02/01' AND '2004/02/14'
    -- 1059

    -- Equivalent to
    SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
    WHERE OrderDate BETWEEN '2004/02/01 00:00:00.000' AND '2004/02/14 23:59:59.997'
    -- 1059
    SELECT COUNT(*) FROM AdventureWorks.Sales.SalesOrderHeader
    WHERE OrderDate >= '2004/02/01 00:00:00.000'
      AND OrderDate <  '2004/02/15 00:00:00.000'
    -- 1059 
    ------------
    ------------
    -- SQL Server convert from string to smalldatetime
    ------------
    -- T-SQL convert from format mm/dd/yyyy to smalldatetime
    SELECT CONVERT(smalldatetime, '10/23/2016', 101)
    -- 2016-10-23 00:00:00
    -- MSSQL convert from format dd/mm/yyyy to smalldatetime
    SELECT CONVERT(smalldatetime, '23/10/2016', 103)
    -- 2016-10-23 00:00:00
    -- Month 23 is out of range
    SELECT CONVERT(smalldatetime, '23/10/2016', 101)
    /* Msg 242, Level 16, State 3, Line 1
    The conversion of a varchar data type to a smalldatetime data type resulted
    in an out-of-range value.
    */
    ------------
    -- Translate/convert string/text hours and minutes to seconds
    ------------
    DECLARE @TimeStr varchar(16) = '20:49:30'
    SELECT   PARSENAME(REPLACE(@TimeStr,':','.'),1)
           + PARSENAME(REPLACE(@TimeStr,':','.'),2) * 60
           + PARSENAME(REPLACE(@TimeStr,':','.'),3) * 3600
    -- 74970
    ------------


    Tags
    Author Image

    About Author::
    I am a Software Developer, expert in SEO, ASP.NET, MVC, MSSQL, PHP (OPP), PHP (MVC) with MySQL,Java, Android, I have working experience in rapid software design and development of Microsoft and Oracle based desktop, web, Mobile applications and services including SQL Server and Oracle database design and optimization, API development and devices integration. I have strong background and understanding of OOP and MVC Framework, which make my skills more concrete. Combined with a vast knowledge of technologies such as C#, ASP.Net MVC, WCF/ Web API, Entity Framework, SQL, SSRS, SSIS, Crystal Reports, RDLC, AngularJs, Knockout, Bootstrap, Telerik, Ajax, Javascript, JQuery, HTML5, CSS3 , ASP.net Zero and more, I am able to offer Python time-lines using a balance of innovative solutions and tools.
    Regards,
    Safiqul Islam Tuhin
    Founder Fabulous Code

    By at
    Labels: ,

    1 comment:

    1. BEAUTIFULLY WHIPPED BEAUTYAugust 5, 2021 at 12:56 AM

      This article of yours has been very useful for my development. Thank you very much.
      loren

      ReplyDelete

    I am Safiqul Islam Tuhin

    Subscribe to: Post Comments (Atom)