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

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

Advertisements

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

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

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

 

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)

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)) )