Last datetime (or beginning) of the month, week, and year with sql

Posted: September 18, 2008 | Author: | Filed under: SQL Server | Leave a comment
Some usefull datetime functions. To retrieve this month records, it can be done this way:

….WHERE checkedintime>DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE()),0))  AND checkedintime< =DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0))

–SELECT DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0)) as LastDayTime_CurrentMonth

–SELECT DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE()),0)) as LastDayTime_PreviousMonth

 
A list of these functions for other usage.
–Quarter
SELECT  DATEADD(Q, DATEDIFF(Q, 0, getdate()) +1 , 0) as endOfQuarter,

DATEADD

(Q, DATEDIFF(Q, 0, getdate()) , 0) as beginningQuarter

SELECT

DATEADD(DD, 1– DATEPART(DW, getdate()), DateDiff(day, 0, GETDATE())) as beginningOfThisSunday

SELECT

DATEADD(DD, 1– DATEPART(DW, getdate()), 7 + DateDiff(day, 0, GETDATE())) as beginningOfNextSunday

SELECT

DATEADD(DD, 1– DATEPART(day, getdate()), DateDiff(day, 0, GETDATE())) as beginningOfThisMonth

or

SELECT

DATEADD(mm, DATEDIFF(mm,0,GETDATE())-1,0) as StartOfLastMonth

SELECT

DATEADD(mm, DATEDIFF(mm,0,GETDATE()),0) as StartOfThisMonth

SELECT

DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0) as StartOfNextMonth

 

SELECT

DATEADD(day, DATEDIFF(day,0,GETDATE()),0) as StartOftheday

SELECT

DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0) as StartOfNextday

 

SELECT

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) as LastDay_CurrentMonth

SELECT

DATEADD(s,-1, DATEADD(day, DATEDIFF(day,0,GETDATE()),0)) as LastDayTime_PreviousDay

SELECT

DATEADD(s,-1, DATEADD(day, DATEDIFF(day,0,GETDATE())+1,0)) as LastDayTime_CurrentDay

SELECT

DATEADD(s,-1, DATEADD(year, DATEDIFF(year,0,GETDATE()),0)) as LastDayTime_PreviousYear

SELECT

DATEADD(s,-1, DATEADD(year, DATEDIFF(year,0,GETDATE())+1,0)) as LastDayTime_CurrentYear

SELECT

DATEADD(s,-1, DATEADD(week, DATEDIFF(week,0,GETDATE()),0)) as LastDayTime_PreviousWeek

SELECT

DATEADD(s,-1, DATEADD(week, DATEDIFF(week,0,GETDATE())+1,0)) as LastDayTime_CurrentWeek

 

SELECT

DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE())+1,0)) as LastDayTime_CurrentMonth

SELECT

DATEADD(s,-1, DATEADD(month, DATEDIFF(month,0,GETDATE()),0)) as LastDayTime_PreviousMonth

SELECT

  DATEADD(ms,-3, DATEADD(year, DATEDIFF(year,0,GETDATE()),0)) as LastDayTimeOfPreviousYear

SELECT

DATEADD(ms,-3, DATEADD(year, DATEDIFF(year,0,GETDATE())+1,0)) as beginningOfNextYear

Select All (Any) From a DropDownList with range items in ASP.NET

Posted: September 17, 2008 | Author: | Filed under: ASP.NET 2 | Leave a comment 
SQL part:
Declare @length decimal(8,1)
SET
 @length=9.5
SELECT
 length FROM mylength
WHERE
 
@length 
IS NULL Or
length
 >=@length and length < 
CASE
 
WHEN
 @length= 7.5 THEN 8 
WHEN
 @length= 8.5 THEN 9 
WHEN
 @length= 9.5 THEN 10 
WHEN
 @length= 10.5 THEN 15 
WHEN @length= 15.5 THEN 20 
WHEN
 @length= 20.5 THEN 25 
WHEN
 @length= 25.5 THEN 30 
WHEN
 @length= 30.5 THEN 35 
WHEN
 @length= 35.5 THEN 45 
WHEN
 @length= 45.5 THEN 60 
WHEN
 @length= 60.5 THEN 300 
ELSE
1000.0
END
A complete sample ASP.NET page working with the DropDownList:
<asp:DropDownList ID="DropDownListLength" runat="server" AutoPostBack="true" >
<asp:ListItem Value="">Any</asp:ListItem>
<asp:ListItem Value="0.0">7" and under"</asp:ListItem>
<asp:ListItem Value="7.5">7.5" - 8"</asp:ListItem>
<asp:ListItem Value="8.5">8.5" - 9"</asp:ListItem>
<asp:ListItem Value="9.5">9.5" - 10"</asp:ListItem>
<asp:ListItem Value="10.5">10.5" - 15"</asp:ListItem>
<asp:ListItem Value="15.5">15.5" - 20"</asp:ListItem>
<asp:ListItem Value="20.5">20.5" - 25"</asp:ListItem>
<asp:ListItem Value="25.5">25.5" - 30"</asp:ListItem>
<asp:ListItem Value="30.5">30.5" - 35"</asp:ListItem>
<asp:ListItem Value="35.5">35.5" - 45"</asp:ListItem>
<asp:ListItem Value="45.5">45.5" - 60"</asp:ListItem>
<asp:ListItem Value="60.5">60.5" Plus</asp:ListItem>
</asp:DropDownList>

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="id" SortExpression="id" />
                <asp:BoundField DataField="length" HeaderText="length" SortExpression="length" />
            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:forumConnectionString %>" CancelSelectOnNullParameter="false"
            SelectCommand="SELECT [id], [length] FROM [myLength] WHERE  @length IS NULL Or (length >@length and length < CASE  WHEN @length= 7.5 THEN 8 WHEN @length= 8.5 THEN 9 WHEN @length= 9.5 THEN 10	WHEN @length= 10.5 THEN 15 WHEN @length= 15.5 THEN 20 WHEN @length= 20.5 THEN 25 WHEN @length= 25.5 THEN 30	WHEN @length= 30.5 THEN 35	WHEN @length= 35.5 THEN 45	WHEN @length= 45.5 THEN 60	WHEN @length= 60.5 THEN 300 ELSE 1000.0 END)">
            <SelectParameters>
            <asp:ControlParameter Name="length" ControlID="DropDownListLength" PropertyName="SelectedValue" Type="Decimal" />
            </SelectParameters></asp:SqlDataSource>


 


Question asked from here: http://forums.asp.net/p/1321016/2627669.aspx#2627669

Use two languages in the same project with ASP.NET 2.0

Posted: September 15, 2008 | Author: | Filed under: ASP.NET 2 | Leave a comment
I need to migrate a five year old project written in VB with a few added functions in C#. This can be done easily with a separate sub folder in the App_Code along the following in web.config file.
<compilation>
    <codeSubDirectories>
        <add directoryName="VBCode" />
<add directoryName="CSharpCode" />
    </codeSubDirectories>
</compilation>
 
Done!

retrieve Data within this week using SQL

Posted: September 13, 2008 | Author: | Filed under: SQL Server | Leave a comment
 

SET DATEFIRST 7

SELECT DATEADD(DD, 1– DATEPART(DW, getdate()), DateDiff(day, 0, GETDATE())) as beginningOfThisSunday

SELECT DATEADD(DD, 1– DATEPART(DW, getdate()), 7 + DateDiff(day, 0, GETDATE())) as beginningOfNextSunday

 

SELECT * FROM yourtable WHERE yourDTcolumn>=DATEADD(DD, 1– DATEPART(DW, getdate()), DateDiff(day, 0, GETDATE()))

AND yourDTcolumn<DATEADD(DD, 1– DATEPART(DW, getdate()), 7 + DateDiff(day, 0, GETDATE()))

Extract part of a string by using SQL functions (substring, charindex, reverse and len)

Posted: September 11, 2008 | Author: | Filed under: SQL Server | Leave a comment
A user wants to get part of his invoice number column from a table. The invoice_No data format is: BC123456-789123-LL or LL123456-789123-L.
Here is the query:
 
SELECT  SUBSTRING([Invoice_No],charindex(‘-‘,Invoice_No)+1,len(Invoice_No)-charindex(‘-‘,Invoice_No) – charindex(‘-‘,REVERSE(Invoice_No)) ) as newString  FROM table1
 
The new string can be updated to a new column Invoice_No2:

UPDATE table1

SET  Invoice_No2=SUBSTRING([Invoice_No],charindex(‘-‘,Invoice_No)+1,len(Invoice_No)-charindex(‘-‘,Invoice_No) – charindex(‘-‘,REVERSE(Invoice_No)) )