Many Thanks In Advance! - Simple Date Function - Please Help!!!
Aug 2, 2004
Hi All,
Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)
Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.
Many thanks in advance!!
Brad
----------------------------------------------
declare @today DateTime
Select @today = GetDate()
print @today
View 2 Replies
ADVERTISEMENT
Apr 13, 2007
Below is an example of a pivot table from the help in SQL2005.
My question: Do you have to manually define the columns ([164], [198], etc.) for the pivot?
I would like to use this for a daily report where the columns would be the dates?
Thanks.
GO
SELECT VendorID, [164] AS Emp1, [198] AS Emp2, [223] AS Emp3, [231] AS Emp4, [233] AS Emp5
FROM
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [164], [198], [223], [231], [233] )
) AS pvt
ORDER BY VendorID
View 3 Replies
View Related
Jul 22, 2015
We need to use SQL Server Authentication for some reason and would like to enforce Password Policy with 90-day expiration period. I found "Change password" dialogue appears when I first logged in with the new user, but don't know
(1)what happens when the user failed to change the password before it's expired or
(2)how a user can change his password in advance of the expiration date with no particular server-level permission.
View 10 Replies
View Related
Apr 20, 2006
Hello
I'm trying to do a simple =Sum([unitPrice] * [quantity]) but it won't doing anything more than stay on zero! I've put it in the properties but has anyone got any ideas to get the total of this???
View 2 Replies
View Related
Jul 29, 2015
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
View 9 Replies
View Related
Mar 9, 2005
I have a cube with 8 dimensions: time, organization, customer, item, region, market, salesperson, shipped_from
3 Measures: invoice_qty, invoice_price, sales_total
I want to get the average invoice_price for an item ...across any dimension or groups of dimensions... what is the proper syntax for creating a calculated member using the dims and measures above
Thanks in advance
Steve
View 1 Replies
View Related
Jul 19, 2006
Hello all. I have an Access table with EmpName, JobTask, and Hours. Multiple lines can contain the same Employee with the same JobTask. What I need to do is to list the Employee, JobTasks (grouped), and summed hours for each JobTask.
DB Ex:
John Doe Welding 8
John Doe Cleaning 4
Bubba Smith Fork Lift 3
John Doe Welding 7
Steve Johnson Welding 5
Bubba Smith Fork Lift 6
Page output:
John Doe
Welding 15 hrs.
Cleaning 4 hrs.
Bubba Smith
Fork Lift 9 hrs.
Steve Johnson
Welding 5 hrs.
This is how I figured it should be, but it's not working for me:
SELECT EmpName, SUM (Hours), JobTask
FROM tblEmpTime
Group By JobTask
Any suggestions would be greatly appreciated.
Thanks,
Parallon
View 4 Replies
View Related
May 18, 2007
Hey all,
Very simple question from a real n00b: In many of my stored procedures I am repeating a CASE statement where I'm replacing null values with zero, like this:
(CASE WHEN @Field IS NULL THEN 0 ELSE @Field END)
I have many SPs where I have that code repeated literally dozens of times. Is there any performance benefit to creating a function to perform this task and using that instead of the repeated CASE statements? Or am I trying to be too clever?
Thanks!
Ron Moses
ConEst Software Systems
View 5 Replies
View Related
Jun 26, 2007
Hi, I need to write a simple function to format the contents of the fields in my table. I bascially want to say that if the value in a field is below '0' then format the text in colour red and if the value in the field is 0 or above then format it in black. Obviously this can be done by writing an expression in each field but i would prefer to write a function - any ideas.....
View 1 Replies
View Related
Oct 18, 2005
Hi All!This is the first time i am tryin to write a sql server 2000 function.I need to clean up some of my stored procedures..Can any one please give me skeleton for a userdefined function orcorrect my function. I get the following error.Select statements included within a function cannot return data to aclient.This returns a bit and it does a query to the database to decide thevalue of the bitCREATE FUNCTION GoodAd(@adid int,@currentdate datetime)RETURNS bitASBEGINdeclare @Return bitselect @return = 1if exists (select null from adqas where adid=@adid)select @return 0return @returnend
View 2 Replies
View Related
Apr 25, 2015
I have a really basic question. The following SQL query works for me:
Select EnterUserID, Enterdate
from tblCsEventReminders
where EnterDate >= Convert(datetime, '2015-04-01')
I am essentially trying to write a query to count the number of user logins after a certain date, so I need to count 'EnterUserID' but I am having problems getting the count() function to work.
View 3 Replies
View Related
Mar 18, 2014
I have the following
Column Name : [Converted Date]
Data Type : varchar(50)
When I try and do month around the [Converted Date] I get the following error message
“Msg 241, Level 16, State 1, Line 2
Conversion failed when converting date and/or time from character string.”
My Query is
SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]
View 7 Replies
View Related
Mar 10, 2008
I have a reference table that currently has no web front-end. It's a small table(<10 rows) that's not going to change very often (maybe once every few months).
We manually update rows on the table via the GUI table interface in Enterprise Mgr., not in T-SQL.
What I'd like to do is have SQL Server automatically update the "Last_Modified" column with the current timestamp. I can do it on an Insert using the GetDate() function, but if I update a row, this doesn't work.
Is there a function I can use that can auto-populate for both insert and updates?
View 4 Replies
View Related
Jul 9, 2013
I want my query to list all SSNS that have more than one record in the table. I have this query:
Code:
SELECT SSN, name4, count(*) from [1099_PER]
group by SSN, name4
having count(SSN) > 1
It does retrieve the right SSNS and tells me how many times the SSN occurs in the table. However, I want my query results to display their full records.
For example
SSN NAME4 COUNT
123445555 WALTER - 4
I want the query to show me all four records for this SSN. I thought removing the count field would do this, but it still gives me only one instance of each SSN.
View 6 Replies
View Related
Dec 15, 2005
Hi,
We are seeing memory issues with a simple C# based TVF that splits an input string into a table based a delimiter. Entries such as the following show up in the SQL log:
AppDomain 8 (DBName.dbo[runtime].7) is marked for unload due to memory pressure.
AppDomain 8 (DBName.dbo[runtime].7) unloaded.
These entries only show up after the TVF has been called many times (~ half million times or more).
We encountered the same memory issues with June CTP, but they appeared to be fixed in Sepetmber CTP. Somehow the issues come back for us in the SQL Server 2005 RTM version. With June CTP after these errors show up many many times, the SQL server had to be re-started.
I'd appreciate any comments/ideas regarding these error messages in the SQL log?
We are using the RTM version of SQL2005: Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86)
Oct 14 2005 00:33:37
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Thanks,
Wenbin Zhang
View 10 Replies
View Related
Aug 17, 2007
I have created an assembly with permission set safe, and a function inside the assembly.
The function reads data from the same SQL Server as it is running inside. For connection I use the "Context connection = true", and the function has the SystemDataAccessKind attribute set to Read.
However when I execute my CLR function I get an error saying something like:
"The request for permission of type System.Data.SqlClient.SqlClientPermission...... failed"
I do, as the login user, have been granted the necessary rights, so I don't believe this is the answer to the error.
And my .dll is also signed.
Has this something to do with writing something a config file?
I have had simular problems with reporting services but fixed them by entering a node in the rspolicy.config file. If this is the case here - which .config file should i modify...machine.config?
View 6 Replies
View Related
Aug 4, 2006
Here is one such function:CREATE FUNCTION my_max_market_date () RETURNS datetimeBEGINDECLARE @mmmd AS datetime;SELECT max(h_market_date) INTO @mmmd FROM holdings_tmp;RETURN @mmmd;ENDOne change I had to make, relative to what I had working in MySQL, wasto insert 'AS' between my variable and its type. Without 'AS', MS SQLinsisted in telling me that datetime is not valid for a cursor; and Iam not using a cursor here. The purpose of this function is tosimplify a number of SQL statements that depend on obtaining the mostrecent datetime value in column h_market_date in the holdings_tmptable.The present problem is that MS SQL doesn't seem to want to allow me toplace that value in my variable '@mmmd'. I could do this easily inMySQL. Why is MS SQL giving me grief over something that should be sosimple. I have not yet found anything in the documentation for SELECTthat could explain what's wrong here. :-(Any ideas?ThanksTed
View 6 Replies
View Related
Feb 20, 2007
Hi,how do I do a simple formula, which will search a field for specialcharacters and return a value.If it finds "%" - it returns 1elseIf it finds "?" it returns 2endIf this is the incorrect newsgroups, please direct me to the correct oneregards Jorgen
View 2 Replies
View Related
Mar 28, 2008
hello I have this code Select Waste.WasteName AS [Waste Name], InvoiceDetail.Volume, Branch.BranchLocation AS [Branch Location] From InvoiceHeader INNER JOIN InvoiceDetail ON InvoiceHeader.InvoiceNo = InvoiceDetail.InvoiceNo INNER JOIN Waste ON InvoiceDetail.WasteID = Waste.WasteID INNER JOIN Branch ON InvoiceDetail.BranchID = Branch.BranchID Where WasteName = 'Sludge' AND InvoiceDate >= '" + TextBox1.Text + "' AND InvoiceDate <= '" + TextBox2.Text + "'; The problem is everytime I will search for example InvoiceDate 03/27/2008, I need to add one day for example --> 03/28/2008 for me to be able to get that Invoicedate 03/27/2008.. What's do you think is the problem with my code? Thanks!
View 9 Replies
View Related
Apr 13, 2004
hi, when i use the getdate() function, i will get '4/14/2004 AM 11:25:56'. how can i edit the getdate() function to get just the date only '4/14/2004'.
thanks in advance
View 3 Replies
View Related
Feb 24, 2005
I can get the date to list all of the dates in the recordset, but when I use request.querystring("date") to specify all the recordsets with the date "2/26/2005" for example, the recordset comes up empty. Here is my sql statement:
SELECT eventdate=convert(varchar, eventdate, 101), eventtext, eventtime, location, eventtitle, eventID, picture
FROM dbo.calendar
WHERE eventdate like 'thee' and status='live'
ORDER BY eventdate ASC
this will return everything when the browser url shows this: http://www.kapsi-western.org/calendar.asp?date= However, if we put a date in there, and made it like this http://www.kapsi-western.org/calendar.asp?date=2/26/2005, then nothing comes up even though there is event with that date.
View 1 Replies
View Related
Aug 3, 2006
I'm trying to find the first Monday for any given year. I've found something
that works, but I typically find the most convoluted way to do a simple task.
Is there something simpler/cleaner than this?
DECLARE @intYear INTEGER
DECLARE @dtmFirstMon DATETIME
DECLARE @dtmWorkDate DATETIME
SET @intYear=YEAR(GETDATE())+2
SET @dtmWorkDate=CAST('01/01/' +CAST(@intYear AS CHAR(4)) AS DATETIME)
SET @dtmFirstMon=(SELECT CASE
WHEN DATEPART(dw, @dtmWorkDate)=2 THEN @dtmWorkDate
WHEN DATEPART(dw, DATEADD(D,1,@dtmWorkDate))=2 THEN DATEADD(D,1,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,2,@dtmWorkDate))=2 THEN DATEADD(D,2,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,3,@dtmWorkDate))=2 THEN DATEADD(D,3,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,4,@dtmWorkDate))=2 THEN DATEADD(D,4,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,5,@dtmWorkDate))=2 THEN DATEADD(D,5,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,6,@dtmWorkDate))=2 THEN DATEADD(D,6,@dtmWorkDate)
END)
PRINT(@DTMFIRSTMON)
View 1 Replies
View Related
Feb 20, 2008
I want today's date (GetDate()), but I want the hour, minutes, et cetera set to 0 as they play havoc with my date comparisons.
How do I do it?
Thanks.
View 5 Replies
View Related
Oct 4, 2005
This seems really simple so I'm suprised I'm having so much trouble. Perhaps I'm just overthinking the whole issue. I'm trying to write a query that will extract a list of Users from a table that have logged in within the last 90 days. Sounds easy huh? I'm still stumped!Here's the basics of the attempted query:SELECT UserNameFROM UsersWHERE (LastLoggedInDate<= DATEPART(dd, GETDATE() - 90))Thanks!
View 1 Replies
View Related
Jun 10, 2004
I simply want to format a datetime to be printed out as 'mm/dd/yyyy', instead of 'January xx, xxxx tttt'. For example:
declare @thedate as smalldatetime
set @thedate = (SELECT SampDate
FROM table where tableID = 1)
print @thedate
This has got to be ridiculously obvious. Right??
View 2 Replies
View Related
Apr 6, 2004
Hello Friends ,
As Iam a very beginner to this sqlserver arena , Iam getting more and more doubts. Can anyone of you help to display a date as string type which is of format, "date/month/year hour:month:second:millisecond".
Please get rid of the huge command which Iam using now,
select rtrim(cast(datename(day ,getdate()) as char )) + '/'+ rtrim(cast(datename(month ,getdate()) as char )) + '/' + rtrim(cast(datename(year ,getdate()) as char )) + ' ' + rtrim(cast(datename(hour ,getdate()) as char )) + ':' + rtrim(cast(datename(minute ,getdate()) as char )) + ':' + rtrim(cast(datename(second ,getdate()) as char )) + ':'+rtrim(cast(datename(millisecond ,getdate()) as char )) as date
View 13 Replies
View Related
Apr 30, 2007
Hello
I was wondering if someone could tell me the syntax for a simple date range query. Basically:
select * from TABLE where start date= and end date =
Thanks
View 3 Replies
View Related
Apr 13, 2008
Well you would think it was pretty simple. If I create a Database on Server a last week, take a backup of that database and put it on server b today, the CRDate in sysdatabases still shows the date of last week instead of today. I need to find the actual date for all 150+ servers from sql 7 - sql 2005 in our domain. Any ideas? I had heard that maybe SQL DMO had an object that might be helpfull but I have not been able to look it up anywhere or see how to use it using sp_oa procs.
Any help would be greatly appreciated.
View 3 Replies
View Related
Aug 25, 2007
I want to create a procedure where nextvisitdate is a cloumn for date
so when I passed variable @nextvisitdate then which data type I have touse with it.
CREATE PROCEDURE sp_salesman_nextvisitdate
@salesman varchar (50),
@nextvisitdate date (50)
As
Select * from Entry Form
WHERE
@salesman = salesman
AND
@nextvisitdate = nextvisitdate
GO
You Have to Loss Many Times to Win Single Time
View 3 Replies
View Related
Jan 19, 2007
Hi, I have just started using SQL server 2005 reporting. But I am stuck on my select part of the report. I want to bring back all results from a table for todays date (whatever that would be). I have tried:
Where ColumnA = Today()
and a few other Today variations.
Can anybody please help?
Also is there a good site / tutorial which helps with SQL syntax, statements, etc.
Thanks
Dan
View 4 Replies
View Related
Mar 21, 2005
I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query
update
timecard
set
TcdDate = #3/18/05#
TcdDate is defined as a date/time type
It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?
View 4 Replies
View Related
May 7, 2004
Hi,
The subject say it all :)
I have a field which stores date which the datetime datatype... The problem is that I am also getting time information in this stored field.
I just want to extract the date part in dd-Mmm-yy format (e.g. 07-May-04). I tried convert (char (8), MyDateField, 112) but it gives me date in yyyymmdd format. How do I get the results in dd-Mmm-yy format instead?
Thanks.
View 7 Replies
View Related
Jul 20, 2005
DECLARE @varDate datetimeDECLARE @varMonthDate datetimeDECLARE @varYear datetimeDECLARE @varFOM varchar(10)DECLARE @NumDaysInMonth as intselect @varDate = '2/1/2004'select @varMonthDate = MONTH(@varDate)select @varYear = YEAR(@varDate)select @varFOM = CAST(CAST(@varMonthDate as int) as varchar) + '/1/' +CAST(CAST(@varYear as int) as varchar)select @NumDaysInMonth = DATEDIFF (day,@varFOM,DATEADD(mm,1,@varFOM))select @NumDaysInMonth as DaysInMonth
View 1 Replies
View Related
