Thursday, January 26, 2017

Error Method not found: '!!0[] System.Array.Empty()'. or Microsoft.VisualStudio.Web.PageInspector.Runtime 

this error showing then try below code Method not found: '!!0[] System.Array.Empty()'. or Microsoft.VisualStudio.Web.PageInspector.Runtime
this work for me
<compilation targetFramework="4.5"> 
    <assemblies> 
        <remove assembly="Microsoft.VisualStudio.Web.PageInspector.Loader,
    Version=1.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    </assemblies> 
</compilation>
Posted by at No comments:

Tuesday, March 1, 2016

iframe with external page not working in asp.net

iframe with external page not working asp.net then use object
<object type="text/html" data="http://www.google.co.in" style="width:100%; height:100%">

 or
<script>$("#LoadUrl").load("http://www.google.co.in");</script>
<div id="LoadUrl"></div>

 or 

 Add this to header part of page or master page

 <meta http-equiv="X-Frame-Options" content="allow" />

 Add this to page body

   <iframe name="myIframe" id="myIframe" style="height: 500px; width: 100%;" runat="server" src="http://google.co.in/"></iframe>
Posted by at No comments:

Tuesday, February 23, 2016

Placing multiple markers on a Google Map

Placing multiple markers on a Google Map 

The HTML

<div id="map_wrapper">
    <div id="map_canvas" class="mapping"></div>
</div>

The CSS

#map_wrapper {
    height: 400px;
}

#map_canvas {
    width: 100%;
    height: 100%;
}

The JS

jQuery(function($) {
    // Asynchronously Load the map API
    var script = document.createElement('script');
    script.src = "http://maps.googleapis.com/maps/api/js?sensor=false&callback=initialize";
    document.body.appendChild(script);
});

function initialize() {
    var map;
    var bounds = new google.maps.LatLngBounds();
    var mapOptions = {
        mapTypeId: 'roadmap'
    };
                   
    // Display a map on the page
    map = new google.maps.Map(document.getElementById("map_canvas"), mapOptions);
    map.setTilt(45);
       
    // Multiple Markers
    var markers = [
        ['London Eye, London', 51.503454,-0.119562],
        ['Palace of Westminster, London', 51.499633,-0.124755]
    ];
                       
    // Info Window Content
    var infoWindowContent = [
        ['<div class="info_content">' +
        '<h3>London Eye</h3>' +
        '<p>The London Eye is a giant Ferris wheel situated on the banks of the River Thames. The entire structure is 135 metres (443 ft) tall and the wheel has a diameter of 120 metres (394 ft).</p>' +        '</div>'],
        ['<div class="info_content">' +
        '<h3>Palace of Westminster</h3>' +
        '<p>The Palace of Westminster is the meeting place of the House of Commons and the House of Lords, the two houses of the Parliament of the United Kingdom. Commonly known as the Houses of Parliament after its tenants.</p>' +
        '</div>']
    ];
       
    // Display multiple markers on a map
    var infoWindow = new google.maps.InfoWindow(), marker, i;
   
    // Loop through our array of markers & place each one on the map 
    for( i = 0; i < markers.length; i++ ) {
        var position = new google.maps.LatLng(markers[i][1], markers[i][2]);
        bounds.extend(position);
        marker = new google.maps.Marker({
            position: position,
            map: map,
            title: markers[i][0]
        });
       
        // Allow each marker to have an info window   
        google.maps.event.addListener(marker, 'click', (function(marker, i) {
            return function() {
                infoWindow.setContent(infoWindowContent[i][0]);
                infoWindow.open(map, marker);
            }
        })(marker, i));

        // Automatically center the map fitting all markers on the screen
        map.fitBounds(bounds);
    }

    // Override our map zoom level once our fitBounds function runs (Make sure it only runs once)
    var boundsListener = google.maps.event.addListener((map), 'bounds_changed', function(event) {
        this.setZoom(14);
        google.maps.event.removeListener(boundsListener);
    });
   

}
Posted by at No comments:

wkhtmltopdf convert HTML document to Pdf

How do I use it?
  1. Download wkhtmltopdf.exe
  2. Create your HTML document that you want to convert into a PDF
  3. Run your HTML document through the tool.

Create Class WKHtmlToPdfClass.vb

Download wkhtmltopdf.exe and save in Folder
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Security
Imports System.Web
Imports System.Web.Hosting
Imports System.IO
Imports System.Data
Imports System.Data.SqlClient
Public Class WKHtmlToPdfClass
    Public Shared Function WKHtmlToPdf(ByVal url As String) As Byte()
        Dim fileName = " - "
        Dim wkhtmlDir = "C:\"
        Dim wkhtml = HttpContext.Current.Server.MapPath("~/FolderName/") & "wkhtmltopdf.exe"
        Dim p = New Process
        p.StartInfo.CreateNoWindow = True
        p.StartInfo.RedirectStandardOutput = True
        p.StartInfo.RedirectStandardError = True
        p.StartInfo.RedirectStandardInput = True
        p.StartInfo.UseShellExecute = False
        p.StartInfo.FileName = wkhtml
        p.StartInfo.WorkingDirectory = wkhtmlDir
        Dim switches As String = ""
        switches = (switches + "--print-media-type ")
        switches = (switches + "--margin-top 10mm --margin-bottom 10mm --margin-right 10mm --margin-left 10mm ")
        switches = (switches + "--page-size Letter ")
        p.StartInfo.Arguments = (switches + (" " _
                    + (url + (" " + fileName))))
        p.Start()
        'read output
        Dim buffer() As Byte = New Byte((32768) - 1) {}
        Dim file() As Byte
        Dim ms = New MemoryStream

        While True
            Dim read As Integer = p.StandardOutput.BaseStream.Read(buffer, 0, buffer.Length)
            If (read <= 0) Then
                Exit While
            End If

            ms.Write(buffer, 0, read)

        End While

        file = ms.ToArray
        ' wait or exit
        p.WaitForExit(60000)
        ' read the exit code, close process
        Dim returnCode As Integer = p.ExitCode
        p.Close()
        'Return (returnCode = 0)
        Return If(returnCode = 0, file, Nothing)
        'TODO: Warning!!!, inline IF is not supported ?
        'TODO: Warning!!!! NULL EXPRESSION DETECTED...

    End Function
   
End Class

Create default.aspx.vb
    Public Sub HTMLtoPDF()

        Dim url = "http://yoursite/FolderName/test.html"         
Dim file = WKHtmlToPdfClass.WKHtmlToPdf(url, TemplateName)
        If (Not (file) Is Nothing) Then
            Response.ContentType = "Application/pdf"
            Response.BinaryWrite(file)
            Response.End()
        End If
       
    End Sub


Posted by at No comments:

HTML Table to Save File in Asp.Net

    Public Sub SaveHTMLTabletoFile()
        Try

            Dim sb As StringBuilder = New StringBuilder()
            sb.Clear()
            sb.AppendLine("<table id='tbl1' border='0'>")
            sb.AppendLine("<tr>")
            sb.AppendLine("<td>Test 1</td>")
            sb.AppendLine("</tr>")
            sb.AppendLine("<tr>")
            sb.AppendLine("<td>Test 2</td>")
            sb.AppendLine("</tr>")
            sb.AppendLine("</table>")

            Dim path As String = Server.MapPath("download")
            If Not Directory.Exists(path) Then
                Directory.CreateDirectory(path)
            End If

            Dim strPath As String = Server.MapPath("download/test.html")

            Dim fStream As FileStream = File.Create(strPath)
            fStream.Close()
            Dim sWriter As New StreamWriter(strPath)
            sWriter.Write(sb)

            sWriter.Close()

        Catch ex As Exception
            Console.Write(ex.Message)
        End Try

    End Sub
Posted by at No comments:

File Transfer with FTP in Asp.Net

Create Page Default.aspx.vb
Write Method 
    Public Sub FTPFileTransfer()
        Dim objFtp As New FTP.FtpClass()
        Dim filePath As String = Server.MapPath("download/test.txt")
        objFtp.FtpUrl = "ftp://www.yoursite.com/FolderName"
        objFtp.Ftppassword = "Ftppassword"
        objFtp.Ftpusername = "Ftpusername"
        If objFtp.UploadFileToFTP(filePath) Then
            lblMessage.Text = "File has been uploaded on the server <br/> Check the uploaded file on http://yoursite.com/" & Path.GetFileName(filePath)
        Else
            Console.Write(objFtp.FtpException.Message)
        End If
    End Sub

Create Class FtpClass 
Write Method 
Imports System.Reflection
Imports System.Data
Imports System.IO
Imports System.Net
Imports System.Text

Public Class FtpClass 
    Public Property FtpUrl() As String
        Get
            Return m_FtpUrl
        End Get
        Set(value As String)
            m_FtpUrl = value
        End Set
    End Property
    Private m_FtpUrl As String
    Public Property Ftpusername() As String
        Get
            Return m_Ftpusername
        End Get
        Set(value As String)
            m_Ftpusername = value
        End Set
    End Property
    Private m_Ftpusername As String
    Public Property Ftppassword() As String
        Get
            Return m_Ftppassword
        End Get
        Set(value As String)
            m_Ftppassword = value
        End Set
    End Property
    Private m_Ftppassword As String
    Public Property FtpException() As Exception
        Get
            Return m_FtpException
        End Get
        Set(value As Exception)
            m_FtpException = value
        End Set
    End Property
    Private m_FtpException As Exception

    Public Function UploadFileToFTP(source As String) As Boolean
        Try

            Dim filename As String = Path.GetFileName(source)
            Dim ftpfullpath As String = FtpUrl
            ' +"/aceafrica";
            Dim ftp As FtpWebRequest = DirectCast(FtpWebRequest.Create(ftpfullpath & "/" & filename), FtpWebRequest)
            ftp.Credentials = New NetworkCredential(Ftpusername, Ftppassword)

            ftp.KeepAlive = True
            ftp.UseBinary = True
            ftp.Method = WebRequestMethods.Ftp.UploadFile

            Dim fs As FileStream = File.OpenRead(source)
            Dim buffer As Byte() = New Byte(fs.Length - 1) {}
            fs.Read(buffer, 0, buffer.Length)
            fs.Close()

            Dim ftpstream As Stream = ftp.GetRequestStream()
            ftpstream.Write(buffer, 0, buffer.Length)
            ftpstream.Close()
            Return True
        Catch ex As Exception
            FtpException = ex
            Return False
        End Try
    End Function

    Public Function GetFileList(Directory As String) As Boolean
        ' Public Function GetFileList(Directory As String) As List(Of String)
        Dim Files As New List(Of String)()
        Dim filename As String = Path.GetFileName(Directory)
        Dim ftpfullpath As String = FtpUrl
        Dim request As FtpWebRequest = DirectCast(WebRequest.Create(New Uri(ftpfullpath & "/" & filename)), FtpWebRequest)
        request.Method = WebRequestMethods.Ftp.ListDirectory

        request.Credentials = New NetworkCredential(Ftpusername, Ftppassword)
        ' Is this correct?
        ' request.Credentials = new NetworkCredential(ServerInfo.Username, ServerInfo.Password); // Or may be this one?
        request.UseBinary = False
        request.UsePassive = True

        Dim response As FtpWebResponse = DirectCast(request.GetResponse(), FtpWebResponse)

        Dim responseStream As Stream = response.GetResponseStream()
        Dim reader As New StreamReader(responseStream)
        Dim CurrentLine As String = reader.ReadLine()
        While Not String.IsNullOrEmpty(CurrentLine)
            Files.Add(CurrentLine)
            CurrentLine = reader.ReadLine()
        End While
        reader.Close()
        response.Close()
        Return True
    End Function
End Class


Posted by at No comments:

Adding Parameters to Commands

         Adding Parameters to Commands

          ' Method 1
            cmd.Parameters.Add("@ID", SqlDbType.Int)
            cmd.Parameters("@ID").Value = ID

            ' Method 2
            cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID

            ' Method 3
            cmd.Parameters.AddWithValue("@ID", ID)

            ' Method 4
            Dim param As SqlParameter = New SqlParameter
            param.ParameterName = "@ID"
            param.Value = ID
            cmd.Parameters.Add(param)
Posted by at No comments:

Monday, February 2, 2015

Create Cursor and execute update query with local variable


GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- exec GenQuery tablename


CREATE PROCEDURE [dbo].[GenQuery]  

  @tablename sysname
as


Declare @Value1 NVarchar(8)
Declare @Value2 NVarchar(8)
Declare @Value3 Nchar(8)
Declare @tablequery NVarchar(100)

BEGIN

--- Start Cursor  -----
DECLARE CURt CURSOR FOR

Select Value1,Value2,Value3 From tablename

OPEN CURt
FETCH NEXT FROM CURt INTO @Value1,@Value2,@Value3
WHILE @@FETCH_STATUS = 0
BEGIN
Set @tablequery = N'update ' + @tablename + ' Set Value1= ' + @Value1 +',Value2= ' + @Value2 + ' where Value3= ' + @Value3 + ' '
execute sp_executesql @tablequery
FETCH NEXT FROM CURt INTO@Value1,@Value2,@Value3
END
CLOSE CURt
DEALLOCATE CURt
--- End Cursor  -----
END




 

Posted by at No comments:

Tuesday, December 30, 2014

Execute SQL Script Using SQLCMD Command Line in SQL Server


