WeekDay Defaults...

Feb 27, 2001

Hi!
I´m experiencing problems with datepart(weekday,...) function. In particular with week 54 of 2000... (by the way, don´t make a bet about that, because Jan 1st. was saturday (last day of week) and year 2000 was a leap year!!!, so Dec 31 became the one and only case of 54 weeks... in our calendar).
Is there a way I can fix sunday as first day of week, instead using SET DATEFIRST in every procedure? (because "somebody" puts MONDAY as a default in spanish)
Thanx in advance,
César.

View 1 Replies


ADVERTISEMENT

Group By WEEKDAY And ORDER By WEEKDAY

Jan 31, 2014

I try to make the following:

select DATENAME(WEEKDAY,[date]) as DAY, sum(earnings) as Earnings
from myTable
where DATENAME(WEEKDAY,[date]
in ('monday','tuesday','wednesday','thursday','friday')
group by DATENAME(WEEKDAY,[date])
order by DAY

this query works very well except the last line. the last line causes SQL-Server to order the days in an alphabetical order.What do I have to do that SQL-Server orders the days according to their natural appearance (monday, tuesday, wednesday, thurs......)

View 2 Replies View Related

Data Import Excel XML Defaults Which File Governs Excel Defaults?

Jul 21, 2015

I'm importing a multi tab spreadsheet using Import wizard, which I understand to use the same internals as SSIS. The total number of columns in the spread sheet will be over 500. The import wizard defaults everything to varchar 255. I understand there is an XML file I can manipulate to change this and they are located

C:Program FilesMicrosoft SQL Server100DTSMappingFiles

Assuming one of these will control Excel defaults, which one is it? None of the names lend themselves to the Excel as a source. SqlClientToMSSql10?

View 6 Replies View Related

Weekday Report

May 29, 2008

Hello Guys,

I'm going crazy tring to figure out how to generate a report that execute a Store procedure and then give me a total Per WeekDay and group it by Part Number.

For example my data should look like this

(PartNumber-Monday-Tuesday-Wednesday-Thursday-Friday-Saturday-Sunday-)

The report will give me a list of all available part and the sold sold per part per day.



Thanks in Advance.

View 8 Replies View Related

Weekday Names

Jan 4, 2007

Sql server 2000 -sql query analyzerselect datename(dw,'01-01-2006')returns sundayNow I do:set language danishselect datename(dw,'01-01-2006')returns sonday. But it should be søndag. Same goes forsaturday/lordag/lørdagTried substring'ing and ascii'ing/unicode'ing and it is AFAIK an o insteadof the special danish character ø.Is this a "feature" or am I doing something wrong?I "solved" it by doingselect case DATENAME(dw,'01-01-2006') WHEN 'lordag' THEN 'lørdag' WHEN'sondag' THEN 'søndag' ELSE DATENAME(dw,'01-01-2006') ENDbut it sure aint pretty./jim

View 3 Replies View Related

Calculating Weekday Of A Date

Nov 25, 2013

Aim – To work out what day of the week a Closedate Falls on

Date format is of Closedate is “Year-month-Day†(varchar(50)

E.g.
2013-11-25
2013-11-21
2013-11-19
2013-11-18

Desired results
2013-11-25Monday
2013-11-21Thursday
2013-11-19Tuesday
2013-11-18Monday

View 3 Replies View Related

Average Transactions By Weekday

Jan 25, 2008

Hi,

I have a transaction file containing a year's worth of transactions with a date/time field that tells me when the transaction took place. I'd like to write a view (or perhaps multiple views) that tells me the average number of transactions per weekday.

I've been messing around with DATEPART and I can calculate the *total* number of transactions that took place on a Monday, for example, but I can't figure how to do the *average* number. My problem seems to be calculating the number of Mondays there were in the year. Or overthinking it.

Any suggestions would be greatly appreciated.

Thanks,

Jennifer

View 5 Replies View Related

DatePart Returning Incorrect Value For Weekday (dw)

Feb 18, 2008

Hi All,In ASP.Net, I am passing a date as a string formatted as yyyymmdd (e.g. 20080219) via an SqlParameter as follows:aSqlParams(0) = New SqlParameter("@DepartDate", inpDepartDate.Value)In the Stored Procedure (SP) I am using DATEPART(dw, @DepartDate).If I execute the SP from within SQL Server and enter a date, formatted as above, as tha value for the Parameter @DepartDate then DatePart returns the correct value (3).If I use the SP via ASP.Net it returns a value which is 1 less (2). When I execute the SP manually the resultant EXEC statement uses "@DepartDate = N'20080219'". Can something similar be done in ASP.Net when setting the SqlParameter? Thank you,

View 4 Replies View Related

Convert Day Of Week Integer To Weekday Name?

Sep 10, 2007

In a table, I store an integer to represent a day of the week (1 = sunday ) and then in procedures, compare that to datepart(dw, getdate()) to do alternative tasks on certain days.

If I have the number 1, is there a built in function to convert that to sunday or should one just write a function to do just that?

View 3 Replies View Related

Display Date And Weekday In A Different Language

Oct 4, 2007

How can I set a different language (for example swedish) when using WeekdayName,

DatePart and FormatDateTime?

View 2 Replies View Related

Reporting Services :: SSRS Datetimepicker And Textboxes Values Different When Using Weekday Function?

May 7, 2015

In my SSRS report i have a date parameter and i want to set to it a default value with a complicated logic. I arrived at this strange behaviour:

Today is May 6th, wednesday. If i use the following expression:

DateAdd("d",Weekday(Today(),DayOfWeek.Sunday),Today())

for the default time picker I get May 9th. If I use exactly the same expression in a textbox in the same report

DateAdd("d",Weekday(Today(),DayOfWeek.Sunday),Today()).ToLongDateString()

I get May 10th! The only thing that changes is the toString. Why are the two values different? I tried with different expressions and the difference arises when i start using Weekday(Today(), somevalue).

The result of Weekday should be independent from regional settings as I am forcing Sunday to be the first day of the week, right?

View 2 Replies View Related

Defaults In The DB

Oct 7, 2004

How do i know whats the default values on which table and which column and whats the default value

It should be like this

Table columnconstraint_name value

empemp_idDF_emp_emp_no 1
empjoin_dateDF_emp_cur_date getdate()

How can i get these results on all the tables in my DB.

Thanks.

View 3 Replies View Related

BCP Defaults To 8.0 Instead Of 9.0

Sep 13, 2007

I have a 64-bit box with SQL 2000 and 2005 installed on it. At the command prompt, typing BCP -v gives me the 8.0 version, not 9.0. I really want the 9.0 version as the default. Obviously typing
"C:Program FilesMicrosoft SQL Server90 oolsinncp" gives me the 9.0 version.

My PATH variable looks like this:
Path=C:WINDOWSsystem32;C:WINDOWS;C:WINDOWSSystem32Wbem;C:Program FilesAT
I TechnologiesATI Control Panel;C:Program FilesIntelDMIX;C:Program Files (
x86)Microsoft SQL Server80ToolsBINN;C:Program Files (x86)Microsoft SQL Ser
ver80ToolsBinn;C:Program FilesMicrosoft SQL Server90DTSBinn;C:Program
FilesMicrosoft SQL Server90Toolsinn;C:Program Files (x86)Microsoft SQL
Server90Toolsinn;C:Program Files (x86)Microsoft SQL Server90DTSBinn;C
:Program Files (x86)Microsoft SQL Server90ToolsBinnVSShellCommon7IDE;C:
Program Files (x86)Microsoft Visual Studio 8Common7IDEPrivateAssemblies






Do I dare change the order so it looks in the 9.0 directories first? Or is this by design?




thx

View 1 Replies View Related

Dropping Defaults

Jul 6, 2001

I have run the following script to create a default on a table:

alter table TableXYY
add default 0 for ColumnX

Now, how do I delete this default (without re-creating the table)??

Thanks

View 1 Replies View Related

DateTime Defaults

Jan 17, 2007

I'm having some problems when trying to import an external table into my SQL Server 7.0 DB. When date fields are being created the datatype is being set to smalldatetime which is causing errors because some of the data in the source table is using dates proir to 1900.

Is there a way I can set the datatype to be set as datetime instead of smalldatetime?

View 4 Replies View Related

Overriding Defaults

Sep 26, 2007

I have a stored procedure that has @BeginDate and @EndDate as parameters. I created a report with a default for both. They run just fine. After I deployed, I created Linked Reports and wanted to override the defaults. In the defaults, I tried to put in GetDate() for @BeginDate and GetDate()+10 for the @EndDate so this can be passed in the where statement of the stored procedure. I get 'Syntax error converting datetime from character string.'

What I assume is that if I override the default, the stored procedure will process what I put in the @BeginDate and @EndDate parameters.

The where statement looks like:

and (m.BeginDate >= @BeginDate) and (m.EndDate <= @EndDate)

I'm using Reporting Services 2005 and SQL Server 2005.

Thanks, Iris

View 1 Replies View Related

Finding Paramters With Defaults

Jan 12, 2000

Does anyone have a way of finding all parameters with default values, for any stored procedure in a database?

aTdHvAaNnKcSe!

View 1 Replies View Related

OLE DB Transformations And Column DEFAULTS

Jul 2, 2007

I have a column which uses a DEFAULT as GETDATE in one of my tables. When I execute a DTS package to insert data into it, the column values are all the same, but if I use SSIS, the dates differ slightly (by a few ticks after several rows, but not a consistent amount of rows).

Is there an explanation for this difference, and how can I correct this problem?

View 2 Replies View Related

Select * Into Removes Defaults

Jul 20, 2005

I have a table, tbl1:create table tbl1 ([field1] [char] (16) NULL DEFAULT (' '),[field2] [char] (6) NULL DEFAULT (' ')).When I do a select * into tbl2 from tbl1, tbl2 does not have defaults.Is there any settings I have to keep on when I do a select * into?Any help will be appreciated.

View 2 Replies View Related

Parameters, Defaults, And Datatypes...oh MY!

Nov 28, 2007



Thanks in advance for fielding my question!

I have parameters (type=datetime) on a report whose values are populated from a query. This query just pulls a list of all the dates (all Sunday dates) in a table. I have the parameter set up to have the date as the value and then a string representation of the value as the label (ie 2007-11-25) to make it easier for the user.

I want to set up a default for this parameter that essentially takes today's date and calculates the previous Sunday's date so the parameter drop down defaults to last Sunday's date.

My expression works fine just to display it on the report in a text box. It calculates and displays last Sunday's date perfectly. BUT, it doesn't work when I use it in the expression for the parameter default - Im' assuming because the latter cares about data type?

here's my statement:

=Iif(Now.DayofWeek=1,dateadd("d",-1,Now),(Iif(Now.DayofWeek=2,dateadd("d",-2,Now),(Iif(Now.DayofWeek=3,dateadd("d",-3,Now),(Iif(Now.DayofWeek=4,dateadd("d",-4,Now),(Iif(Now.DayofWeek=5,dateadd("d",-5,Now),(Iif(Now.DayofWeek=6,dateadd("d",-6,Now),(Iif(Now.DayofWeek=7,Now,0)))))))))))))

In short all those if statements calculate the day of last Sunday's date based on Now()...so if it's Monday, subtract 1, if it's Tuesday, subtract 2...etc.... BUT this returns the time also - argh! Formats!

How can I format this so it will equate to my oracle date populating in my parameter list? Do I need to match the output of the above statement to the LABEL (ie, string) or the actual VALUE (ie, date). I've tried both. I've hacked at this thing for an hour and I'm sure it's so obvious!



Thanks!
J

View 8 Replies View Related

SharePoint Set Server Defaults

Feb 22, 2007

Hi guys,

I have a problem when configuring SharePoint and RS on two boxes. (If install everything in one box is fine)

I have SQL SP2 + RS in one server and SharePoint on the other server. I've followed every step in the SQL 2005 Online Book to configure the RS and SharePoint including:

How to: Install the Windows SharePoint Services Object Model on a Report Server Computer. (Done)
How to: Configure Service Accounts (Reporting Services Configuration). (Done)

However, I got an error when click "Set server defaults" in SharePoint Central Admin:
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.

In ReadMe of SQL SP2, see below: http://download.microsoft.com/download/f/d/c/fdcb3a53-0cb1-4e67-a1b6-45b89b3c59cf/readme_rsaddin.htm#_rsshare_known_problems_7

---
6.4 Service account requirements for Reporting Services


Restrictions on using built-in accounts apply to some deployment topologies of Reporting Services that include a report server running in SharePoint integrated mode. The following combination of factors will result in service account requirements:

The report server is integrated with a SharePoint farm comprising more than one computer.


The report server and SharePoint Central Administration Web site run on separate computers.



In this scenario, if either the Report Server Web service or Windows service runs under a built-in account such as NetworkService, the Grant database access option in SharePoint Central Administration will not work correctly. Consequently, accessing any Reporting Services feature through a SharePoint site will result in the following error:

"An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode. --> Server was unable to process request. --> Client found response content type of 'text/html; charset=utf-8', but expected 'text.xml'."

To avoid this error, choose one of the following approaches:

On the computer that hosts the report server, continue to run the Report Server Web service as NetworkService and add the built-in account, such as NT_AUTHORITYNetworkService to the WSS_WPG Windows group.


Configure the service accounts to run under a domain user account as follows:



Start the Reporting Services Configuration tool and connect to the report server.


Click Windows Service Identity, click Windows Account, type a domain user account, and click Apply.


Click Web Service Identity, for Report Server, click New, type an application pool name, click Windows Account, type a domain user account, and click Apply.


Reset IIS.


Restart the Windows service.

---

I did try it out but still get the error when clicking "Set server defaults" link in SharePoint Central Admin.

Is there something wrong with the two boxes installation???

Hope the MS guys can help.

Paul

View 8 Replies View Related

SqlParameter Defaults To Nvarchar For Strings

Aug 2, 2006

We've got an app that is in final testing so we can't go around forcing SqlDbType.VarChar.  Is there any way to make SqlParameter default to varchar if you don't explicitly set a type for a string value?  It's bad for our db index usage because they're all varchar and nvarchar forces a convert_implicit messing up performance.

View 1 Replies View Related

Defaults And Constaint Values In SQL Server And DB2?

Mar 22, 2004

Hi,
I Just wanted know that where is the Default and Constraint values are stored in SQL server or DB2 sytem Tables?

View 4 Replies View Related

Use DEFAULT CONSTRAINTs Or BOUND DEFAULTs?

Oct 30, 2005

I am doing a little research on Google about this topic and I ran intothis thread:http://groups.google.com/group/micr...dc13d4ee6758966I read SQL Server MVP Louis Davidson's post saying:"Actually they are more likely to drop the concept of bound defaults.Constraints are the standard way to do this, and really should be the wayyou create defaults anyhow."Even I read in the Microsoft SQL Server Introduction (SQL 7 bookpage 244, however we're using SQL Server 2000):"Constraints define rules regarding the values allowed in columns and arethe standard mechanism for enforcing integrity, preferred over triggers,rules, and defaults. They are also used by the query optimizer to improveperformance in selectivity estimation, cost calculations, and queryrewriting."Why constraint defaults are better? The second sentence about constraintshaving better optimization, I am guessing they don't mean this aboutDefault Constraints, rather the other type of constraints?Because I don't see how a Default Constraint have anything to do withperformance? Isn't default only to do with new records being created?At work we are setting all tables' columns to have constraint defaultsof 0 or ' ' (space character) in order not to have any column with theNULL value. Therefore we have dozens of files containing statements like:alter table TABLE1 add constraint TABLE1_ID_DFDEFAULT(' ') FOR IDgoalter table TABLE1 add constraint TABLE1_QUANTITY_DFDEFAULT(0) FOR QUANTITYgoFirst I was thinking to create 3 SQL Defaults called:DefaultZeroDefaultSpaceDefaultDateand then bind these defaults to all the columns of all tables excludingprimary keys. After creating the tables I would enumerate throughall the columns and bind one of these three Defaults based on theirdatatype:number = DefaultZerotext type = DefaultSpacedate type = DefaultDateAnd then unbind the ones that we specifically need to specify otherdefault values.So my question is should I do this by using sp_binddefault or stickwith using Default Constraints inside a table/columns loop code?Thank you

View 10 Replies View Related

Changing SSIS Property Defaults

Sep 5, 2006

To make certain SSIS features work there are many properties that need to be set over and over for most containers in the package. For example with CheckpointRestart, you need to set (in most cases) all of the tasks' FailPackageOnFailure to True. If you miss one, the package may not restart properly and you might never know. There are other situations where as a development team we want certain properties to be usually set the same but differently from the SSIS default.

Is there a way to control the defaults that the SSIS IDE uses?

I remember back in classic VB that if you wanted to change the defaults of a bare form you could create a template form adjusted the way you like and put it in a templates folder. Then new forms added to a project would be based on the template form.

View 5 Replies View Related

Where Do User-defined Defaults Show Up?

Mar 22, 2007

I created the following Default


CREATE DEFAULT HelloWorld AS 'HelloWorld'

Now, tell me how can I get the information for this default? I can find it in sys.objects and it says its a DEFAULT CONSTRAINT, but sys.default_constraints does not show anything. Anyone?

Thanks

View 3 Replies View Related

Merge Replication: Does It Support UDTs && Defaults

Feb 27, 2003

Hi Everyone,

Can anyone help me with this little problem that im stuck with.
As part of the initial snapshot using merge replication, i am creating the initial schema on the subscriber database however it seems as though it does not like UDDTs or defaults and hence does not create them on the subscribers.

Its looking likely that I may have to create a script which executes on the subscriber which creates the initial schema then use merge replication to transfer data.

Does Merge Replication support UDTs/Defaults? If so how does it work?

Ive search many places though have come up with nothing.

Please help. = (

View 2 Replies View Related

RULES And Custom Defaults In Express Edition?

Apr 15, 2007

Does it allow you it create rules ? a right click does not present an option, as if you cannot. Going to try T-SQL to see if that works.



John

View 5 Replies View Related

Setting Defaults On A Multi-valued Parameter

Jan 27, 2006

I want to set defaults for my multi-valued report parameter MONTH so that when the report starts, it automatically selects all the months prior to the current month (effectively creates a YTD report). However, using RS2005, I can't seem to figure out how to do this. I can create an IIF expression in 12 different value entries in the report parameters that returns the month based on the system date, but the first time I pass blanks, null or anything except a valid parameter, it clears the entire parameter list when the report displays.

Does anyone have any suggestions for auto-populating multiple values in a parameter at runtime where one or more of the parameter values may be empty? Checking "Allow Null" or "Allow Blank" doesn't fix this problem.

I tried to pass all the values in a single value entry on the report parameters page, but can't find the syntax that will allow this. I'm not sure if it will let you do that anyway...

Trent

View 3 Replies View Related

Report Runs Automatically Now That Defaults Are Set On All Parameters

Dec 4, 2007

It seems that my report runs immediately now that I've set defaults on all parameters. Is there a way to allow defaults on all params but still require that the user click "view report" before it runs?

View 3 Replies View Related

Parameter Defaults Not Updated When Deploying From BIDS

Oct 10, 2007

Hi,

Ever since working with RS I had noticed that deploying reports felt inconsistent and didn't always seem to work, requiring sometimes for me to delete and re-deploy the reports before seeing changes applied. This has the knock on effect of generating new report ID's (GUID's) for the reports and any data in the ExecutionLog table for your reports becomes useless inhibiting effective usage reporting.

I never quite put my finger on exactly what the issue was until recently discovered that "by design" report paramerter defaults are not updated when deploying reports from BIDS. This is because MS envisaged that DBA's or Report Adminstrators would be responsible for changing parameter defaults and prompts once the reports are deployed.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=100960&SiteID=1

I disagree and having discussed this with the product team here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2181673&SiteID=1 it seems a sensible option would be to add a deployment option to the reporting services project definition similar to the OverwriteDataSources option.

I have posted a product feedback report on the Connect site https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299372

Please vote for this to be added to Katmai (RS 2008).

View 1 Replies View Related

T-SQL (SS2K8) :: Finding Stored Procedure Defaults For Parameters

Jun 2, 2014

I have various ways of getting the parameters of a stored procedure:

I have a procedure that has all defaults 4 are null and 2 are 0.

The following shows most of what I need but no defaults

SELECT PARAMETER_NAME ,
ORDINAL_POSITION ,
DATA_TYPE ,
CHARACTER_MAXIMUM_LENGTH ,
CHARACTER_OCTET_LENGTH ,
NUMERIC_PRECISION ,

[Code] ...

This one has two values:

PARAMETER_HASDEFAULT (always 0) and PARAMETER_DEFAULT (always 0)
sp_procedure_params_rowset proc procedure

Is there something else that would tell me if there is a default on a parameter and what the default is if there is one.

View 2 Replies View Related

Enterprise Manager: Defaults Lost In Import/export

Jul 20, 2005

Hi,When I copy tables in a database from one server to another usingenterprise manager, everything copies ok, except for field defaults.Has anyone seen this, and what is the solution?--http://www.dbForumz.com/ This article was posted by author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbForumz.com/General-Dis...pict174830.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=586973

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved