Mega Techno Tips

Saturday, July 14, 2018

SQL Puzzle – How Does YEAR Function Work?

There are many different datetime related functions such as DAY, MONTH, YEAR, DATEDIFF, etc available in SQL Server. For example, YEAR function can be used to extract year value from a date.

Let me show you a simple example

1

2

3

DECLARE @TRANSACTION_DATE DATETIME

SET @TRANSACTION_DATE='2016-10-19 15:20:30'

SELECT YEAR(@TRANSACTION_DATE) AS TRANSACTION_YEAR

The result will be 2016

Puzzle – Year Function
Now let us see an exciting mystery which involves Year Function.

Now execute the following SELECT statement

SELECT YEAR(35000/20) AS YEAR_VALUE

When you ran the above script, it gives us a result as the year 1904.

This is indeed a strange result. As you can see that 35000/20 is not a valid date value. 

The matter of the fact 35000/20 is results in value 1750.

Puzzle: How does SQL Server consider this as a valid date and return year value as 1904?

Please leave your answers in the comment sections.

 I will be publishing all the valid answers next week same time. 

I suggest you share this with your friends who know SQL and see if they can solve this or not.

 I promise you that the answer to this puzzle is straightforward,

 once you know the trick behind it.

Friday, August 14, 2015

jQuery Autocomplete Textbox in Asp.net with Database Example using C#

Here I will explain how to fill a jQuery autocomplete textbox from database in asp.net usingc#, with example or jQuery autocomplete textbox example in asp.net with database using c#, and show / display no results found message in autocomplete textbox when no matching records found in asp.net using c#.

Before implement this example first design one table userdetails in your database like as shown below:

Column Name	Data Type	Allow Nulls
UserId	Int(IDENTITY=TRUE)	No
UserName	varchar(50)	Yes
Education	varchar(50)	Yes
Location	varchar(50)	Yes

<head>

<title>jQuery Show Records Found Message in AutoComplete</title>

$(function () {

SearchText();

});

function SearchText() {

$(".autosuggest").autocomplete({

source: function (request, response) {

$.ajax({

type: "POST",

contentType: "application/json; charset=utf-8",

url: "jQueryAutoCompleteTextbox.aspx/GetAutoCompleteData",

data: "{'username':'" + $('#txtSearch').val() + "'}",

dataType: "json",

success: function (data) {

if (data.d.length >0) {

response($.map(data.d, function (item) {

return {

label: item.split('/')[0],

val: item.split('/')[1]

}

}));

}

else {

response([{ label: 'No Records Found', val: -1}]);

}

error: function (result) {

alert("Error");

}

});

select: function (event, ui) {

if (ui.item.val == -1) {

return false;

}

$('#lblUserId').text(ui.item.val);

}

});

}

</script>

</head>

<body>

<asp:HiddenField ID="hdnId" runat="server" />

<label for="tbAuto">Enter UserName: </label>

</div>

<div>

Selected UserId:<b><label id="lblUserId" /></b>

</div>

</form>

</body>

</html>

using System;

using System.Collections.Generic;

using System.Web.Services;

using System.Data.SqlClient;

After completion of adding namespaces you need to write the code like as shown below

[WebMethod]

public static List<string> GetAutoCompleteData(string username)

{

List<string> result = new List<string>();

using (SqlConnection con = new SqlConnection("Data Source=Suresh;Integrated Security=true;

Initial Catalog=MySampleDB"))

{

using (SqlCommand cmd = new SqlCommand("select UserId,UserName from userdetails where UserName LIKE '%'+@SearchText+'%'", con))

{

con.Open();

cmd.Parameters.AddWithValue("@SearchText", username);

SqlDataReader dr = cmd.ExecuteReader();

while (dr.Read())

{

result.Add(string.Format("{0}/{1}", dr["UserName"], dr["UserId"]));

}

return result;

}