Geocoding UK Postcodes with Google Map API

Referencs: http://www.tomanthony.co.uk/blog/geocoding-uk-postcodes-with-google-map-api/

Geocoding UK Postcodes with Google Map API

Notice: As a few people have pointed out, this announcement from Google means Geocoding is now built in. Yet as more people have pointed out - it kinda sucks accuracy wise (think over a mile off on some postcodes!), whereas my method continues to be accurate.
Google Maps API provides a geocoding feature, for finding the latitude and longitude of places or addresses; but it does not work for UK postcodes. This is thanks to Royal Mail who have a copyright on the data, and are very restrictive with their (expensive) licenses for it.
There are various solutions out there for using 3rd party services and importing the data to be used with Google Maps, or for using community built databases for the info. However, I’ve had a few people ask me about doing it just though Google.
It is possible — Google AJAX Search API does provide geocoding for UK postcodes. We need to use the two APIs in harmony to achieve our result.
So here it is.

Step by step

I’ll assume you already know how to use Google Maps API, and you came here just looking how to add geocoding for the UK.

Step 1.

Grab a two API keys, if you already have your Google Maps API key, just grab an AJAX search key. You can get them here:
http://www.google.com/apis/maps/signup.html
http://code.google.com/apis/ajaxsearch/signup.html

Step 2.

Google will give you a sample page, you need to stick your two API keys at the top of the page, followed by a reference to your Javascript file:


type="text/javascript">script_sample_end>

<script_sample_startsrc="http://www.google.com/uds/api?file=uds.js&v=1.0&key=*KEY*"
type="text/javascript">

<script_sample_startsrc="gmap.js" type="text/javascript">script_sample_send>

Ensure the reference to your Javascript file comes after the two API keys.

Step 3.

In addition to the Google Maps API stuff, you need to stick a reference to Google local search at the top of your Javascript file:

var localSearch = new GlocalSearch();


You can grab my Javascript file right here, but remember you’ll need to change the API keys.

Step 4.

The key to this Geocoder is only a single function:

function usePointFromPostcode(postcode, callbackFunction) {

  localSearch.setSearchCompleteCallback(null,
    function() {
   
      if (localSearch.results[0]) { 
        var resultLat = localSearch.results[0].lat;
        var resultLng = localSearch.results[0].lng;
        var point = new GLatLng(resultLat,resultLng);
        callbackFunction(point);
      }else{
        alert("Postcode not found!");
      }
    });
 
  localSearch.execute(postcode + ", UK");
}


It takes 2 arguments; postcode is the postcode you want to look for, and callbackFunction is the function you wish to run on the results.
Why is it necessary to do it this way? It is the way AJAX, and thus Google AJAX Search API, works - the request is sent, and a callback function is designated to handle the results returned, when they are ready.
In our case, the callback function can do whatever you want with the results, which will come in the format of a GLatLng (often just called a point); I’ve supplied 2 sample functions, placeMarkerAtPoint and setCenterToPoint which do pretty much what they sound like they do.

Step 5.

Putting aside accessibility and graceful degradation for the sake of simplicity in this tutorial, the last step we need is just to add some hooks into our Javascript:


<input_sample type="text" id="postcode" size="10" />
<input_sample type="submit" value="Place Marker" onclick="javascript:
usePointFromPostcode(document.getElementById('postcode').value, placeMarkerAtPoint)" />


We have a field for inputting a postcode, and I’ve added a button for placing a marker there. Where I have placeMarkerAtPoint you can put a reference to your own function, or you can even add a function right in there, like this:





end tutorial

Write C# code in html Source

Default.aspx

   

        <%if (Session["Message"] != null && Session["Message"].ToString() != "")
          {%>
       
            <%=Session["Message"] %>
       

        <%}else{%> <%= Session["Message_new"]%><%}%>

   

Default.aspx.cs

 protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Session["Message"] = "";
            Session["Message_new"] = "Hello welcome....";
        }
    }

Sending email in asp.net

 public void sendemail(string from, string to, string subject, string body)
    {
        try
        {
            MailMessage omsg = new MailMessage();
            omsg.From = new MailAddress(from);
            omsg.To.Add(to);
            omsg.Subject = subject;
            omsg.Body = body;
            omsg.Priority = MailPriority.High;
            omsg.IsBodyHtml = true;
            omsg.BodyEncoding = System.Text.Encoding.UTF8;

            SmtpClient smtp = new SmtpClient();
            smtp.Send(omsg);
        }
        catch (Exception ex)
        {
            //
        }
    }

   
      
     
       
     
   
   

Embedding SQL Server Express into Custom Applications

Embedding SQL Server Express into Custom Applications

How to format DateTime in GridView BoundColumn and TemplateColumn?

http://www.codedigest.com/Articles/ASPNET/137_How_to_format_DateTime_in_GridView_BoundColumn_and_TemplateColumn.aspx

http://peterkellner.net/2006/05/24/how-to-set-a-date-format-in-gridview-using-aspnet-20using-htmlencode-property/

http://www.devasp.net/net/articles/display/680.html

Visual Studio shortcut keys You are familiar with many of Visual Studio's shortcut keys, but not all of them. Here is a handy reference that can make your .NET lifestyle easier and a lot more productive. General Shortcut Description Ctrl-X or Shift-Delete Cuts the currently selected item to the clipboard Ctrl-C or Ctrl-Insert Copies the currently selected item to the clipboard Ctrl-V or Shift-Insert Pastes the item in the clipboard at the cursor Ctrl-Z or Alt-Backspace Undo previous editing action Ctrl-Y or Ctrl-Shift-Z Redo the previous undo action Ctrl-Shift-V or Ctrl-Shift-Insert Pastes an item from the clipboard ring tab of the Toolbox at the cursor in the file and automatically selects the pasted item. Cycle through the items on the clipboard by pressing the shortcut keys repeatedly Esc Closes a menu or dialog, cancels an operation in progress, or places focus in the current document window Ctrl-S Saves the selected files in the current project (usually the file that is being edited) Ctrl-Shift-S Saves all documents and projects Ctrl-P Displays the Print dialog F7 Switches from the design view to the code view in the editor Shift-F7 Switches from the code view to the design view in the editor F8 Moves the cursor to the next item, for example in the TaskList window or Find Results window Shift-F8 Moves the cursor to the previous item, for example in the TaskList window or Find Results window Shift-F12 Finds a reference to the selected item or the item under the cursor Ctrl-Shift-G Opens the file whose name is under the cursor or is currently selected Ctrl-/ Switches focus to the Find/Command box on the Standard toolbar Ctrl-Shift-F12 Moves to the next task in the TaskList window Ctrl-Shift-8 Moves backward in the browse history. Available in the object browser or Class View window Alt-Left Arrow Go back in the web browser history Alt-Right Arrow Go forward in the web browser history Text navigation Shortcut Description Left Arrow Moves the cursor one character to the left Right Arrow Moves the cursor one character to the right Down Arrow Moves the cursor down one line Up Arrow Moves the cursor up one line Page Down Scrolls down one screen in the editor window Page Up Scrolls up one screen in the editor window End Moves the cursor to the end of the current line Home Moves the cursor to the beginning of the line. If you press Home when the cursor is already at the start of the line, it will toggle the cursor between the first non-whitespace character and the real start of the line Ctrl-End Moves the cursor to the end of the document Ctrl-Home Moves the cursor to the start of the document Ctrl-G Displays the Go to Line dialog. If the debugger is running, the dialog also lets you specify addresses or function names to go to Ctrl-] Moves the cursor to the matching brace in the document. If the cursor is on an opening brace, this will move to the corresponding closing brace and vice versa Ctrl-K, Ctrl-N Moves to the next bookmark in the document Ctrl-K, Ctrl-P Moves to the previous bookmark Ctrl-K, Ctrl-I Displays Quick Info, based on the current language Ctrl-Down Arrow Scrolls text down one line but does not move the cursor. This is useful for scrolling more text into view without losing your place. Available only in text editors Ctrl-Up Arrow Scrolls text up one line but does not move the cursor. Available only in text editors Ctrl-Right Arrow Moves the cursor one word to the right Ctrl-Left Arrow Moves the cursor one word to the left Ctrl-Shift-1 Navigates to the next definition, declaration, or reference of an item. Available in the object browser and Class View window. Also available in source editing windows if you have already used the Edit.GoToReference (Shift-F12) shortcut Ctrl-Shift-2 Navigates to the previous definition, declaration, or reference of an item Text manipulation Shortcut Description Enter Inserts a new line Delete Deletes one character to the right of the cursor Insert Toggles between insert and overtype insertion modes Tab Indents the currently selected line or lines by one tab stop. If there is no selection, this inserts a tab stop Shift-Tab Moves current line or selected lines one tab stop to the left Backspace or Shift-Backspace Deletes one character to the left of the cursor Ctrl-K, Ctrl-C Marks the current line or selected lines of code as a comment, using the correct comment syntax for the programming language Ctrl-K, Ctrl-U Removes the comment syntax from the current line or currently selected lines of code Ctrl-T or Shift-Enter Swaps the characters on either side of the cursor. (For example, AC|BD becomes AB|CD.) Available only in text editors Ctrl-K, Ctrl-L Removes all unnamed bookmarks in the current document Ctrl-M, Ctrl-O Automatically determines logical boundaries for creating regions in code, such as procedures, and then hides them. This collapses all such regions in the current document Alt-Right Arrow or Ctrl-Spacebar Displays statement completion based on the current language or autocompletes word if existing text unambiguously identifies a single symbol Ctrl-K, Ctrl-\ Removes horizontal whitespace in the selection or deletes whitespace adjacent to the cursor if there is no selection Ctrl-K, Ctrl-F Applies the indenting and space formatting for the language as specified on the Formatting pane of the language in the Text Editor section of the Options dialog to the selected text. Ctrl-L Cuts all selected lines or the current line if nothing has been selected to the clipboard Ctrl-Shift-L Deletes all selected lines or the current line if no selection has been made Ctrl-Enter Inserts a blank line above the cursor Ctrl-Shift-Enter Inserts a blank line below the cursor Shift-Alt-T Moves the line containing the cursor below the next line Ctrl-J Lists members for statement completion when editing code Ctrl-U Changes the selected text to lowercase characters Ctrl-Shift-U Changes the selected text to uppercase characters Ctrl-Shift-Spacebar Displays a tooltip that contains information for the current parameter, based on the current language Ctrl-M, Ctrl-U Removes the outlining information for the currently selected region Ctrl-M, Ctrl-P Removes all outlining information from the entire document Ctrl-R, Ctrl-P Swaps the anchor and endpoint of the current selection Ctrl-M, Ctrl-L Toggles all previously marked hidden text sections between hidden and display states Ctrl-K, Ctrl-K Sets or removes a bookmark at the current line Ctrl-M, Ctrl-M Toggles the currently selected hidden text section or the section containing the cursor if there is no selection between the hidden and display states Ctrl-K, Ctrl-H Sets or removes a shortcut in the tasklist to the current line Ctrl-R, Ctrl-R Enables or disables word wrap in an editor Ctrl-R, Ctrl-W Shows or hides spaces and tab marks Ctrl-Delete Deletes the word to the right of the cursor Ctrl-Backspace Deletes the word to the left of the cursor Ctrl-Shift-T Transposes the two words that follow the cursor. (For example, |End Sub would be changed to read Sub End|.) Ctrl-.[dot] Display options on smarttag menu. Very useful for showing using/Imports options. Text selection Shortcut Description Shift-Left Arrow Moves the cursor to the left one character, extending the selection Shift-Alt-Left Arrow Moves the cursor to the left one character, extending the column selection Shift-Right Arrow Moves the cursor to the right one character, extending the selection Shift-Alt-Right Arrow Moves the cursor to the right one character, extending the column selection Ctrl-Shift-End Moves the cursor to the end of the document, extending the selection Ctrl-Shift-Home Moves the cursor to the start of the document, extending the selection Ctrl-Shift-] Moves the cursor to the next brace, extending the selection Shift-Down Arrow Moves the cursor down one line, extending the selection Shift-Alt-Down Arrow Moves the cursor down one line, extending the column selection Shift-End Moves the cursor to the end of the current line, extending the selection Shift-Alt-End Moves the cursor to the end of the line, extending the column selection Shift-Home Moves the cursor to the start of the line, extending the selection Shift-Alt-Home Moves the cursor to the start of the line, extending the column selection Shift-Up Arrow Moves the cursor up one line, extending the selection Shift-Alt-Up Arrow Moves the cursor up one line, extending the column selection Shift-Page Down Extends selection down one page Shift-Page Up Extends selection up one page Ctrl-A Selects everything in the current document Ctrl-W Selects the word containing the cursor or the word to the right of the cursor Ctrl-= Selects from the current location in the editor back to the previous location in the navigation history Ctrl-Shift-Page Down Moves the cursor to the last line in view, extending the selection Ctrl-Shift-Page Up Moves the cursor to the top of the current window, extending the selection Ctrl-Shift-Alt-Right Arrow Moves the cursor to the right one word, extending the column selection Ctrl-Shift-Left Arrow Moves the cursor one word to the left, extending the selection Ctrl-Shift-Alt-Left Arrow Moves the cursor to the left one word, extending the column selection return to top Project related Shortcut Description Ctrl-Shift-B Builds the solution Ctrl-N Displays the New File dialog. Note: files created this way are not associated with a project. Use Ctrl-Shift-A to add a new file in a project Ctrl-Shift-N Displays the New Project dialog Ctrl-O Displays the Open File dialog Ctrl-Shift-O Displays the Open Project dialog Shift-Alt-A Displays the Add Existing Item dialog Ctrl-Shift-A Displays the Add New Item dialog Ctrl-Alt-Insert Allows you to override base class methods in a derived class when an overridable method is highlighted in the Class View pane Window manipulation Shortcut Description Shift-Alt-Enter Toggles full screen mode Ctrl-+ Goes back to the previous location in the navigation history. (For example, if you press Ctrl-Home to go to the start of a document, this shortcut will take the cursor back to wherever it was before you pressed Ctrl-Home.) Ctrl-Shift-+ Moves forward in the navigation history. This is effectively an undo for the View.NavigateBackward operation Ctrl-F4 Closes the current MDI child window Shift-Esc Closes the current tool window Ctrl-F2 Moves the cursor to the navigation bar at the top of a code view Ctrl-Tab Cycles through the MDI child windows one window at a time Ctrl-F6, Ctrl-Shift-Tab Moves to the previous MDI child window Alt-F6, Ctrl-Shift-F6 Moves to the next tool window Shift-Alt-F6 Moves to the previously selected window F6 Moves to the next pane of a split pane view of a single document Shift-F6 Moves to the previous pane of a document in split pane view Ctrl-Pagedown Moves to the next tab in the document or window (e.g., you can use this to switch the HTML editor from its design view to its HTML view Ctrl-PageUp Moves to the previous tab in the document or window Control editor (designer) Shortcut Description Ctrl-Down Arrow Moves the selected control down in increments of one on the design surface Down Arrow Moves the selected control down to the next grid position on the design surface Ctrl-Left Arrow Moves the control to the left in increments of one on the design surface Left Arrow Moves the control to the left to the next grid position on the design surface Ctrl-Right Arrow Moves the control to the right in increments of one on the design surface Right Arrow Moves the control to the right into the next grid position on the design surface Ctrl-Up Arrow Moves the control up in increments of one on the design surface Up Arrow Moves the control up into the next grid position on the design surface Tab Moves to the next control in the tab order Shift-Tab Moves to the previous control in the tab order Ctrl-Shift-Down Arrow Increases the height of the control in increments of one on the design surface Shift-Down Arrow Increases the height of the control to the next grid position on the design surface Ctrl-Shift-Left Arrow Reduces the width of the control in increments of one on the design surface Shift-Left Arrow Reduces the width of the control to the next grid position on the design surface Ctrl-Shift-Right Arrow Increases the width of the control in increments of one on the design surface Shift-Left Arrow Increases the width of the control to the next grid position on the design surface Ctrl-Shift-Up Arrow Decreases the height of the control in increments of one on the design surface Shift-Up Arrow Decreases the height of the control to the next grid position on the design surface Search and replace Shortcut Description Ctrl-F Displays the Find dialog Ctrl-Shift-F Displays the Find in Files dialog F3 Finds the next occurrence of the previous search text Ctrl-F3 Finds the next occurrence of the currently selected text or the word under the cursor if there is no selection Shift-F3 Finds the previous occurrence of the search text Ctrl-Shift-F3 Finds the previous occurrence of the currently selected text or the word under the cursor Ctrl-D Places the cursor in the Find/Command line on the Standard toolbar Alt-F3, H Selects or clears the Search Hidden Text option for the Find dialog Ctrl-I Starts an incremental search—after pressing Ctrl-I, you can type in text, and for each letter you type, VS.NET will find the first occurrence of the sequence of letters you have typed so far. This is a very convenient facility, as it lets you find text by typing in exactly as many characters as are required to locate the text and no more. If you press Ctrl-I a second time without typing any characters, it recalls the previous pattern. If you press it a third time or you press it when an incremental search has already found a match, VS.NET searches for the next occurrence. Alt-F3, C Selects or clears the Match Case option for Find and Replace operations Alt-F3, R Selects or clears the Regular Expression option so that special characters can be used in Find and Replace operations Ctrl-H Displays the Replace dialog Ctrl-Shift-H Displays the Replace in Files dialog Ctrl-Shift-I Performs an incremental search in reverse direction Alt-F3, S Halts the current Find in Files operation Alt-F3, B Selects or clears the Search Up option for Find and Replace operations Alt-F3, W Selects or clears the Match Whole Word option for Find and Replace operations Alt-F3, P Selects or clears the Wildcard option for Find and Replace operations Help Shortcut Description Ctrl-Alt-F1 Displays the Contents window for the documentation Ctrl-F1 Displays the Dynamic Help window, which displays different topics depending on what items currently have focus. If the focus is in a source window, the Dynamic Help window will display help topics that are relevant to the text under the cursor F1 Displays a topic from Help that corresponds to the part of the user interface that currently has the focus. If the focus is in a source window, Help will try to display a topic relevant to the text under the cursor Ctrl-Alt-F2 Displays the Help Index window Shift-Alt-F2 Displays the Index Results window, which lists the topics that contain the keyword selected in the Index window Alt-Down Arrow Displays the next topic in the table of contents. Available only in the Help browser window Alt-Up Arrow Displays the previous topic in the table of contents. Available only in the Help browser window Ctrl-Alt-F3 Displays the Search window, which allows you to search for words or phrases in the documentation Shift-Alt-F3 Displays the Search Results window, which displays a list of topics that contain the string searched for from the Search window. Shift-F1 Displays a topic from Help that corresponds to the user interface item that has the focus Debugging Shortcut Description Ctrl-Alt-V, A Displays the Auto window to view the values of variables currently in the scope of the current line of execution within the current procedure Ctrl-Alt-Break Temporarily stops execution of all processes in a debugging session. Available only in run mode Ctrl-Alt-B Displays the Breakpoints dialog, where you can add and modify breakpoints Ctrl-Alt-C Displays the Call Stack window to display a list of all active procedures or stack frames for the current thread of execution. Available only in break mode Ctrl-Shift-F9 Clears all of the breakpoints in the project Ctrl-Alt-D Displays the Disassembly window Ctrl-F9 Enables or disables the breakpoint on the current line of code. The line must already have a breakpoint for this to work Ctrl-Alt-E Displays the Exceptions dialog Ctrl-Alt-I Displays the Immediate window, where you can evaluate expressions and execute individual commands Ctrl-Alt-V, L Displays the Locals window to view the variables and their values for the currently selected procedure in the stack frame Ctrl-Alt-M, 1 Displays the Memory 1 window to view memory in the process being debugged. This is particularly useful when you do not have debugging symbols available for the code you are looking at. It is also helpful for looking at large buffers, strings, and other data that does not display clearly in the Watch or Variables window Ctrl-Alt-M, 2 Displays the Memory 2 window Ctrl-Alt-M, 3 Displays the Memory 3 window Ctrl-Alt-M, 4 Displays the Memory 4 window Ctrl-Alt-U Displays the Modules window, which allows you to view the .dll or .exe files loaded by the program. In multiprocess debugging, you can right-click and select Show Modules for all programs Ctrl-B Opens the New Breakpoint dialog Ctrl-Alt-Q Displays the Quick Watch dialog with the current value of the selected expression. Available only in break mode. Use this command to check the current value of a variable, property, or other expression for which you have not defined a watch expression Ctrl-Alt-G Displays the Registers window, which displays CPU register contents Ctrl-Shift-F5 Terminates the current debugging session, rebuilds if necessary, and then starts a new debugging session. Available in break and run modes Ctrl-Alt-N Displays the Running Documents window that displays the set of HTML documents that you are in the process of debugging. Available in break and run modes Ctrl-F10 Starts or resumes execution of your code and then halts execution when it reaches the selected statement. This starts the debugger if it is not already running Ctrl-Shift-F10 Sets the execution point to the line of code you choose Alt-NUM * Highlights the next statement to be executed F5 If not currently debugging, this runs the startup project or projects and attaches the debugger. If in break mode, this allows execution to continue (i.e., it returns to run mode). Ctrl-F5 Runs the code without invoking the debugger. For console applications, this also arranges for the console window to stay open with a "Press any key to continue" prompt when the program finishes F11 Executes code one statement at a time, tracing execution into function calls Shift-F11 Executes the remaining lines of a function in which the current execution point lies F10 Executes the next line of code but does not step into any function calls Shift-F5 Available in break and run modes, this terminates the debugging session Ctrl-Alt-V, T Displays the This window, which allows you to view the data members of the object associated with the current method Ctrl-Alt-H Displays the Threads window to view all of the threads for the current process F9 Sets or removes a breakpoint at the current line Ctrl-F11 Displays the disassembly information for the current source file. Available only in break mode Ctrl-Alt-W, 1 Displays the Watch 1 window to view the values of variables or watch expressions Ctrl-Alt-W, 2 Displays the Watch 2 window Ctrl-Alt-W, 3 Displays the Watch 3 window Ctrl-Alt-W, 4 Displays the Watch 4 window Ctrl-Alt-P Displays the Processes dialog, which allows you to attach or detach the debugger to one or more running processes Object browser Shortcut Description Alt-F12 Displays the Find Symbol dialog Ctrl-F12 Displays the declaration of the selected symbol in the code F12 Displays the definition for the selected symbol in code Ctrl-Alt-F12 Displays the Find Symbol Results window Ctrl-Alt-J Displays the Object Browser to view the classes, properties, methods, events, and constants defined either in your project or by components and type libraries referenced by your project Alt-+ Moves back to the previously selected object in the selection history of the object browser Shift-Alt-+ Moves forward to the next object in the selection history of the object browser Tool window Shortcut Description Ctrl-Shift-M Toggles the Command window into or out of a mode allowing text within the window to be selected Ctrl-Shift-C Displays the Class View window Ctrl-Alt-A Displays the Command window, which allows you to type commands that manipulate the IDE Ctrl-Alt-T Displays the Document Outline window to view the flat or hierarchical outline of the current document Ctrl-Alt-F Displays the Favorites window, which lists shortcuts to web pages Ctrl-Alt-O Displays the Output window to view status messages at runtime F4 Displays the Properties window, which lists the design-time properties and events for the currently selected item Shift-F4 Displays the property pages for the item currently selected. (For example, use this to show a project's settings.) Ctrl-Shift-E Displays the Resource View window Ctrl-Alt-S Displays the Server Explorer window, which allows you to view and manipulate database servers, event logs, message queues, web services, and many other operating system services Ctrl-Alt-R Displays the web browser window, which allows you to view pages on the Internet Ctrl-Alt-L Displays the Solution Explorer, which lists the projects and files in the current solution Ctrl-Alt-K Displays the TaskList window, which displays tasks, comments, shortcuts, warnings, and error messages Ctrl-Alt-X Displays the Toolbox, which contains controls and other items that can be dragged into editor and designer windows Html editor (Design View) Shortcut Description Ctrl-B Toggles the selected text between bold and normal Ctrl-Shift-T Decreases the selected paragraph by one indent unit Ctrl-T Indents the selected paragraph by one indent unit Ctrl-I Toggles the selected text between italic and normal Ctrl-Shift-K Prevents an absolutely positioned element from being inadvertently moved. If the element is already locked, this unlocks it Ctrl-G Toggles the grid Ctrl-Shift-G Specifies that elements be aligned using an invisible grid. You can set grid spacing on the Design pane of HTML designer options in the Options dialog, and the grid will be changed the next time you open a document Ctrl-U Toggles the selected text between underlined and normal Ctrl-Shift-L Displays the Bookmark dialog Ctrl-J Inserts in the current HTML document Ctrl-L When text is selected, displays the Hyperlink dialog Ctrl-Shift-W Displays the Insert Image dialog Ctrl-Alt-Up Arrow Adds one row above the current row in the table Ctrl-Alt-Down Arrow Adds one row below the current row in the table Ctrl-Alt-Left Arrow Adds one column to the left of the current column in the table Ctrl-Alt-Right Arrow Adds one column to the right of the current column in the table Ctrl-Shift-Q Toggles display of marker icons for HTML elements that do not have a visual representation, such as comments, scripts, and anchors for absolutely positioned elements Ctrl-Page Down Switches from design view to HTML view and vice versa Ctrl-Q Displays a 1-pixel border around HTML elements that support a BORDER attribute and have it set to zero, such as tables, table cells, and divisions Macro Shortcut Description Alt-F8 Displays the Macro Explorer window, which lists all available macros Alt-F11 Launches the macros IDE Ctrl-Shift-R Places the environment in macro record mode or completes recording if already in record mode Ctrl-Shift-P Plays back a recorded macro

Wpf Tutorials

Welcome to the webs WPF tutorial directory. The Windows Presentation Foundation (or WPF) is a graphical subsystem for rendering user interfaces in Windows-based applications. Here we will include all WPF related tutorials and resource web sites.

• WPF Videos - WindowsClient.net
• WPF Tutorial
• WPF Tutorials from Live Journal
• DotNetSlackers WPF Tutorial
• kirupa.com - Expression Blend / WPF Tutorials
• Jason Zander's WebLog : VS2010 Tutorial: Build a WPF App (Step 1)

WPF Tutorial - A Beginning : 1

WPF Tutorial - A Beginning : 1

WPF Tutorial

WPF Tutorial

Benefits of LINQ over stored Procedure

1. Makes it easier to transform data into objects. I'm sure you've heard the term "Impedence Mismatch" being used quite often, meaning that LINQ reduces the amount of work you must do to translate between object-oriented code and data paradigms such as hierarchical, flat-file, messages, relational, and more. It doesn't eliminate the "Impedence Mismatch" because you must still reason about your data in its native form, but the bridge from here to there is (IMO) much shorter.



2. A common syntax for all data. Once you learn query syntax, you can use it with any LINQ provider. I think this is a much better development paradigm than the Tower of Babel that has grown over the years with data access technologies. Of course, each LINQ provider has unique nuances that are necessary, but the basic approach and query syntax is the same.



3. Strongly typed code. The C# (or VB.NET) query syntax is part of the language and you code with C# types, which are translated into something a provider understands. This means that you gain the productivity of having your compiler find errors earlier in the development lifecycle than elsewhere. Granted, many errors in stored proc syntax will generate errors when you save, but LINQ is more general than SQL Server. You have to think of all the other types of data sources that generate runtime errors because their queries are formed with strings or some other loosely typed mechanism.



4. Provider integration. Pulling together data sources is very easy. For example, you can use LINQ to Objects, LINQ to SQL, and LINQ to XML together for some very sophisticated scenarios. I think it's very elegant.



5. Reduction in work. Before LINQ, I spent a lot of time building DALs, but now my DataContext is the DAL. I've used OPFs too, but now I have LINQ that ships with multiple providers in the box and many other 3rd party providers, giving me the benefits from my previous points. I can set up a LINQ to SQL DataContext in a minute (as fast as my computer and IDE can keep up).



6. Performance in the general case doesn't become an issue. SQL Server optimizes queries quite well these days, just like stored procs. Of course, there are still cases where stored procs are necessary for performance reasons. For example, I've found it smarter to use a stored proc when I had multiple interactions between tables with additional logic inside of a transaction. The communications overhead of trying to do the same task in code, in addition to getting the DTC involved in a distributed transaction made the choice for a stored proc more compelling. However, for a query that executes in a single statement, LINQ is my preferred choice because even if there was a small performance gain from a stored proc, the benefits in previous points (IMO) carry more weight.



