Get first date of baselined month

I had been working on one query where I need to find the data between the date range and the start date range should be from the 1st date of month of October. So here is a simple solution for the same. What you can do is if you need April 1st to be the start of your fiscal year just change the if condition from 10 to 4 And replace '10/01' with '04/01' /*****************************************************************************************/ declare @dtFiscalYearStart datetime if (month(getdate()) <= 10) --This will check for the Month of October in this case set @dtFiscalYearStart = convert(datetime, convert(varchar(4),(year(getdate()) -1)) + '/10/01',21) else set @dtFiscalYearStart = convert(datetime, convert(varchar(4),(year(getdate()))) + '/10/01',21) select @dtFiscalYearStart /*****************************************************************************************/

Common Mistakes When Searching for Dates:

When searching for dates there are a number of common mistakes that new SQL Server programmers sometimes make. In this section, I will show you two common date/time pitfalls.

The intent of this first example is to select all the records in the DATE_SAMPLE table that have a SAMPLE_DATE equal to '2003-04-09'. Here is the code:

"SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE = '2003-04-09'"

When this code is run only record 4 is returned. Why are records 5, 6 & 7 not returned? Can you tell why? Remember DATETIME, or SMALLDATE columns contain not only the date but also the time. In this particular example SAMPLE_DATE is a DATETIME column, so all the dates store contain a time, down to the milliseconds. When you specify a search criteria that only contains a date, like the above example, SQL Server needs to first convert the string expression '2003-04-09' to a date and time value, prior to matching the string with the values in the SAMPLE_DATE column. This conversion creates a value of '2003-04-09 00:00:00.000', which matches with only record 4.

Another common mistake is to use the BETWEEN verb like so:

"SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between '2003-04-09' AND '2003-04-10'"

When using the BETWEEN verb all records that are between or equal to the dates specified are returned. Now if in my example above I only wanted to return records that have a SAMPLE_DATE in '2003-04-09'. This example returns all the records that have a SAMPLE_DATE in '2003-04-09' (records 4 - 7), but also returns record 8 that has a SAMPLE_DATE of '2003-04-10'. Since the BETWEEN clause is inclusive of the two dates specified, record 8 is also returned.

Now if you really desire to select all the records in the DATE_SAMPLE table that have a SAMPLE_DATE sometime in '2003-04-09' you have a couple of options. Let me go through each option and then explain why one might be better than another might.

Using the Convert Function:

This first example selects all records from the DATE_SAMPLE where the date portion of the SAMPLE_DATE is equal to '2003-04-09'.

"SELECT * FROM DATE_SAMPLE  WHERE CONVERT(CHAR(10),SAMPLE_DATE,120) = '2003-04-09'"
The reason this example works, and the first example above does not, is because this example removes the time portion of the SAMPLE_DATE column prior to the comparison with string '2003-04-09' being performed. The CONVERT function removes the time portion by truncating the value of the SAMPLE_DATE field to only the first 10 characters.SELECT * FROM DATE_SAMPLE  WHERE CONVERT(CHAR(10),SAMPLE_DATE,120) = '2003-04-09'

Comments

Post a Comment

Popular posts from this blog

WCF WSDL location address Issue resolved when hosted over HTTPS with basicHTTPBinding

Image
Recently I was working on a WCF web service which needs SSL implementation using basicHttpBinding, so that the service remains inter-operable and cater to native clients. Everything was going well, I was able to host my web service over HTTPS (I will detail out the configuration for the same in section below). But when I deployed on Server, following errors were received when proxy is generated: " Metadata contains a reference that cannot be resolved: " " The WSDL document contains links that could not be resolved." The issue lies with the WSDL which generates the location url with client machine name, which restricts generation of appropriate proxy over Internet because it will never be able to resolve machine name over internet. Here is what the Soap Address location and WSDL Import location look like in my WSDL: < soap:address location ="https://MachineName.mydomain.com/MyService/Service.svc" /> < wsdl:import   locati
Read more

Yellow Background issue in Word to PDF conversion

Image
For those who are use to save Word file as PDF might have faced the issue of Yellow background in PDF file post saving (see screenshot)  Below are the simple steps to resolve this issue. You can set a background color for a Word document, but it is only visible in  Web Layout . The background will appear white in  Print Layout . Use the  View  tab's  Document Views  group,  Web Layout  command to see it one has been set. If it has, it can be removed from the  Page Layout  tab's  Page Background ,  Page Color  options. Normally, the colored page background would not print. However, there is a Word Option to  Print background colors and images . Click the  Office button, Word options, Display  and look in the bottom section,  Printing Options  for  Print background colors and images  and unchecked it. Hope this help.
Read more

Gmail tricks - create unlimited siblings of your Gmail address

Use (+) to create unlimited siblings of your Gmail address Many times, you need multiple email accounts for testing and the options you have is to create free accounts imaging if you have to create 100+ accounts for testing and then to test you have to open each and every account to check for the emails. A simple workaround is to get a gmail account and just append a plus (“+”) sign after your email address and after that, you can insert any combination of words or numbers to create any number of personalized email IDs with the same inbox. For instance, if your current email ID is  myname@gmail.com , you can modify email to  myname +sibling1@gmail.com  or  myname +sibling2 @gmail.com  or use any combination to still receive an email with the same ID,  myname@gmail.com . Hence using this Gmail trick, you will be able to create multiple aliases of your primary ID and that too without tweaking any setting or configuration. Tips: You can make use of multiple aliases to sign
Read more