.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Return year only from datetime field

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :ASP.Net

Using SQL Server 2005.

I need to return the year only from a datetime field and then group by that field, does anyone know the syntax for this?



View Complete Post

More Related Resource Links

Comparing Datetime field in DB?

I need to check the datetime field in my DB to see if the time that's current set is 12hr's past? What's the command to use?  Can I just DATEDIFF? 

Flat file upload: Error when inserting into datetime field.

Hi All I am having an issue with uploading a txt file into an SQL table and one of the fields needs to go into a datetime column. The issue is with column3 I have been into the advanced editor of the flat file source and set the output column to decimal and it fails. The error I am getting is below: Error: 0xC02020A1 at Materials Transfer, Flat File Source [1]: Data conversion failed. The data conversion for column "Column 3" returned status value 2 and status text "The value could not be converted because of a potential loss of data.". Error: 0xC0209029 at Materials Transfer, Flat File Source [1]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "output column "Column 3" (48)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "Column 3" (48)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure. Error: 0xC0202092 at Materials Transfer, Flat File Source [1]: An error occurred while processing file "D:\Development\r3_downloads\mat.txt" on data row 1. Any help would be great.   Thanks BigGopher

Showing the value as blank in a grid view when the Datetime field in SQL has its minimum value

Hi,Developing an application in C#.Retrieving data using SQL server. A record has a datetime field( eg: Resigned Date). If the datetime field in null in my code I will have the minimum value stored in the database which is "1/1/1900" ( eg: for current employees as they do not have a resigned date this field will have the 1/1/1900 value as I cannot leave this blank)Now when I'm displaying the employee records in a gridview in my application it shows the 1/1/1900 value for the "Resigned Date" field. I need this to be blank.Here is the code that I have done so far...void GetRecords(){DBConnect db = new DBConnect();DataSet ds = new DataSet();ds = db.getDataForgrid("Select * from Employee");DataTable dt = ds.Tables[0];GridView1.DataSource = dt;GridView1.DataBind();}Please help.Thanks

Set the DateTime field using javascript

Hi everyone, I need Help in implementing a solution. I have 2 fields: Resolution Date (DateTime) and Severity (Dropdown) and I need depending on the choise in dropdown (0-3) set different dates into the DateTime field. The difficult part is that I can't realize how is to SET the date there %) Here is my code: var ExpDateResolved = $("input[title=Expected Resolution Date]").first(); $("select[title=Severity]").first().change(function(){ ExpDateResolved.val(''); var chbReqSevDom = $(this).get(0); if (chbReqSevDom.selectedIndex == 0)  //first value in dropdown selected { var currentDate = new Date(); DateResolved.val(zeroPad(currentDate.getDate(),2) + '.' + zeroPad((currentDate.getMonth()+1),2) + '.' + currentDate.getFullYear()); } } P.S. I've tried to set a date into a Text field and it works.

YEAR --WEEK of the YEAR -- DAY of the WEEK to datetime

Good Evening Can someone help me covert a string in the format YEAR --WEEK of the YEAR(1-53)  -- DAY of the WEEK(1-7)to datetime ie 2010-38-6 is today = '2010-09-17'  valid sql server date nb day numbers are 1 to 7, 1= sunday thanks   David          

Formatting datatable datetime field

I am getting an error message:Conversion from string "mm/dd/yyyy" to type 'Integer' is not valid.  What is best way to format date filed from datatable without showing the time?   ClosingDate is smalldatetime in my SQL DB. ThankslblClosingDate.Text = dt.Rows(0).Item("ClosingDate").ToString("mm/dd/yyyy")

How to return a specific field from the last record entered


I have two tables, Discrepancies and Actions that are linked by a field called JobNumber

What I need to do in a stored procedure is return a date field from the last record entered in the Actions table where the JobNumber is the same

The Actions tables primary key is an Identity field.

I'm trying to make a subquery but am stuck on the syntax.

Any help would be appreciated, this is just a bit beyond my skill set. Thanks.

Onchange event for datetime field in javascript is not working?


I want to compare start date and end date when start date is changed. If start date is big, on change event i need to make the end date the same as start date.

So I wrote On change event for start date. But it doesnot work  

getField('input','Start Time').onchange = function() {showEndDate()};


function showEndDate()



var date1 = getField('input','Start Time');

var date2 = getField('input','End Time');