7. Built-in security. One reason I preferred stored procs before LINQ was that they forced the use of parameters, helping to reduce SQL injection attacks. LINQ to SQL already parameterizes input, which is just as secure.



8. LINQ is declarative. A lot of attention is paid to working with LINQ to XML or LINQ to SQL, but LINQ to Objects is incredibly powerful. A typical example of LINQ to Objects is reading items from a string[]. However, that's just a small example. If you think about all of the IEnumerable collections (you can also query IEnumerable) that you work with every day, the opportunities are plentiful. i.e. Searching an ASP.NET ListBox control for selected items, performing set operations (such as Union) on two collections, or iterating through a List and running a lambda in a ForEach of each item. Once you begin to think in LINQ, which is declarative in nature, you can find many of your tasks to be simpler and more intuitive than the imperative techniques you use today.



I could probably go on, but I'd better stop there. Hopefully, this will provide a more positive view of how you could be more productive with LINQ and perhaps see it as a useful technology from a broader perspective.

Reading Excel tables via OLEDB .NET - returns null values

1. External Data - Mixed Data Types

2. Read Forum post

3. MSDN

4. Excel Values Returned as NULL Using DAO OpenRecordset

VS 2010 and .NET 4 Series

VS 2010 and .NET 4 Series

Visual Studio 2008 and .NET Framework 3.5 Service Pack 1 Beta

Visual Studio 2008 and .NET Framework 3.5 Service Pack 1 Beta

Hard Drive Speed and Visual Studio Performance

Hard Drive Speed and Visual Studio Performance

Visual Studio 2008 and .NET 3.5 Released

Visual Studio 2008 and .NET 3.5 Released

Microsoft Surface: Behind-the-Scenes First Look (with Video)

ASP.NET 2.0 Tips, Tricks, Recipes and Gotchas by Scottgu

ASP.NET 2.0 Tips, Tricks, Recipes and Gotchas

Guard Against SQL Injection Attacks

Guard Against SQL Injection Attacks

Logging JavaScript Errors To ASP.NET

Logging JavaScript Errors To ASP.NET

Session Security in ASP.NET

http://www.dotnetfunda.com/articles/article730-session-security-in-aspnet-.aspx

Open a Page in a new window from code behind in asp.net

Dim strScript As String = ""
Page.RegisterStartupScript("PopUp", strScript)

Here "filePath " can be a page name like "/Application/folder/default.aspx" or "/Application/folder/mypdffile.pdf".

Remove non-numeric or non-alphameric characters.

This can be used to remove or save any range of characters from a string or field.

declare @s varchar(100), @i int

select @s = 'asd i/.,<>as>[{}]vnbv'
select @s

select @i = patindex('%[^a-zA-Z0-9 ]%', @s)
while @i > 0
begin
select @s = replace(@s, substring(@s, @i, 1), '')
select @i = patindex('%[^a-zA-Z0-9 ]%', @s)
end

select @s


gives
before
asd i/.,<>as>[{}]vnbv
after
asd iasvnbv


Removing the characters from a field in a table


create table #a (s varchar(100))

insert #a (s) select 'asd i/.,<>as>[{}]vnbv'
insert #a (s) select 'aaa'
insert #a (s) select '123 ''h 9)'

select * from #a

while @@rowcount > 0
update  #a
set s = replace(s, substring(s, patindex('%[^a-zA-Z0-9 ]%', s), 1), '')
where patindex('%[^a-zA-Z0-9 ]%', s) <> 0

select * from #a

Gives

before
asd i/.,<>as>[{}]vnbv
aaa
123 'h 9)
after
asd iasvnbv
aaa
123 h 9

Asp.Net page Life Cycle events

If (!IsPostBack)

1. Begin PreInit
2. End PreInit
3. Begin Init
4. End Init
5. Begin InitComplete
6. End InitComplete
7. Begin PreLoad
8. End PreLoad
9. Begin Load
10. End Load
11. Begin LoadComplete
12. End LoadComplete
13. Begin PreRender
14. End PreRender
15. Begin PreRenderComplete
16. End PreRenderComplete
17. Begin SaveState
18. End SaveState
19. Begin SaveStateComplete
20. End SaveStateComplete
21. Begin Render
22. End Render
    

