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

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

Be aware of using DAY(DATEADD("Day", -x, ) )

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
I saw so many people using the following expression in naming their file name. "\\myPath\\myFolder\\myFile_" + (DT_WSTR, 4) YEAR( GETDATE()  )  + (DT_WSTR, 2)MONTH( GETDATE()  ) +  (DT_WSTR, 2) DAY( DATEADD("d", -SomeDays, GETDATE())  ) + ".txt" I would like to point out an issue that I had found with this expression. Let' say today is 03 September 2010, and SomeDays = -1. Then the result of the expression will be "\\myPath\\myFolder\\myFile_20100902.txt", which is correct. How about if SomeDays = -3, the result will be "\\myPath\\myFolder\\myFile_20100931.txt". Can you spot the mistake? That's right, the day is correct. BUT, the month is incorrect. It should be 08 instead of 09. And this could happen with the YEAR too if you're using DATEADD for month. To avoid this issue, simply uses DATEADD(<date interval>, -Somedays, GETDATE() ), in YEAR, MONTH and DAY. I hope this helps. Cheers, Uzzie

View Complete Post

More Related Resource Links

Windows Mobile: Use GPS And Web Maps For Location-Aware Apps


We show you how to build a location-aware task list application for Windows Mobile devices that uses GPS to remind you of a task when and where it's appropriate.

Christopher Mitchell

MSDN Magazine January 2009

Mobility: Make Your WPF Apps Power-Aware


Here Andre Michaud shows you how to use power notifications to make your applications power aware.

Andre Michaud

MSDN Magazine July 2007

Resource File: Creating Privacy-aware Web Sites


Privacy issues are of primary concern to those involved in Internet commerce. Some consumers are hesitant to provide information to Web sites without clearly understanding how their data will be used and with whom it will be shared.

MSDN Magazine August 2003

Web-Aware Apps: Build Hyperlinks into Your Client App with the Windows Forms LinkLabel Control


LinkLabels are Windows Forms controls that enable a user to hyperlink to a URL that points to either the Web or the local directory system. While the SDK documentation discusses the control, it does not demonstrate how linking is accomplished, nor does it outline the power and flexibility the control provides. This article fills those gaps, showing how to link using the LinkLabel control. It also shows the flexibility of this control for the programmer using the .NET Framework. In addition, the author covers the large number of properties that allow you to customize your controls and accurately place them. Their built-in behaviors are also discussed, along with their use in both Visual Basic .NET and C#.

Dan Hurwitz

MSDN Magazine February 2003

SO-Aware webinar