Large script files not execute in SQL Server Management Studio.

 so we can execute it Using SQLCMD Command Line.

 SQLCMD command line Utility is an alternative way to execute SQL scripts using the command line. 
This allows us to execute SQL scripts without touching SQL Server Management Studio.
1. Click Start button , 
2. open Run command and write below query in to Run Command and Click ok button.

 3. sqlcmd -S Servername -i C:\Scriptname.sql
4. after write query into Run Command, 
   Open Sql Command Prompt and Execute query in to Command Prompt.

ex.

 below code save it as Scriptname.sql file under the C:\Scriptname.sql

 USE Master
GO

 IF EXISTS ( SELECT [name] FROM sys.databases WHERE [name] = 'DBEmployee' )
DROP DATABASE DBCompany
GO

 CREATE DATABASE DBEmployee 
GO

 USE DBEmployee  
GO

 IF EXISTS ( SELECT [name] FROM sys.tables where [name]='EmployeeMaster' )
DROP TABLE Employee
GO

 CREATE TABLE EmployeeMaster
(
EmpId int,
Fname varchar(40),
Lname varchar(40),
Hiredate datetime,
Salary int
)
GO

 INSERT INTO EmployeeMasterVALUES (1,'Akash','Patel','12/31/2014',5000);

I have to get whole database script 983 MB size, executed script approx 2 hrs.  

Posted by at No comments:

Wednesday, August 13, 2014

Date Format in Sql


Standard Date Formats
Date Format
Standard
SQL Statement
Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
Jan 1 2005 1:29PM 1
MM/DD/YY
USA
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
11/23/98
MM/DD/YYYY
USA
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
11/23/1998
YY.MM.DD
ANSI
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
72.01.01
YYYY.MM.DD
ANSI
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
1972.01.01
DD/MM/YY
British/French
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
19/02/72
DD/MM/YYYY
British/French
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
19/02/1972
DD.MM.YY
German
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
25.12.05
DD.MM.YYYY
German
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
25.12.2005
DD-MM-YY
Italian
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
24-01-98
DD-MM-YYYY
Italian
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
24-01-1998
DD Mon YY 1
-
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
04 Jul 06 1
DD Mon YYYY 1
-
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
04 Jul 2006 1
Mon DD, YY 1
-
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
Jan 24, 98 1
Mon DD, YYYY 1
-
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
Jan 24, 1998 1
HH:MM:SS
-
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1
Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
Apr 28 2006 12:32:29:253PM 1
MM-DD-YY
USA
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
01-01-06
MM-DD-YYYY
USA
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
01-01-2006
YY/MM/DD
-
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
98/11/23
YYYY/MM/DD
-
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
1998/11/23
YYMMDD
ISO
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
980124
YYYYMMDD
ISO
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1
Europe default + milliseconds
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)
28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H)
-
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)
ODBC Canonical
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)
ODBC Canonical
(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM
ISO8601
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1
Kuwaiti
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)
28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM
Kuwaiti
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
28/04/2006 12:39:32:429AM


Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.
Extended Date Formats
Date Format
SQL Statement
Sample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
1999-01-24
MM/YY
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
08/99
MM/YYYY
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]
12/2005
YY/MM
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]
99/08
YYYY/MM
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
2005/12
Month DD, YYYY 1
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]
July 04, 2006 1
Mon YYYY 1
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]
Apr 2006 1
Month YYYY 1
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]
February 2006 1
DD Month 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]
11 September 1
Month DD 1
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]
September 11 1
DD Month YY 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]
19 February 72 1
DD Month YYYY 1
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]
11 September 2002 1
MM-YY
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
12/92
MM-YYYY
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]
05-2006
YY-MM
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
92/12
YYYY-MM
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]
2006-05
MMDDYY
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]
122506
MMDDYYYY
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]
12252006
DDMMYY
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]
240702
DDMMYYYY
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]
24072002
Mon-YY 1
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]
Sep-02 1
Mon-YYYY 1
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]
Sep-2002 1
DD-Mon-YY 1
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]
25-Dec-05 1
DD-Mon-YYYY 1
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]
25-D


-- Get date only from datetime - QUICK SYNTAX
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, CURRENT_TIMESTAMP)) -- 2016-10-23 00:00:00.000
------------
-- SQL Server T-SQL date & datetime formats - Gregorian calendar - Christian calendar
-- getdate() / CURRENT_TIMESTAMP(ANSI) returns system date & time in standard format
-- SQL datetime formats with century (YYYY or CCYY format)- sql date & time format
SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)                                        -- Oct  2 2010 11:01AM          
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2010                  
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd - 2010.10.02           
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) -- hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
                                        -- Oct  2 2010 11:02:44:013AM   
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
-- yyyymmdd - ISO date format - international standard - works with any language setting
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
                                        -- 02 Oct 2010 11:02:07:577     
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm - ISO8601
                                        -- 2010-10-02T10:52:47.513
SELECT convert(varchar, getdate(), 127) -- yyyy-mm-ddThh:mm:ss.mmmZ - with time zone
SELECT convert(nvarchar(64), getdate(), 130) -- Arabic Hijri date
-- 29 جمادى الاولى 1433 1:13:04:633AM:
SELECT convert(nvarchar, getdate(), 131) -- Arabic Hijri date - Islamic calendar
-- 29/05/1433 12:57:26:690AM
-- Without century (YY) date / datetime conversion - there are exceptions!
SELECT convert(varchar, getdate(), 0)   -- mon dd yyyy hh:mmAM (or PM)
SELECT convert(varchar, getdate(), 1)   -- mm/dd/yy
SELECT convert(varchar, getdate(), 2)   -- yy.mm.dd          
SELECT convert(varchar, getdate(), 3)   -- dd/mm/yy
SELECT convert(varchar, getdate(), 4)   -- dd.mm.yy
SELECT convert(varchar, getdate(), 5)   -- dd-mm-yy
SELECT convert(varchar, getdate(), 6)   -- dd mon yy
SELECT convert(varchar, getdate(), 7)   -- mon dd, yy
SELECT convert(varchar, getdate(), 8)   -- hh:mm:ss
SELECT convert(varchar, getdate(), 9)   -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
SELECT convert(varchar, getdate(), 10)  -- mm-dd-yy
SELECT convert(varchar, getdate(), 11)  -- yy/mm/dd
SELECT convert(varchar, getdate(), 12)  -- yymmdd
SELECT convert(varchar, getdate(), 13)  -- dd mon yyyy hh:mm:ss:mmm
SELECT convert(varchar, getdate(), 14)  -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 20)  -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 21)  -- yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 22)  -- mm/dd/yy hh:mm:ss AM (or PM)
SELECT convert(varchar, getdate(), 23)  -- yyyy-mm-dd
SELECT convert(varchar, getdate(), 24)  -- hh:mm:ss
SELECT convert(varchar, getdate(), 25)  -- yyyy-mm-dd hh:mm:ss.mmm
-- SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 -- yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          -- mon yyyy
SELECT substring(convert(varchar, getdate(), 120),6, 11)   -- mm-dd hh:mm
----------
-- Current (today) midnight calculation - remove time part of datetime
-- Today's date without time - datetime without time
DECLARE @CurrentMidnight datetime =
      dateadd(day, datediff(day,0, CURRENT_TIMESTAMP), 0)
SELECT @CurrentMidnight
-- 2012-03-15 00:00:00.000
----------
------------
-- SQL Server date formatting function - convert datetime to string
------------
-- SQL datetime functions - SQL date functions - SQL server datetime formatting
-- T-SQL convert dates - T-SQL date formats - Transact-SQL date formats
CREATE FUNCTION dbo.fnFormatDate (@Datetime DATETIME, @FormatMask VARCHAR(32))
RETURNS VARCHAR(32)
AS
BEGIN
    DECLARE @StringDate VARCHAR(32)
    SET @StringDate = @FormatMask
    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))   
RETURN @StringDate
END
GO

-- Microsoft SQL Server date format function test - MSSQL formatting date - sql datetime
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YYYY')           -- 01/03/2012
SELECT dbo.fnFormatDate (getdate(), 'DD/MM/YYYY')           -- 03/01/2012
SELECT dbo.fnFormatDate (getdate(), 'M/DD/YYYY')            -- 1/03/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YYYY')             -- 1/3/2012
SELECT dbo.fnFormatDate (getdate(), 'M/D/YY')               -- 1/3/12
SELECT dbo.fnFormatDate (getdate(), 'MM/DD/YY')             -- 01/03/12
SELECT dbo.fnFormatDate (getdate(), 'MON DD, YYYY')         -- JAN 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Mon DD, YYYY')         -- Jan 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'Month DD, YYYY')       -- January 03, 2012
SELECT dbo.fnFormatDate (getdate(), 'YYYY/MM/DD')           -- 2012/01/03
SELECT dbo.fnFormatDate (getdate(), 'YYYYMMDD')             -- 20120103
SELECT dbo.fnFormatDate (getdate(), 'YYYY-MM-DD')           -- 2012-01-03
-- CURRENT_TIMESTAMP returns current system date and time in standard internal format
SELECT dbo.fnFormatDate (CURRENT_TIMESTAMP,'YY.MM.DD')      -- 12.01.03
------------

-- Convert date into integer format
DECLARE @date as DATETIME = CURRENT_TIMESTAMP;
SELECT CONVERT(int, CONVERT(char(8), @date, 112)) AS DateInt
-- 20120312
------------
/***** SELECTED SQL DATE/DATETIME FORMATS WITH NAMES *****/

-- SQL format datetime - - sql hh mm ss - sql yyyy mm dd
-- Default format: Oct 23 2006 10:40AM
SELECT [Default]=CONVERT(varchar,GETDATE(),100)

-- US-Style format: 10/23/2006
SELECT [US-Style]=CONVERT(char,GETDATE(),101)

-- ANSI format: 2006.10.23
SELECT [ANSI]=CONVERT(char,CURRENT_TIMESTAMP,102)

-- UK-Style format: 23/10/2006
SELECT [UK-Style]=CONVERT(char,GETDATE(),103)

-- German format: 23.10.2006
SELECT [German]=CONVERT(varchar,GETDATE(),104)

-- ISO format: 20061023
SELECT ISO=CONVERT(varchar,GETDATE(),112)

-- ISO8601 format: 2010-10-23T19:20:16.003
SELECT [ISO8601]=CONVERT(varchar,GETDATE(),126)
------------

------------
-- Islamic date conversion
------------
-- Gregorian date
SELECT CONVERT(VARCHAR(36), GETDATE(), 109)
-- Mar 17 2012  9:27:19:027PM
-- Islamic / Hijri date
SELECT CONVERT(NVARCHAR(36), GETDATE(), 130)
-- 24 ربيع الثاني 1433  9:27:19:030PM
SELECT CONVERT(VARCHAR(36), GETDATE(), 131)
-- 24/04/1433  9:27:19:030PM
------------

-- SQL Server datetime formats - Format dates SQL Server 2005 / 2008
-- Century date format MM/DD/YYYY usage in a query
SELECT TOP (1)
      SalesOrderID,
      OrderDate = CONVERT(char(10), OrderDate, 101),
      OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
/*
SalesOrderID      OrderDate               OrderDateTime
43697             07/01/2001          2001-07-01 00:00:00.000
*/

-- SQL update datetime column - SQL datetime DATEADD - datetime function
UPDATE Production.Product
SET ModifiedDate=DATEADD(dd,1, ModifiedDate)
WHERE ProductID = 1001

-- MM/DD/YY date format - Datetime format sql
SELECT TOP (1)
      SalesOrderID,
      OrderDate = CONVERT(varchar(8), OrderDate, 1),
      OrderDateTime = OrderDate
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY SalesOrderID desc
/*
SalesOrderID      OrderDate         OrderDateTime
75123             07/31/04          2004-07-31 00:00:00.000
*/
------------

-- Converting UNIX timestamp to datetime
DECLARE @Date  BIGINT = 1477180800
/* (UNIX time = 1477180800 --> midnight, OCT 23, 2016) */
SELECT DATEADD(ss, @Date, '19700101')
-- 2016-10-23 00:00:00.000
------------
 
-- SQL convert datetime to char - sql date string concatenation: + (plus) operator
PRINT 'Style 110: '+CONVERT(CHAR(10),GETDATE(),110)         -- Style 110: 07-10-2012
PRINT 'Style 111: '+CONVERT(CHAR(10),GETDATE(),111)         -- Style 111: 2012/07/10
PRINT 'Style 112: '+CONVERT(CHAR(8), GETDATE(),112)         -- Style 112: 20120710   
------------
-- Combining different style formats for date & time
-- Datetime formats - sql times format - datetime formats sql
DECLARE @Date DATETIME
SET @Date = '2015-12-22 03:51 PM'
SELECT CONVERT(CHAR(10),@Date,110) + SUBSTRING(CONVERT(varchar,@Date,0),12,8)
-- Result: 12-22-2015  3:51PM

-- Microsoft SQL Server cast datetime to string
SELECT stringDateTime=CAST (getdate() as varchar)
-- Result: Dec 29 2012  3:47AM
Related article: Date and Time Functions (Transact-SQL)

------------
-- SQL Server date and time functions overview
------------
-- SQL Server CURRENT_TIMESTAMP function - SQL Server datetime functions
-- Local NYC - EST - Eastern Standard Time zone
-- SQL DATEADD function - SQL DATEDIFF function
SELECT CURRENT_TIMESTAMP                        -- 2012-01-05 07:02:10.577
-- SQL Server DATEADD function
SELECT DATEADD(month,2,'2012-12-09')            -- 2013-02-09 00:00:00.000
-- SQL Server DATEDIFF function
SELECT DATEDIFF(day,'2012-12-09','2013-02-09')  -- 62
-- SQL Server DATENAME function
SELECT DATENAME(month,   '2012-12-09')          -- December
SELECT DATENAME(weekday, '2012-12-09')          -- Sunday
-- SQL Server DATEPART function
SELECT DATEPART(month, '2012-12-09')            -- 12
-- SQL Server DAY function
SELECT DAY('2012-12-09')                        -- 9
-- SQL Server GETDATE function
-- local NYC - EST - Eastern Standard Time zone
SELECT GETDATE()                                -- 2012-01-05 07:02:10.577
-- SQL Server GETUTCDATE function
-- London - Greenwich Mean Time
SELECT GETUTCDATE()                             -- 2012-01-05 12:02:10.577
-- SQL Server MONTH function
SELECT MONTH('2012-12-09')                      -- 12
-- SQL Server YEAR function
SELECT YEAR('2012-12-09')                       -- 2012

------------
-- Universal CONVERT function - datetime conversion UDF
------------
CREATE FUNCTION fnCONVERT( @Input datetimeoffset, @StyleNo int)
RETURNS nvarchar(35)  AS
BEGIN
  RETURN (CONVERT(nvarchar(35), @Input, @StyleNo))
END
GO

DECLARE @dt DATE=getdate(); SELECT dbo.fnCONVERT( @dt,106) -- 23 Dec 2010
GO
DECLARE @dt DATE=getdate(); SELECT dbo.fnCONVERT( @dt,156) -- 23 Dec 2010
GO
/* Msg 281, Level 16, State 1, Line 1
156 is not a valid style number when converting from datetimeoffset
to a character string. */
DECLARE @dt DATETIME=getdate(); SELECT dbo.fnCONVERT( @dt,112) -- 20101223
GO
DECLARE @dt SMALLDATETIME=getdate(); SELECT dbo.fnCONVERT( @dt,101) -- 12/23/2010
GO
------------
------------
-- SQL calculate the number of business days function - exclude Saturdays & Sundays
------------
CREATE FUNCTION fnBusinessDaysCount (@StartDate DATE, @EndDate  DATE)
RETURNS INT AS
  BEGIN
    IF (@StartDate IS NULL OR @EndDate IS NULL)  RETURN (0)
    DECLARE  @i INT = 0;
    WHILE (@StartDate <= @EndDate)
      BEGIN
        SET @i = @i + CASE
                        WHEN datepart(dw,@StartDate) BETWEEN 2 AND 6 THEN 1
                        ELSE 0
                      END 
        SET @StartDate = DATEADD(dd,1,@StartDate)
      END  -- while 
    RETURN (@i)
  END -- function
GO
SELECT dbo.fnBusinessDaysCount('2016-01-01','2016-12-31')
-- 261
------------
------------
-- T-SQL Date and time function application
-- CURRENT_TIMESTAMP and getdate() are the same in T-SQL
------------
-- T-SQL first day of week and last day of week
SELECT FirstDateOfWeek = dateadd(dd,-DATEPART(dw,GETDATE()) + 1,GETDATE())
SELECT LastDateOfWeek = dateadd(dd,7 - DATEPART(dw,GETDATE()),GETDATE())
-- SQL first day of the month - SQL first date of the month
-- SQL first day of current month - 2012-01-01 00:00:00.000
SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
-- SQL last day of the month - SQL last date of the month
-- SQL last day of current month - 2012-01-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP)+1,0))
-- SQL first day of last month
-- SQL first day of previous month - 2011-12-01 00:00:00.000
SELECT DATEADD(mm,-1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
-- SQL last day of last month
-- SQL last day of previous month - 2011-12-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,-1,GETDATE()))+1,0))
-- SQL first day of next month - 2012-02-01 00:00:00.000
SELECT DATEADD(mm,1,DATEADD(mm, DATEDIFF(mm,0,CURRENT_TIMESTAMP),0))
-- SQL last day of next month - 2012-02-28 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,DATEADD(MM,1,GETDATE()))+1,0))
GO
-- SQL first day of a month - 2012-10-01 00:00:00.000
DECLARE @Date datetime; SET @Date = '2012-10-23'
SELECT DATEADD(dd,0,DATEADD(mm, DATEDIFF(mm,0,@Date),0))
GO
-- SQL last day of a month - 2012-03-31 00:00:00.000
DECLARE @Date datetime; SET @Date = '2012-03-15'
SELECT DATEADD(dd,-1,DATEADD(mm, DATEDIFF(mm,0,@Date)+1,0))
GO
-- SQL first day of year - SQL first day of the year - 2012-01-01 00:00:00.000
SELECT DATEADD(yy, DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)
-- SQL last day of year - SQL last day of the year - 2012-12-31 00:00:00.000
SELECT DATEADD(yy,1, DATEADD(dd, -1, DATEADD(yy,
                     DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0)))
-- SQL last day of last year - SQL last day of previous year - 2011-12-31 00:00:00.000
SELECT DATEADD(dd,-1,DATEADD(yy,DATEDIFF(yy,0,CURRENT_TIMESTAMP), 0))
GO
------------

-- First and last day of date periods
DECLARE @Date DATETIME; SET = @Date = '20161023';
SELECT ReferenceDate      = @Date 
SELECT FirstDayOfYear     = DATEADD(YY, DATEDIFF(YY,0, @Date),0)
SELECT LastDayOfYear      = DATEADD(YY, DATEDIFF(YY,0, @Date)+1,-1)
SELECT FirstDayOfSemester = DATEADD(QQ,((DATEDIFF(QQ,0,@Date)/2)*2),0)
SELECT LastDayOfSemester  = DATEADD(QQ,((DATEDIFF(QQ,0,@Date)/2)*2)+2,-1)
SELECT FirstDayOfQuarter  = DATEADD(QQ, DATEDIFF(QQ,0, @Date),0)
-- 2016-10-01 00:00:00.000
SELECT LastDayOfQuarter   = DATEADD(QQ, DATEDIFF(QQ,0, @Date)+1,-1)
-- 2016-12-31 00:00:00.000
SELECT FirstDayOfMonth    = DATEADD(MM, DATEDIFF(MM,0, @Date),0)
SELECT LastDayOfMonth     = DATEADD(MM, DATEDIFF(MM,0, @Date)+1,-1)
SELECT FirstDayOfWeek     = DATEADD(WK, DATEDIFF(WK,0, @Date),0)
SELECT LastDayOfWeek      = DATEADD(WK, DATEDIFF(WK,0, @Date)+1,-1)
-- 2016-10-30 00:00:00.000
------------
-- Start of week SUNDAY - US_english language setting -
SELECT CURRENT_TIMESTAMP, DATEADD (week, DATEDIFF(week,6, CURRENT_TIMESTAMP),6)
-- End of week SATURDAY
SELECT CURRENT_TIMESTAMP, DATEADD (week, DATEDIFF(week,5, CURRENT_TIMESTAMP),5)
------------
-- SQL calculate age in years, months, days - Format dates SQL Server 2008
-- SQL table-valued function - SQL user-defined function - UDF
-- SQL Server age calculation - date difference
USE AdventureWorks2008;
GO
CREATE FUNCTION fnAge  (@BirthDate DATETIME)
RETURNS @Age TABLE(Years  INT,
                   Months INT,
                   Days   INT)
AS
  BEGIN
    DECLARE  @EndDate     DATETIME, @Anniversary DATETIME
    SET @EndDate = Getdate()
    SET @Anniversary = Dateadd(yy,Datediff(yy,@BirthDate,@EndDate),@BirthDate)
    INSERT @Age
    SELECT Datediff(yy,@BirthDate,@EndDate) - (CASE
                                                 WHEN @Anniversary > @EndDate THEN 1
                                                 ELSE 0
                                               END), 0, 0
     UPDATE @Age     SET    Months = Month(@EndDate - @Anniversary) - 1
    UPDATE @Age     SET    Days = Day(@EndDate - @Anniversary) - 1
    RETURN
  END
GO

-- Test table-valued UDF
SELECT * FROM   fnAge('1956-10-23')
SELECT * FROM   dbo.fnAge('1956-10-23')
/* Results
Years       Months      Days
52          4           1
*/

----------
-- SQL date range between
----------
-- SQL between dates
USE AdventureWorks;
-- SQL between
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate BETWEEN '20040301' AND '20040315'
-- Result: 108

-- BETWEEN operator is equivalent to >=...AND....<=
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate
BETWEEN '2004-03-01 00:00:00.000' AND '2004-03-15  00:00:00.000'
/*
Orders with OrderDates
'2004-03-15  00:00:01.000'  - 1 second after midnight (12:00AM)
'2004-03-15  00:01:00.000'  - 1 minute after midnight
'2004-03-15  01:00:00.000'  - 1 hour after midnight

are not included in the two queries above.
*/
-- To include the entire day of 2004-03-15 use the following two solutions
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE OrderDate >= '20040301' AND OrderDate < '20040316'

-- SQL between with DATE type (SQL Server 2008)
SELECT POs=COUNT(*) FROM Purchasing.PurchaseOrderHeader
WHERE CONVERT(DATE, OrderDate) BETWEEN '20040301' AND '20040315'
----------
-- Non-standard format conversion: 2011 December 14
-- SQL datetime to string
SELECT [YYYY Month DD] =
CAST(YEAR(GETDATE()) AS VARCHAR(4))+ ' '+
DATENAME(MM, GETDATE()) + ' ' +
CAST(DAY(GETDATE()) AS VARCHAR(2))

-- Converting datetime to YYYYMMDDHHMMSS format: 20121214172638
SELECT replace(convert(varchar, getdate(),111),'/','') +
replace(convert(varchar, getdate(),108),':','')

-- Datetime custom format conversion to YYYY_MM_DD
select CurrentDate=rtrim(year(getdate())) + '_' +
right('0' + rtrim(month(getdate())),2) + '_' +
right('0' + rtrim(day(getdate())),2)

-- Converting seconds to HH:MM:SS format
declare @Seconds int
set @Seconds = 10000
select TimeSpan=right('0' +rtrim(@Seconds / 3600),2) + ':' +
right('0' + rtrim((@Seconds % 3600) / 60),2) + ':' +
right('0' + rtrim(@Seconds % 60),2)
-- Result: 02:46:40

-- Test result
select 2*3600 + 46*60 + 40
-- Result: 10000
-- Set the time portion of a datetime value to 00:00:00.000
-- SQL strip time from date
-- SQL strip time from datetime
SELECT CURRENT_TIMESTAMP ,DATEADD(dd, DATEDIFF(dd, 0, CURRENT_TIMESTAMP), 0)
-- Results: 2014-01-23 05:35:52.793 2014-01-23 00:00:00.000
/* VALID DATE RANGES FOR DATE/DATETIME DATA TYPES

SMALLDATETIME (4 bytes) date range:
January 1, 1900 through June 6, 2079

DATETIME (8 bytes) date range:
January 1, 1753 through December 31, 9999

DATETIME2 (8 bytes) date range (SQL Server 2008):
January 1,1 AD through December 31, 9999 AD

DATE (3 bytes) date range (SQL Server 2008):
January 1, 1 AD through December 31, 9999 AD

*******/
-- Selecting with CONVERT into different styles
-- Note: Only Japan & ISO styles can be used in ORDER BY
SELECT TOP(1)
     Italy  = CONVERT(varchar, OrderDate, 105)
   , USA    = CONVERT(varchar, OrderDate, 110)
   , Japan  = CONVERT(varchar, OrderDate, 111)
   , ISO    = CONVERT(varchar, OrderDate, 112)
FROM AdventureWorks.Purchasing.PurchaseOrderHeader
ORDER BY PurchaseOrderID DESC
/* Results
Italy       USA         Japan       ISO
25-07-2004  07-25-2004  2004/07/25  20040725
*/
-- SQL Server convert date to integer
DECLARE @Datetime datetime
SET @Datetime = '2012-10-23 10:21:05.345'
SELECT DateAsInteger = CAST (CONVERT(varchar,@Datetime,112) as INT)
-- Result: 20121023

-- SQL Server convert integer to datetime
DECLARE @intDate int
SET @intDate = 20120315
SELECT IntegerToDatetime = CAST(CAST(@intDate as varchar) as datetime)
-- Result: 2012-03-15 00:00:00.000
-------------
-- Julian date (YYYYDDD) to date / datetime converter
------------
CREATE FUNCTION dbo.fnJulianToDate (@JulianDt char(7))
RETURNS date AS
BEGIN
    RETURN (SELECT DATEADD(day, CAST(RIGHT(@JulianDt,3) AS int) - 1,
    CONVERT(datetime, LEFT(@JulianDt,4) + '0101', 112)))
END
GO
SELECT dbo.fnJulianToDate ('2016040')
-- 2016-02-09
------------
-----------
-- SQL Server CONVERT script applying table INSERT/UPDATE
------------
-- SQL Server convert date
-- Datetime column is converted into date only string column
USE tempdb;
GO
CREATE TABLE sqlConvertDateTime   (
            DatetimeCol datetime,
            DateCol char(8));
INSERT sqlConvertDateTime (DatetimeCol) SELECT GETDATE()

UPDATE sqlConvertDateTime
SET DateCol = CONVERT(char(10), DatetimeCol, 112)
SELECT * FROM sqlConvertDateTime

-- SQL Server convert datetime
-- The string date column is converted into datetime column
UPDATE sqlConvertDateTime
SET DatetimeCol = CONVERT(Datetime, DateCol, 112)
SELECT * FROM sqlConvertDateTime

-- Adding a day to the converted datetime column with DATEADD
UPDATE sqlConvertDateTime
SET DatetimeCol = DATEADD(day, 1, CONVERT(Datetime, DateCol, 112))
SELECT * FROM sqlConvertDateTime

-- Equivalent formulation - SQL Server CAST datetime
UPDATE sqlConvertDateTime
SET DatetimeCol = DATEADD(dd, 1, CAST(DateCol AS datetime))
SELECT * FROM sqlConvertDateTime
GO
DROP TABLE sqlConvertDateTime
GO
/* First results
DatetimeCol                   DateCol
2014-12-25 16:04:15.373       20141225 */

/* Second results:
DatetimeCol                   DateCol
2014-12-25 00:00:00.000       20141225  */

/* Third results:
DatetimeCol                   DateCol
2014-12-26 00:00:00.000       20141225  */
------------
-- SQL month sequence - SQL date sequence generation with table variable
-- SQL Server cast string to datetime - SQL Server cast datetime to string
-- SQL Server insert default values method
DECLARE @Sequence table (Sequence int identity(1,1))
DECLARE @i int; SET @i = 0
DECLARE @StartDate datetime;
SET @StartDate = CAST(CONVERT(varchar, year(getdate()))+
                 RIGHT('0'+convert(varchar,month(getdate())),2) + '01' AS DATETIME)
WHILE ( @i < 120)
BEGIN
      INSERT @Sequence DEFAULT VALUES
      SET @i = @i + 1
END
SELECT MonthSequence = CAST(DATEADD(month, Sequence,@StartDate) AS varchar)
FROM @Sequence
GO
/* Partial results:
MonthSequence
Jan  1 2012 12:00AM
Feb  1 2012 12:00AM
Mar  1 2012 12:00AM
Apr  1 2012 12:00AM
*/
------------

------------
-- SQL Server Server datetime internal storage - SQL Server datetime formats
------------
-- SQL Server datetime to hex
SELECT Now=CURRENT_TIMESTAMP, HexNow=CAST(CURRENT_TIMESTAMP AS BINARY(8))
/* Results

Now                     HexNow
2009-01-02 17:35:59.297 0x00009B850122092D
*/
-- SQL Server date part - left 4 bytes - Days since 1900-01-01
SELECT Now=DATEADD(DAY, CONVERT(INT, 0x00009B85), '19000101')
GO
-- Result: 2009-01-02 00:00:00.000

-- SQL time part - right 4 bytes - milliseconds since midnight
-- 1000/300 is an adjustment factor
-- SQL dateadd to Midnight
SELECT Now=DATEADD(MS, (1000.0/300)* CONVERT(BIGINT, 0x0122092D), '2009-01-02')
GO
-- Result: 2009-01-02 17:35:59.290
------------
------------
-- String date and datetime date&time columns usage
-- SQL Server datetime formats in tables
------------
USE tempdb;
SET NOCOUNT ON;
-- SQL Server select into table create
SELECT TOP (5)
      FullName=convert(nvarchar(50),FirstName+' '+LastName),
      BirthDate = CONVERT(char(8), BirthDate,112),
      ModifiedDate = getdate()
INTO Employee
FROM AdventureWorks.HumanResources.Employee e
INNER JOIN AdventureWorks.Person.Contact c
ON c.ContactID = e.ContactID
ORDER BY EmployeeID
GO
-- SQL Server alter table
ALTER TABLE Employee ALTER COLUMN FullName nvarchar(50) NOT NULL
GO
ALTER TABLE Employee
ADD CONSTRAINT [PK_Employee] PRIMARY KEY (FullName )
GO
/* Results

Table definition for the Employee table
Note: BirthDate is string date (only)

CREATE TABLE dbo.Employee(
      FullName nvarchar(50) NOT NULL PRIMARY KEY,
      BirthDate char(8) NULL,
      ModifiedDate datetime NOT NULL
      )
*/
SELECT * FROM Employee ORDER BY FullName
GO
/* Results
FullName                BirthDate   ModifiedDate
Guy Gilbert             19720515    2009-01-03 10:10:19.217
Kevin Brown             19770603    2009-01-03 10:10:19.217
Rob Walters             19650123    2009-01-03 10:10:19.217
Roberto Tamburello      19641213    2009-01-03 10:10:19.217
Thierry D'Hers          19490829    2009-01-03 10:10:19.217
*/

-- SQL Server age
SELECT FullName, Age = DATEDIFF(YEAR, BirthDate, GETDATE()),
       RowMaintenanceDate = CAST (ModifiedDate AS varchar)
FROM Employee ORDER BY FullName
GO
/* Results
FullName                Age   RowMaintenanceDate
Guy Gilbert             37    Jan  3 2009 10:10AM
Kevin Brown             32    Jan  3 2009 10:10AM
Rob Walters             44    Jan  3 2009 10:10AM
Roberto Tamburello      45    Jan  3 2009 10:10AM
Thierry D'Hers          60    Jan  3 2009 10:10AM
*/

-- SQL Server age of Rob Walters on specific dates
-- SQL Server string to datetime implicit conversion with DATEADD
SELECT AGE50DATE = DATEADD(YY, 50, '19650123')
GO
-- Result: 2015-01-23 00:00:00.000

-- SQL Server datetime to string, Italian format for ModifiedDate
-- SQL Server string to datetime implicit conversion with DATEDIFF
SELECT FullName,
         AgeDEC31 = DATEDIFF(YEAR, BirthDate, '20141231'),
         AgeJAN01 = DATEDIFF(YEAR, BirthDate, '20150101'),
         AgeJAN23 = DATEDIFF(YEAR, BirthDate, '20150123'),
         AgeJAN24 = DATEDIFF(YEAR, BirthDate, '20150124'),
       ModDate = CONVERT(varchar, ModifiedDate, 105)
FROM Employee
WHERE FullName = 'Rob Walters'
ORDER BY FullName
GO
/* Results
Important Note: age increments on Jan 1 (not as commonly calculated)

FullName    AgeDEC31    AgeJAN01    AgeJAN23    AgeJAN24    ModDate
Rob Walters 49          50          50          50          03-01-2009 */

------------
-- SQL combine integer date & time into datetime
------------
-- Datetime format sql
-- SQL stuff
DECLARE @DateTimeAsINT TABLE ( ID int identity(1,1) primary key, 
   DateAsINT int, 
   TimeAsINT int 
) 
-- NOTE: leading zeroes in time is for readability only!  
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 235959)  
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 010204)  
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 002350)
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000244)  
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000050)  
INSERT @DateTimeAsINT (DateAsINT, TimeAsINT) VALUES (20121023, 000006)  
 
SELECT DateAsINT, TimeAsINT,
  CONVERT(datetime, CONVERT(varchar(8), DateAsINT) + ' '+
  STUFF(STUFF ( RIGHT(REPLICATE('0', 6) + CONVERT(varchar(6), TimeAsINT), 6),
                  3, 0, ':'), 6, 0, ':'))  AS DateTimeValue
FROM   @DateTimeAsINT 
ORDER BY ID
GO
/* Results
DateAsINT   TimeAsINT   DateTimeValue
20121023    235959      2012-10-23 23:59:59.000
20121023    10204       2012-10-23 01:02:04.000
20121023    2350        2012-10-23 00:23:50.000
20121023    244         2012-10-23 00:02:44.000
20121023    50          2012-10-23 00:00:50.000
20121023    6           2012-10-23 00:00:06.000
*/
------------
-- SQL Server string to datetime, implicit conversion with assignment
UPDATE Employee SET ModifiedDate = '20150123'
WHERE FullName = 'Rob Walters'
GO
SELECT ModifiedDate FROM Employee WHERE FullName = 'Rob Walters'
GO
-- Result: 2015-01-23 00:00:00.000

/* SQL string date, assemble string date from datetime parts  */
-- SQL Server cast string to datetime - sql convert string date
-- SQL Server number to varchar conversion
-- SQL Server leading zeroes for month and day
-- SQL Server right string function
UPDATE Employee SET BirthDate =
      CONVERT(char(4),YEAR(CAST('1965-01-23' as DATETIME)))+
      RIGHT('0'+CONVERT(varchar,MONTH(CAST('1965-01-23' as DATETIME))),2)+
      RIGHT('0'+CONVERT(varchar,DAY(CAST('1965-01-23' as DATETIME))),2)
      WHERE FullName = 'Rob Walters'
GO
SELECT BirthDate FROM Employee WHERE FullName = 'Rob Walters'
GO
-- Result: 19650123

-- Perform cleanup action
DROP TABLE Employee
-- SQL nocount
SET NOCOUNT OFF;
GO
------------
------------
-- sql isdate function
------------
USE tempdb;
-- sql newid - random sort
SELECT top(3) SalesOrderID,
stringOrderDate = CAST (OrderDate AS varchar)
INTO DateValidation
FROM AdventureWorks.Sales.SalesOrderHeader
ORDER BY NEWID()
GO
SELECT * FROM DateValidation
/* Results
SalesOrderID      stringOrderDate
56720             Oct 26 2003 12:00AM
73737             Jun 25 2004 12:00AM
70573             May 14 2004 12:00AM
*/
-- SQL update with top
UPDATE TOP(1) DateValidation
SET stringOrderDate = 'Apb 29 2004 12:00AM'
GO
-- SQL string to datetime fails without validation
SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)
FROM DateValidation
GO
/* 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.
*/
-- sql isdate - filter for valid dates
SELECT SalesOrderID, OrderDate = CAST (stringOrderDate as datetime)
FROM DateValidation
WHERE ISDATE(stringOrderDate) = 1
GO
/* Results
SalesOrderID      OrderDate
73737             2004-06-25 00:00:00.000
70573             2004-05-14 00:00:00.000
*/
-- SQL drop table
DROP TABLE DateValidation
Go

------------
-- SELECT between two specified dates - assumption TIME part is 00:00:00.000
------------
-- SQL datetime between
-- SQL select between two dates
SELECT EmployeeID, RateChangeDate
FROM AdventureWorks.HumanResources.EmployeePayHistory
WHERE RateChangeDate >= '1997-11-01' AND 
      RateChangeDate < DATEADD(dd,1,'1998-01-05')
GO
/* Results
EmployeeID  RateChangeDate
3           1997-12-12 00:00:00.000
4           1998-01-05 00:00:00.000
*/

/* Equivalent to

-- SQL datetime range
SELECT EmployeeID, RateChangeDate
FROM AdventureWorks.HumanResources.EmployeePayHistory
WHERE RateChangeDate >= '1997-11-01 00:00:00' AND 
      RateChangeDate <  '1998-01-06 00:00:00'
GO
*/
------------
-- SQL datetime language setting
-- SQL Nondeterministic function usage - result varies with language settings
SET LANGUAGE  'us_english';  –– Jan 12 2015 12:00AM 
SELECT US = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE  'British';     –– Dec  1 2015 12:00AM 
SELECT UK = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE  'German';      –– Dez  1 2015 12:00AM 
SET LANGUAGE  'Deutsch';     –– Dez  1 2015 12:00AM 
SELECT Germany = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE  'French';      –– déc  1 2015 12:00AM 
SELECT France = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE  'Spanish';     –– Dic  1 2015 12:00AM 
SELECT Spain = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE  'Hungarian';   –– jan 12 2015 12:00AM 
SELECT Hungary = convert(VARCHAR,convert(DATETIME,'01/12/2015'));
SET LANGUAGE  'us_english';
GO
------------
-- SQL Server 2008 T-SQL find next Monday for a given date
DECLARE @DateTime DATETIME = '2012-12-31'
SELECT NextMondaysDate=DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0),
WeekDayName=DATENAME(dw,DATEADD(dd,(DATEDIFF(dd, 0, @DateTime) / 7 * 7) + 7, 0));
/*
NextMondaysDate         WeekDayName
2013-01-07 00:00:00.000 Monday
*/
------------
------------
-- Function for Monday dates calculation
------------
USE AdventureWorks2008;
GO
-- SQL user-defined function
-- SQL scalar function - UDF
CREATE FUNCTION fnMondayDate
               (@Year          INT,
                @Month         INT,
                @MondayOrdinal INT)
RETURNS DATETIME
AS
  BEGIN
    DECLARE  @FirstDayOfMonth CHAR(10),
             @SeedDate        CHAR(10)
    
    SET @FirstDayOfMonth = convert(VARCHAR,@Year) + '-' + convert(VARCHAR,@Month) + '-01'
    SET @SeedDate = '1900-01-01'
    
    RETURN DATEADD(DD,DATEDIFF(DD,@SeedDate,DATEADD(DD,(@MondayOrdinal * 7) - 1,
                  @FirstDayOfMonth)) / 7 * 7,  @SeedDate)
  END
GO

-- Test Datetime UDF - Third Monday in Feb, 2015
SELECT dbo.fnMondayDate(2016,2,3)
-- 2015-02-16 00:00:00.000

-- First Monday of current month
SELECT dbo.fnMondayDate(Year(getdate()),Month(getdate()),1)
-- 2009-02-02 00:00:00.000  
------------

Posted by at No comments:
Subscribe to: Posts (Atom)