If(IsPostBack)

1. Begin PreInit
2. End PreInit
3. Begin Init
4. End Init
5. Begin InitComplete
6. End InitComplete
7. Begin LoadState
8. End LoadState
9. Begin ProcessPostData
10. End ProcessPostData
11. Begin PreLoad
12. End PreLoad
13. Begin Load
14. End Load
15. Begin ProcessPostData (Again)
16. End ProcessPostData (Again)
17. Begin Raise ChangedEvents
18. End Raise ChangedEvents
19. Begin Raise PostBackEvent
20. End Raise PostBackEvent
21. Begin LoadComplete
22. End LoadComplete
23. Begin PreRender
24. End PreRender
25. Begin PreRenderComplete
26. End PreRenderComplete
27. Begin SaveState
28. End SaveState
29. Begin SaveStateComplete
30. End SaveStateComplete
31. Begin Render
32. End Render

Export to Excel from html

Response.Clear()
Response.Buffer = True
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader("content-disposition", "attachment;filename=MyFiles.xls")
Response.ContentEncoding = System.Text.Encoding.Unicode
Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble())
Me.EnableViewState = False
Response.Write(Session("Str"))
Response.End()

How to format datetime & date with century?

Execute the following Microsoft SQL Server T-SQL datetime, date and time formatting scripts in Management Studio Query Editor to demonstrate the usage of the multitude of temporal data formats available.
-- Microsoft SQL Server T-SQL date and datetime formats - sql server date datetime
-- Date time formats - mssql datetime - sql server date formats - sql dates format
-- MSSQL getdate returns current system date and time in standard internal format
-- SQL datetime formats with century (YYYY or CCYY format)- sql 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
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
-- 2010-10-02T10:52:47.513
-- 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
------------
-- SQL Server date formatting function - convert datetime to string
------------
-- SQL datetime functions - SQL date functions - SQL datetime formatting
-- SQL Server date formats - sql server date datetime - sql date formatting
-- T-SQL convert dates - T-SQL date formats - Transact-SQL date formats
-- Formatting dates sql server - sql convert datetime format
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 dates - sql datetime date
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
GO
------------
/***** 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)
------------
-- 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
*/

------------

-- 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
------------
-- 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

------------
-- 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
-- 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
------------
-- 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
------------