Are you interested on the strategies and tools that will help you to manage WCF services? Struggling to keep up with your WCF configuration? Looking for a service registry/repository solution that works seamlessness with WCF? Wondering how to effectively test, monitor, catalog your WCF services? This Wednesday at 2pm US EST, I will be presenting a webinar (http://www.regonline.com/register/checkin.aspx?EventId=882502) that will cover in detail the concepts, motivation and value proposition behind...(read more)

Centralizing Federated Services configuration with SO-Aware

Configuring a WCF service to use federated authentication in an organization is not something trivial as it requires some good knowledge of the available security settings, and more precisely, how to talk to the existing security token services with the right WCF bindings. This is something that usually only a few people in the organization knows how to do it right, so having a way to centralize all this configuration in a central location and have the rest of the developers to use becomes really important.     SO-Aware plays an important role in that sense, allowing the security experts to configure and store the bindings and behaviors that the organization will use to secure the services in the service repository. Developers can later reference, reuse and configure their services and client applications with those bindings from the repository using a simple OData API, or the WCF specific classes that SO-Aware also provides for configuring services and proxies. A WCF binding for configuring a service with federated authentication usually looks as follow, <customBinding><binding name="echoClaimsBinding"> <security authenticationMode="IssuedToken" messageSecurityVersion="WSSecurity11WSTrust13WSSecureConversation13WSSecurityPolicy12BasicSecurityProfile10" requireSecurityContextCancellation="false"> <issuedTokenPa

Listening to customers: A simpler SO-Aware installer

Simplicity and the ability to listen to customers are some of the principles behind SO-Aware and Tellago Studios for that matter. Building sophisticated software that is still simple and intuitive from the user/operator perspective is one of those fundamental missing links in enterprise software. The ability of incorporating valuable customer feedback into your product quickly is one of those things that everybody says they do but only a handful of companies truly believe on. Having a team that is...(read more)

Managing the SO-Aware Repository with PowerShell

As Jesus mentioned in this post, SO-Aware provides three interfaces for managing the service repository. An OData API in case you want to integrate third applications with the repository. OData is a pure http API that can be easily consumed in any platform using a simple http client library. The management portal, which is an ASP.NET MVC user interface layered on top of the OData API and probably the one most people will use. And finally, a PowerShell provider that also mounts on top of the OData API to allow administrators to automate management tasks over the repository with scripting.  The SO-Aware PowerShell provider, in that sense offers around 40 commands that enables simple management scenarios like registering bindings or services or more complex scenarios that involves testing services or sending alerts when a service is not properly working.   This provider can be registered as an snapin in an existing script using the following command, $snapin = get-pssnapin | select-string "SOAwareSnapIn"if ($snapin -eq $null){ Add-PSSnapin "SOAwareSnapIn"} Once you have registered the snapin, you can start using most of the commands for managing the repository. The first and more important command is "Set-SWEndpoint", which allows you to connect to an existing SO-Aware instance. This command receives the OData service location as first argument, and it looks a

Using DateAdd from a table

Hi there, I already have a table with all the weekends and holidays defined. what I'm trying to do is just to add 10 days to a date but using the data from the table I already have to exclude weekends and holidays in there. Let me know if I'm making sense here please. Thanks very much. Manny  

making custom apps "cluster aware"

what proactive steps can we take to make our app (ado, stored procs etc) cluster aware, in the event that we someday use clustering?  For instance, I read things about retry strategies etc but dont even know if retry is on a different connection or what appears to be the same connection.  We already make our trans as small as possible.

DATEADD Bug/Feature?

Just trying to troubleshoot an issue with one of my developers and came across this "feature" with DATEADD. He had something akin to, SELECT DATEADD(Y,-1,DateColumn) and couldn't understand why it wasn't returning the correct data. It turns out that using the datapart Y in this context actually runs as if it were D. I checked the documentation in BOL which states that YY or YYYY are valid dateparts for year which i accept. But it surprises me that Y doesn't work and worse than that, doesn't throw an error. If i use the datapart X i get an error "X' is not a recognized dateadd option". I'm using SQL 2008 SP1. Just thought i'd sound out the forums before raising this on Connect.every day is a school day

Webinar: WCF and BizTalk Governance using SO-Aware

Hi Everyone,   I wanted to let everyone know about an upcoming webinar on using BizTalk with SO-Aware to help govern your WCF Endpoints, Use the SO-Aware ESB Resolver, and other things related to BizTalk. Jesus Rodriguez has a great post about how you can use BizTalk Adapters with SO-Aware here: http://tellagostudios.com/simplifying-biztalk-adapters-configuration-using-so-aware. These are just a few of the things we're going to be discussing. You can register/sign up here: https://www.regonline.com/BizTalkGovernanceUsingSOAware. Thanks  MCT, MCSD.NET, BizTalk TS

Tough question: role-aware profiles

Hello,I have been playing a bit with ASP.NET MVC 2 using VWD 2010.After a bit of struggling with profiles(thank you MS for putting 2 web.config files in the solution...) I decided that after all I like this technology. I always thought that dotNET would be great for server side stuff and I've been proven right.Anyhow, I'm gonna start a real project now and I want to do something weird(yes, I know, I shouldn't be wanting to do weird things, but it's just beyond me :-) ): say I have a company website with roles and a forum.When I click on the nickname of a user, I want to bring his/her profile up - just like you would be doing with any decent forum out there. Well, my final idea is a tad more interesting in this regard, but let's stick it at that: click and see profile page.Problem is, I want this profile to be different for company employees and users. I want some general informations such as first and last name, date of birth, photo, etc and then for instance I do not want to show the address property for company users. Yes I know I might want to put the company address there or leave it at all blank and I would rather not display it at all.From what I gather about profiles, this is possible only using views and checking for the roles, doing something like:if User.Role == Company{   // Stuff here}As I understand it, there is no way to do it in the

DynamicData SiteMap with PrimaryKey Filter aware


I looked At the sitemap created by Mr. Steve, at it's great

But I want to add filtering to the parent table as the sample below :

sample :

Order -->OrderDetailes

OrderDetails :PrimaryKey : productID,OrderID

filter applied at the OrderDetailes/list.aspx is  productid=1 ,orderid=10

when I click on order I want to get Order/list.aspx with Filter ProductId=1


Help make a bug aware to the IE9 team


Hi fellow developers.

Can you please test the following senario and submit your results to the bug report for IE9 Beta.

First install IE9. Then create a new win forms application and put a listview on a form (or simply open any current win forms project) and change the listview to have a background image. Now run the application and look at the listview, the background should wither be black or render invalid images (currupt pointer potentialy causing this).

You can then uninstall IE9 and the error will be gone. I belive the IE9 application updates the core Listview which is why when its uninstalled it corrects the issue, but any other input would be helpful for them.


DATEADD in Where Clause


I have data I need to query based on local time, but the timestamp in the table is central time because our server is in central time. We have devices all over the country so local time varies widely.

So we created a field called CentralTimeOffset that holds the value of + or - the number of hours the record is from central time zone. So for example if the local time for the record is Mountain then CentralTimeOffset field contains -1, if the local time for the record is Eastern then the CentralTimeOffset contains 1. I want to use DateAdd to convert central time to the appropriate local time for each record.

The query I run to get the data is date range based, and I need to make sure my data is converted to the local time zone first so that the StartDate and EndDate are comparing against local not central. Here is what I have in the where field, I just need to confirm it’s doing what I think it’s doing:

WHERE   &n

T-SQL Dateadd function

How can I automatically declare the last 6 COMPLETE months?

For example, today is 1/20/10, so I would like the StartDate to be 7/1/09 and the end date to be 12/31/09.

This is what I currently use:
set @@StartDate = '2009-07-01'
set @@EndDate = '2009-12-31'

How can I write this in t-sql using the dateadd function so that it is automatic and I don't need to manually change the code every time?

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