Filtering SQL Statement With Datepart
Jun 15, 2006
Hi!
Im making a page where I want to display this weeks information & news as a default setting.
What do I have to do to make it work (Im new to .Net but used to ASP 3 & MySql)?
My idea is to store the week number in a variable which is updated att Page_load and then
to use this variable in the following sql statement that gets news and info from the MsSql
database where weeknumber is = the defined variable.
So how do I store weeknumber in a variable?
AND
How do I use this variable in the SQL statement?
View 1 Replies
ADVERTISEMENT
Sep 19, 2000
We are looking on how to do a between statement for comparing only month and days(such as birthday.) The proc will be fed 2 parameters and we want to check if a date field (only the month and day) fall between the parameters. Such as the parms are 09/01/2000 and 10/31/2000
I want to return all records where the date field is
between 09/01/xxxx and 10/31/xxxx. I tried the datepart function but couldn't find a way to combine them. Any solution greatly appreciated.
View 9 Replies
View Related
May 20, 2004
I have a perculiar instance when I must use datepart in an update statement, but it seems that all the parentheses are erroring out the code. Here's what I'd like to write:
UPDATE [Event Times] SET
[Day of Week] = 6, [Recurrence Type] = 0,
DATEPART([hour], [Start Time]) = 09,
DATEPART([minute], [Start Time]) = 00
WHERE ([Event id] = 4) AND ([Day of Week] = 6) AND ([Recurrence Type] = 0 )
Any Ideas? Thanks in advance!
View 4 Replies
View Related
Apr 27, 2007
Hi
Hope somebody can help me with a problem that i am having. I have the following piece of code
<%
Dim sqlConn As SqlConnection
Dim sqlCmd As SqlCommand
Dim sqlReader As SqlDataReader
sqlConn = New SqlConnection("server=myserver;database=aspnet;uid=username;pwd=password")
sqlCmd = New SqlCommand("SELECT SamAccount, FirstName, Surname, FirstName + N' ' + Surname+' Logged In ' AS UserLoggedIn FROM tblUsers WHERE (SamAccount = " + Request.ServerVariables("logon_user") + ")", sqlConn)
sqlConn.Open()
sqlReader = sqlCmd.ExecuteReader()
%>
<%
Dim strPCNAme As String
sqlReader.Read()
strPCNAme = sqlReader("UserLoggedIn")
Response.Write(strPCNAme)
%>
The bit i have i have underlined and made bold throws up the error. Basically what i am trying to do is to filter the the SQL statement by the server variable logon user. Everthing works fine until the point that i put the where clase in.
Can anybody help please?
Thanks
Marcus
View 2 Replies
View Related
Dec 2, 2005
My objective is to only get the records for Current USER's region and district. Please let me know
what I am missing....Thank you!!!
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
bindGrid();
}
}
private void bindGrid()
{
@username=HttpContext.Current.User.Identity.Name;
SqlCommand getCommand = new SqlCommand();
getCommand.Connection=sqlConnection1;
getCommand.CommandText="SELECT DealerCode,DealerName,ClaimCount," +
"Total$,CliamDays FROM DealerList,pwausers,pwadlrs WHERE" +
"@username=user_name AND parts_reg=dmpreg AND parts_dist=dmpdist";
sqlConnection1.Open();
sqlDataAdapter1.Fill(dataSet1);
DataGrid1.DataSource=dataSet1;
DataGrid1.DataBind();
Cache["DealerListData"]=dataSet1;
sqlConnection1.Close();
}
View 2 Replies
View Related
Aug 21, 2014
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
View 5 Replies
View Related
Jan 31, 2008
I have a datetime field in a sql db named "arrdate". For what I am doing I only need to extract the date formatted as mm/dd/yyyy. Can someone give me an example of the proper syntax?
Something like seems like it should work:
SELECT id, DATEPART(mm/dd/yyyy, arrdate)
FROM guest
but of course it doesn't.....
View 3 Replies
View Related
May 7, 2001
Why would this return a year of 1905: If @tempdate was a varchar
it would return just 2001, but by it being a dateime it returns
the year 1905.
declare @tempdate datetime
select @tempdate = (select datepart(yy,getdate()))
select @tempdate
View 4 Replies
View Related
Mar 18, 2006
Datepart function is different from general ms using in MSSQL
I need something like this
DatePart ( interval, date, [firstdayofweek], [firstweekofyear])
I try to get which week of year.. but first day of week for some country is not sunday..
How can I determine that the first day of week is monday?
View 1 Replies
View Related
Apr 21, 2008
CREATE PROCEDURE [dbo].[sp_TRAK_PROG_TOTALS]
@Frequency varchar (1),
@Rpt_Yr smallint,
@Prog_Yr varchar (2)
AS
SET NOCOUNT ON
DECLARE
@PROG1VARCHAR (20),
@PROG2VARCHAR (20);
--@PROG_YR VARCHAR (2);
SET @PROG1 = 'TRAK08'
SET @PROG2 = 'TRAK208'
SET @PROG_YR = Select DatePart(YY, GetDate()) as Current_Year
SELECT @Rpt_Yr = case @Frequency
WHEN 'M' then (select dateadd(mm,datediff(mm,0,getdate())-1,0))
WHEN 'O' then convert(varchar,@Start_Date,101) + ' 00:00:00'
ELSE convert(varchar,getdate()-1,101)
end
--------------------------------------------------------------------------------------------------
I have done most ot the script for the rest of my pgoram but I am having problems with the date requirements.
I was told to parse the year so that the user can enter the 2 digit date.
When it is 'O' (other) was told build a string to parse the year using datepart so that the user can enter the program year (2 digit format). (@Rpt_Yr)
When it is 'M' (monthly) then I am to goto to table tk_prog get the active program
I have no clue how to correct the above.
View 2 Replies
View Related
May 31, 2008
Hi All,
i'm trying to format SQL so that I retrive the day of the week and the hr in the same column.
SELECT Datepart([hour], Time) as Hour, SUM(Total) as Sales, count(TransactionNumber) as Customers, SUM(Total)/count(TransactionNumber) as 'Ave Sale'
FROM [transaction]
WHERE time between '05/30/2008' and '05/31/08'
GROUP BY datepart([hour],Time)
Output desired is
05/30/08 09
05/30/08 10
05/30/08 11
...
View 1 Replies
View Related
Mar 9, 2006
Hi , I am converting a datetime field to a string. The column is called DateScanDate.
This is my query;
SELECT CAST(DATEPART(Year, DateScanDate) AS VARCHAR(4)) + CAST(DATEPART(Month, DateScanDate) AS VARCHAR(2))+
CAST(DATEPART(Day, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Hour, DateScanDate) AS VARCHAR(2))+ CAST(DATEPART(Minute, DateScanDate) AS VARCHAR(2))FROM HAAneurysmScan
I would like the month of March to be '03' instead of '3' and the 9th day of the month to be '09' instead of '9'
How can I do this?
regards
ICW
View 9 Replies
View Related
May 3, 2007
I would like to take the following code and display the data / count by month. I want to see how many people are logging in by month.. I tried using the datepart but I keep getting an aggrefate comannd error can anyone help modify this query
SELECT DISTINCT Count(login.login_time) AS CountOflogin_time
FROM login
WHERE login.login_time>=#10/1/2005#;
Thanks,
View 4 Replies
View Related
Sep 28, 2007
Using MS Reporting Services 2005
I have this expression in my field in my table and in my group by expression field
=Monthname(Datepart("m", Fields!CreatedDate.Value))
ok this gives me the month eg July etc
=Datepart("yyyy", Fields!CreatedDate.Value)
ok this gives me the year eg 2006 etc
if I try to add the two together I get the error (not in correct format) obviously as one is numeric and one alpha
=(Monthname(Datepart("m", Fields!CreatedDate.Value) & " " & (Datepart("yyyy", Fields!CreatedDate.Value))))
or I can do this:
=(Datepart("m", Fields!CreatedDate.Value) + (Datepart("yyyy", Fields!CreatedDate.Value)*100))
which gives me 200607
what do I need to do to have this show as
July 2006 etc
thanks
Dianne
View 4 Replies
View Related
May 30, 2008
How can I concat. these two into one column?
datepart(mm,DateCol), datepart(dd,DateCol) as MyDate
thanks.
View 7 Replies
View Related
Oct 9, 2007
Hi everyone, Im currently using SSRS 2000. I have a report that pulls data since 2001. I used Datepart('m", Fields!Shipped_Date.Value) to break it down into months. It works except that it doesnt separate the year. It'll group all of january together but 2001,2002,2003, etc, together. Then when I used Y to break it down by year, it does break it by year but keeps all the months together.....well what I want is to be able to have it jan 2001, feb 2001, jan 2002, separately group. Hope I didnt confuse anyone. All pointers welcomed.
Thanks,
Abner
View 1 Replies
View Related
Aug 22, 2006
I am wanting to set any given @Date parameter to the most current recorded Monday in a table (tblMarketPricing). Would this work:
@Date = Max(Datepart(dw,MktDate) IN 2
I am unsure if you can use Max with the Datepart function.
***If this is not allowed, can anyone suggest anything different that I might try?
View 3 Replies
View Related
Jan 31, 2008
ok, following up on my previous post that I marked as answered a little premature. The query below works fine in sql studio:
SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate
FROM guest
but when I try to use it in a c# code behind file:
comm = new SqlCommand("SELECT id, CONVERT(NVARCHAR(10), arrdate,101) as formatedDate FROM guest WHERE id = @id", conn);
it bombs??
View 5 Replies
View Related
Feb 22, 2005
I'm trying to get just the day part of the date - 2/22/2005 (getdate()) but instead of returning '22', it's returning '2'. Can someone please tell me what I'm doing wrong?
Thanks!
Lynnette
Here's the code
declare @thisDay varchar
set @thisDay = Convert(varchar, Datepart(day, getdate()))
View 3 Replies
View Related
Apr 9, 2006
Hello dears;
I wanted to use DatePart function in an SQL statement using objectDataSource to extract the year only from a Date field, then populating only the years in Dropdown list, but it didn't worked. Is is possible to to use that function in an SQL statment.?
Thanks alot
VWD 2005
View 2 Replies
View Related
Aug 24, 2001
I am updating a demo system and need to bring all the dates with 1998 to 2000 and 1999 to 2001.
Im trying something like this but cant get it work.
UPDATE mytable SET datepart(yyyy, mydate) = '2000' WHERE mydate BETWEEN '01/01/1998' AND '12/31/1998'
How can I get this to work, or am I totally off?
Thanks,Adrian
View 4 Replies
View Related
Jul 30, 2001
I am inserting two fields like this (TimeStamp):
7/30/01 1:26:01 PM
The first one needs to be the Date part, the second one needs to be the Time part. Anyone know who to do this? I saw DATEPART in SQL BOL but it didn't really show me.
TIA,
Bruce
View 1 Replies
View Related
Feb 24, 2000
Hi,
I am having trouble trying to get a "week number" from a date field to store into a numeric table.
I have inserted the following into my DTS ActiveX code;
DTSDestination(15)=DatePart( wk , DTSSource(1) )
When I run the code the following error is shown;
ErrorDescription: Invalid procedure call or argument: ''DatePart'
Q - Does DatePart work in DTS ActiveX ?
Q - Is my syntax correct ?
david
View 1 Replies
View Related
Dec 16, 2003
I am converting a datetime value to char using datepart(mm,datefield).
I would like the month to be 2 digits - 01,02,03...11,12 - but it only returns 1,2,3...11,12. What can I add to do this? thanks in advance.
View 1 Replies
View Related
Aug 17, 2004
Will someone please tell me how to pull the time out of a smalldatetime field.
The code i am trying to use is as follows:
Select datepart(hh:mm, TimeField1)
from table1;
This gives me an error. I have also tried datepart('hh:mm'... datepart("hh:mm"... and other variations but i cant get anything to work. Thanks in advance for any help!!
View 2 Replies
View Related
Nov 3, 2004
Can anyone explain me why this query
SELECT DATEPART(dw, CONVERT(DATETIME, '2001-01-25 01:00:00', 120))
returns 5 on one SQLServer, and 4 on another one. It cannot be a regional settings problem because 25 could never be a month. Any way, is there a deterministic way to create a DATE regardless of the server's regional settings?
I mean, I have the year, month and day... how do I tell the server "This is the year; This is the month; This is the day... convert this into a DATE, and the tell me which day of the week is"??
TIA,
View 4 Replies
View Related
May 12, 2004
Functionally, is there any difference between DATEPART for m, d, and yyyy, verses simply using MONTH, DAY, and YEAR functions respectively?
For all intents and purposes, they seem identical, so is there any performance considerations using one verses another?
View 1 Replies
View Related
May 27, 2008
Hi, everyone! Could anyone help me with understanding of the datepart() function. I want to retrive data from a database that was added in this week, starting from Sunday. Suppose today is Tuesday, so I want to get all the records that were added on Sunday, Monday, and Tuesday. Can I use
...
where datepart(wk, Date_Reported)=datepart(wk, getdate()) ?
Thank you.
View 1 Replies
View Related
Jun 11, 2008
INSERT INTO [IMPAPT].[dbo].[YearMaster]
([yearID]
,[year]
,[IsActive]
,[creationDate])
VALUES
(1
,datepart(yy,getdate()) --------line need to be discuused
,1
,getdate())
the output result is
yearID Year IsActive creationDate
17/2/1905 12:00:00 AMTrue 6/11/2008 1:21:17 PM
But when i write query
select datepart(yy,getdate() As Year
it throws result : 2008
Why this is not happenin in the insert query
View 6 Replies
View Related
May 11, 2006
I have a function that uses the following statement in it
SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref
WHERE (src_terrier.datadate = @dt_src_date) AND
(src_terrier.Areacode = @chr_div) AND
(src_centre_list.Portfolio_no = @vch_portfolio_no) AND
(src_centre_list.propcat = @vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)
The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows
src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.
How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.
Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.
Anybody confused by that , cause I am!
Regards
View 6 Replies
View Related
May 31, 2007
Could someone tell me if they see something off about this expression? I'm using it in an expression. It's giving me a syntax error.
=IIf(Fields!NTFrequency.Value = "Quarterly",IIf((DatePart("m", Now)=1), 9, month(now)-3, IIf((DatePart("m", Now)=1), 12,month(now)-1)))
Thanks, Iris
View 5 Replies
View Related
Apr 1, 2008
Hi - Can anyone tell me what the first day of the week (i.e. equal to 1) for the DATEPART("dw",<date>) expression in SSIS. Or can I specify the first day of the week somehow?
It looks like Monday=1 which seems a little odd as the equivilent T-SQL DATEPART has Sunday=1.
Thanks!
View 5 Replies
View Related
Mar 30, 2008
Hi:
I am puzzled by the following:
If I execute "SELECT DATEPART(weekday, '20080330') as day_of_week", I get a result of 1 which makes sense since Sunday is usually considered the 1st day of the week.
However, if I execute "SELECT DATENAME(weekday, DATEPART(weekday, '20080330')" as day_of_week, I get a result of Tuesday!
Similarly, if I execute "SELECT DATENAME(weekday, 1) as day_of_week", I also get a result of Tuesday.
Can anyone explain what is happening here?
View 5 Replies
View Related