but its not firing at all. So you guys have any idea or any other way to do for datetime on change event?

getting time and date from datetime field in javascript


Hello guys, I need to get hours from the difference between two datetime fields. I wrote the javascript which will get the date from datetime field.

But its not getting the correct time that entered on date time field. Its defaultly getting the current time on my system.

So do you guys have any idea. How to get time from date time fields. and how calculate hours between two datetime fields using javascript.

Named Sets vs. Calculated Members and the Filter Field Conundrum (Year-to-Date measures by company c


I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

create an index on just the date part of a datetime field


Good Morning

is it possible to create an index on a datetime field that just looks at the date part,

as I am constantly querying the table, using a where clause like this


or is there an more efficient method of achieving the same result?







How to query a datetime field in access database using ASP.NET



I am using an Access database with one of the Table columns defined as Date/Time field.


I am trying to use ASP.NET 4.0 Web forms application to query and retrieve the row based on the DateTime field.

Here is my code snippet:

      string connectionString = WebConfigurationManager.ConnectionStrings["AccessDB"].ConnectionString;

      OleDbConnection oleDBConn = new OleDbConnection(connectionString);

      DateTime dt = new DateTime(dtBatchCreation.Year, dtBatchCreation.Month, dtBatchCreation.Day, dtBatchCreation.Hour, dtBatchCreation.Minute, dtBatchCreation.Second);

      //string sql = "SELECT [BatchID] FROM [Batch] WHERE [BatchCreationDate] = " + String.Format("{0:#yyyy-MM-dd hh:mm:ss tt#}", dt);
      string sql = "SELECT [BatchID] FROM [Batch] WHERE [BatchCreationDate] = @BatchDate";

      string s = @String.Format("{0:yyyy-MM-dd HH:mm:ss}", dt);

      OleDbCommand cmd = new OleDbCommand(sql, oleDBConn);

      //cmd.Parameters.AddWithValue("@BatchDate", s);


Get datetime out in 3 dropdownlists (day, month, year)



How do I get my Datetime out in 3 dropdownlists :

ddlDay = Day

ddlMonth = Month

ddlYear = Year


Problem in properties.AfterProperties with respect to DateTime field.

Hi all,

I have an event handler, ItemAdding and I am trying to get start and end for "Calendar List".

For,  StartDate = Convert.ToDateTime(properties.AfterProperties["EventDate"]); its showing next day.
For e.g, User took 5 day vacation from 12 Jan to 16 Jan. But for StartDate its showing 13 Jan and also for end date i.e. 17th.

I tried some reserach on about this issue: 

But in my case, properties.ListItem is null.
SPListItem currentItem = properties.ListItem;
DateTime beforeValue = System.Convert.ToDateTime(currentItem.Fields[displayName].GetFieldValueForEdit(currentItem[displayName]));
properties.AfterProperties[internalFieldName] = SPUtility.CreateISO8601DateTimeFromSystemDateTime(beforeValue.AddDays(1)).ToString();

This is my complete code for ItemAdding

Re: InfoPath 2010 dataconnection to SPList (LookUP field return id)



I designed a form in infopath 2010, created a dataconnection to Sharepoint 2010 List, query and data fields include lookup field.

When i expand field tree (Secondry), i can see following data on mouse hover of the field.

MyXYZField (Field)

DataType (Lookup)

Default Type (None)

I then added a dropdown control on the form , using dropdown properties wizard configure MYXYZField as the source.

When i preview the form i only see dropdown filled with LookupID (1,2,3 etc) instead of the text of the dropdown.


I must be missing something here, please help.










Create Doc Lib View Based on Year in Modfied Field

Hi I'm trying to create a view on a document library that will sort items based on year.  I would need to get the year from the Modified field.  Like maybe I could have one view for 2008, one for 2009, and one for 2010.  I'm new to using formulas in views.  Any ideas?

BCS - Datetime field and time zones


I have an external list that has a datetime column. I get the value for this column from my SQL server database.

The value in the database is '2010-10-30 17:00:00.000', but when the value is displayed in my external list it is converted to '2010-10-30 18:00:00.000'. This is done because the time zone for my sharepoint site is set to UTC+1.

I do not want SharePoint to convert the datetime. I want the value in external list to be '2010-10-30 17:00:00.000' no matter what the time zone is set to on my site.

How can I do that?

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend