DB

Hi
You may want to check out
http://www.microsoft.com/sql/evalua...ibm/default.asp
My very little experience of DB2 was some time ago, but if you have a strong
relational database background then the regardless of the RDBMS a large
amount of what you know is directly transferable, the engine/tool specific
elements are usually available it is a matter of finding out how to do it in
the new environment.
If you are wanting to get a head start you may want to consider downloading
the trial edition.
John
"CG" wrote:

> Hi,
> I come from a strong SQL Server background.
> I am moving into a new role where the company use DB2.
> Is there much difference in terms of syntax between DB2 and SQL Server etc
?
> What is DB2 like to work with (environment, reliability etc)?
> Any feedback is much appreciated.
> Thanks.Also, check out Kevin Kline's book - "SQL in a Nutshell". It gives you
comparative syntaxes across several flavours of SQL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:679AB20E-B81B-41DC-88B3-B27FBBE70598@.microsoft.com...
Hi
You may want to check out
http://www.microsoft.com/sql/evalua...ibm/default.asp
My very little experience of DB2 was some time ago, but if you have a strong
relational database background then the regardless of the RDBMS a large
amount of what you know is directly transferable, the engine/tool specific
elements are usually available it is a matter of finding out how to do it in
the new environment.
If you are wanting to get a head start you may want to consider downloading
the trial edition.
John
"CG" wrote:

> Hi,
> I come from a strong SQL Server background.
> I am moving into a new role where the company use DB2.
> Is there much difference in terms of syntax between DB2 and SQL Server
> etc?
> What is DB2 like to work with (environment, reliability etc)?
> Any feedback is much appreciated.
> Thanks.sql

DayOfWeek Function

Would like to set a date parameter default in the report designer based on
the day of the week. So if it was Monday, then the default date would be set
to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
there a dayOfWeek function that be used in an expression that returns either
the numeric or the alpha of the week?
GlassHi,
You can easely use the expression <code>=WeekDay(Now())</code> for
retrieving the actual weekday. This combined with an IIF expression you can
create the behaviour you need, like
<code>
=IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
</code>
Hope this would help you
Jan Pieter Posthuma
"Glass" wrote:
> Would like to set a date parameter default in the report designer based on
> the day of the week. So if it was Monday, then the default date would be set
> to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> there a dayOfWeek function that be used in an expression that returns either
> the numeric or the alpha of the week?
> Glass|||Jan Pieter... It worked great. Have two ancillary question: what is the
difference between today and now? Is there a list of functions that are
valid in report server for use in expressions? Online books didn't seem to
help here.
Appreciate the help...
Glass
"Jan Pieter Posthuma" wrote:
> Hi,
> You can easely use the expression <code>=WeekDay(Now())</code> for
> retrieving the actual weekday. This combined with an IIF expression you can
> create the behaviour you need, like
> <code>
> =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> </code>
> Hope this would help you
> Jan Pieter Posthuma
>
> "Glass" wrote:
> > Would like to set a date parameter default in the report designer based on
> > the day of the week. So if it was Monday, then the default date would be set
> > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > there a dayOfWeek function that be used in an expression that returns either
> > the numeric or the alpha of the week?
> >
> > Glass|||Glass,
There is a little difference between Now() and Today(). Both return the same
date, but Now returns the actual time and Today will allways return 12AM
back. So for today:
=Now() returns 6/22/2005 9:55:04 AM
=Today() returns 6/22/2005 12:00:00 AM
I must say: I use Now mainly because of my history with VB.NET.
Jan Pieter Posthuma
"Glass" wrote:
> Jan Pieter... It worked great. Have two ancillary question: what is the
> difference between today and now? Is there a list of functions that are
> valid in report server for use in expressions? Online books didn't seem to
> help here.
> Appreciate the help...
> Glass
> "Jan Pieter Posthuma" wrote:
> > Hi,
> >
> > You can easely use the expression <code>=WeekDay(Now())</code> for
> > retrieving the actual weekday. This combined with an IIF expression you can
> > create the behaviour you need, like
> > <code>
> > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > </code>
> >
> > Hope this would help you
> >
> > Jan Pieter Posthuma
> >
> >
> >
> > "Glass" wrote:
> >
> > > Would like to set a date parameter default in the report designer based on
> > > the day of the week. So if it was Monday, then the default date would be set
> > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > there a dayOfWeek function that be used in an expression that returns either
> > > the numeric or the alpha of the week?
> > >
> > > Glass|||Thank you very much...
Glass
"Jan Pieter Posthuma" wrote:
> Glass,
> There is a little difference between Now() and Today(). Both return the same
> date, but Now returns the actual time and Today will allways return 12AM
> back. So for today:
> =Now() returns 6/22/2005 9:55:04 AM
> =Today() returns 6/22/2005 12:00:00 AM
> I must say: I use Now mainly because of my history with VB.NET.
> Jan Pieter Posthuma
> "Glass" wrote:
> > Jan Pieter... It worked great. Have two ancillary question: what is the
> > difference between today and now? Is there a list of functions that are
> > valid in report server for use in expressions? Online books didn't seem to
> > help here.
> >
> > Appreciate the help...
> >
> > Glass
> >
> > "Jan Pieter Posthuma" wrote:
> >
> > > Hi,
> > >
> > > You can easely use the expression <code>=WeekDay(Now())</code> for
> > > retrieving the actual weekday. This combined with an IIF expression you can
> > > create the behaviour you need, like
> > > <code>
> > > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > > </code>
> > >
> > > Hope this would help you
> > >
> > > Jan Pieter Posthuma
> > >
> > >
> > >
> > > "Glass" wrote:
> > >
> > > > Would like to set a date parameter default in the report designer based on
> > > > the day of the week. So if it was Monday, then the default date would be set
> > > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > > there a dayOfWeek function that be used in an expression that returns either
> > > > the numeric or the alpha of the week?
> > > >
> > > > Glass|||"Glass" skrev:
> Thank you very much...
> Glass
> "Jan Pieter Posthuma" wrote:
> > Glass,
> >
> > There is a little difference between Now() and Today(). Both return the same
> > date, but Now returns the actual time and Today will allways return 12AM
> > back. So for today:
> > =Now() returns 6/22/2005 9:55:04 AM
> > =Today() returns 6/22/2005 12:00:00 AM
> >
> > I must say: I use Now mainly because of my history with VB.NET.
> >
> > Jan Pieter Posthuma
> >
> > "Glass" wrote:
> >
> > > Jan Pieter... It worked great. Have two ancillary question: what is the
> > > difference between today and now? Is there a list of functions that are
> > > valid in report server for use in expressions? Online books didn't seem to
> > > help here.
> > >
> > > Appreciate the help...
> > >
> > > Glass
> > >
> > > "Jan Pieter Posthuma" wrote:
> > >
> > > > Hi,
> > > >
> > > > You can easely use the expression <code>=WeekDay(Now())</code> for
> > > > retrieving the actual weekday. This combined with an IIF expression you can
> > > > create the behaviour you need, like
> > > > <code>
> > > > =IIF(WeekDay(Now())=2, Now.AddDays(-3), Now.AddDays(-1))
> > > > </code>
> > > >
> > > > Hope this would help you
> > > >
> > > > Jan Pieter Posthuma
> > > >
> > > >
> > > >
> > > > "Glass" wrote:
> > > >
> > > > > Would like to set a date parameter default in the report designer based on
> > > > > the day of the week. So if it was Monday, then the default date would be set
> > > > > to =today.adddays(-3) otherwise it would default to =today.adddays(-1). Is
> > > > > there a dayOfWeek function that be used in an expression that returns either
> > > > > the numeric or the alpha of the week?
> > > > >
> > > > > Glass
anna jag behöver verkligen din hjälp nuu !!sql

Day of week expression for parameter

I have a sales quotes report and want the report to run for quotes
entered 2 working days ago. I have used a default date-time parameter
in the past to do something similar for another report. I will be
running the report daily and elivering by subscrition, so the parameter
needs to be part of the report. In this case, I want the expression to
evaluate what day of the week it is today and calculate what the date
was 2 working days ago. For example:
If today is Monday, then get last Thursday's date = today -4 days.
If today is Tuesday, then use the range Friday to Sunday (in case
anything is entered over the weekend) = -4 to -2 days.
If today is Wednesday, then get Monday's date = today -2 days.
If today is Thursday, then get Tuesday's date = today -2 days.
If today is Friday, then get Wednesday's date = today -2 days.
Can anybody help me - I'm struggling with how to use the date functions
like this?
Thanks!agenda9533 had a similar question and the answer was writing a user function
Search using DATEDIFF and look for agenda9533 's posting

DateTimestamp

Hi,
I currently have a column in a table that is a default of getdate()). My
question : Is there a way to capture just the date without the time appended
?
For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexes
on this datetimestamp field? Would a clustered index on this field help?
Almost all searching is done by the date field, whether it is searched by da
y
or a date range...Any better way than what is currently being done to help
with speed ? Currently we have a clustered index on this column. The column
currently contains approximately 12 million rows...
Thanks for your input,
WarrenChange the defualt to Cast (getdate() as Integer)
-- Datetimes as stored internally as a decimal value 4 bytes for the
integer portion, and another 4 bytes for the fractional portion. The 4 byte
s
for integer portion are the date, and the other 4 bytes are the time... So i
f
you cast the value to an integer, you truncate the time...
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||Store same time for all rows.
Example:
use tempdb
go
create table dbo.t (
colA int not null identity unique,
colB datetime default (convert(char(8), getdate(), 112))
)
insert into t default values
WAITFOR DELAY '00:00:00.999'
insert into t default values
WAITFOR DELAY '00:00:00.999'
insert into t default values
select * from t
drop table t
go
If you are planning to do a lot of selects filtering this column based on a
range of dates, then having a clustered index by this column will be helpful
.
AMB
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||There is no way to capture a date without the time portion; best thing to do
is probably write a function that sets each portion of the time to zero - us
e
DATEDIFF and DATEADD functions to subtract hours, minutes, seconds, and
milliseconds from the date.
With SQL 2000 32-bit datetimes are stored as 2 4-byte integers, so you can
convert the datetime to a binary(8), take the first 4 bytes and concat
0x00000000 to that and convert back to datetime to get zero hours, mins,
secs, & ms; but this method would not necessarily work on anything but SQL 2
k
32-bit.
As for a clustered index, only testing will tell...
KH
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||If you're really interested in speed, don;t use DateTime column use an
integer or smallint.. and store the integer you get from Cast (getDate()
as Integer) directly. This reduces the size of the data in the column frm
8bytes to 4 bytes (oor 2 bytes if you use smallint) By reducing the width o
f
the index, you will dramatically increase the number of entries you will be
able to store on each IO Page of the index, and increase the performance
using the index.
NOTE: If you use smallints, then you need to subtract 32768 from the valu
you get from Cast(getdate() as Integer) before stuffing it into the smallint
field, because smallint is SIGNED 2-byte integer, but smalldatetime uses
UNSIGNED 2-byte integer..
SmallDatetime goes from Integer value 0 represesnting 1 jan 1900, to 65536
representing 6 June 2079... whereas smallint goes from -32768 to +32767...
Using Regular datetime is no problem, because it uses SIGNED 4-byte Integer,
with values from
January 1, 1753 through December 31, (this way value 0 still represents 1
Jan 1900... )
If your queries against this table often use date as a range of dates...
then a clustered index on this column will increase performance
draamatically.
"Warren" wrote:

> Hi,
> I currently have a column in a table that is a default of getdate()). My
> question : Is there a way to capture just the date without the time append
ed?
> For example, 04/04/2005. Question 2: How does SQL Server 2000 use indexe
s
> on this datetimestamp field? Would a clustered index on this field help?
> Almost all searching is done by the date field, whether it is searched by
day
> or a date range...Any better way than what is currently being done to help
> with speed ? Currently we have a clustered index on this column. The colu
mn
> currently contains approximately 12 million rows...
> Thanks for your input,
> Warren|||Thanks for all the replys...
I found out that the client application inserts the date stamp in the row of
the database. The colun is a dattime field, so it's not the getdate()) that
I thought it was..
Any more thoughts?
Thanks,
Warren
"CBretana" wrote:
> If you're really interested in speed, don;t use DateTime column use an
> integer or smallint.. and store the integer you get from Cast (getDate(
)
> as Integer) directly. This reduces the size of the data in the column fr
m
> 8bytes to 4 bytes (oor 2 bytes if you use smallint) By reducing the width
of
> the index, you will dramatically increase the number of entries you will b
e
> able to store on each IO Page of the index, and increase the performance
> using the index.
> NOTE: If you use smallints, then you need to subtract 32768 from the valu
> you get from Cast(getdate() as Integer) before stuffing it into the smalli
nt
> field, because smallint is SIGNED 2-byte integer, but smalldatetime uses
> UNSIGNED 2-byte integer..
> SmallDatetime goes from Integer value 0 represesnting 1 jan 1900, to 6553
6
> representing 6 June 2079... whereas smallint goes from -32768 to +32767...
> Using Regular datetime is no problem, because it uses SIGNED 4-byte Intege
r,
> with values from
> January 1, 1753 through December 31, (this way value 0 still represents 1
> Jan 1900... )
> If your queries against this table often use date as a range of dates...
> then a clustered index on this column will increase performance
> draamatically.
> "Warren" wrote:
>|||Then you need to
a) change the way the client write the data to strip off the time portion,
How is client writing to Database? using CLient constructed SQL,
Through a Stored Proc, etc.?
b) write an Insert/ update trigger on the database table that strips off the
time portion whenver client tries t owrite time into the table
In either case, To speed things up, same comments I made earlier about
datatypes still apply...
"Warren" wrote:
> Thanks for all the replys...
> I found out that the client application inserts the date stamp in the row
of
> the database. The colun is a dattime field, so it's not the getdate()) th
at
> I thought it was..
> Any more thoughts?
> Thanks,
> Warren
> "CBretana" wrote:
>sql

Datetime to time conversion with default date

Hi,

I am importing a csv file to SQL 2005 table. The source column is coming as datetime. The destination filed is a datetime type. I would like to update the destination with the time part from the source. I used the data conversion to convert it to time using "database time[DT_DBTIME]". For a source value "2/08/2007 21:51:07" this inserts a value "2007-08-03 21:51:07.000". I need the column to have a value as "1900-01-01 21:57:07.000".

Can someone please tell me how do I do this conversion?

Thanks,

Try this in a Derived Column transform (replace DateValue with the name of your column):

Code Snippet

(DT_DBTIMESTAMP)("1900-01-01 " + (DT_WSTR,10)(DT_DBTIME)DateValue)

|||

Thanks, jwelch.

datetime report parameter default value

Issue 1:

I have a report parameter StartDateTime. I set the default value to Now(). When I go to preview, the StartDateTime parameter is empty and its been locked. I am not even able to set it to different value in preview.

Can anyone help me how to set the datetime parameter to default value(Now).

Issue 2:

I have a stored procedure which takes StartDateTime parameter. Whenever the report refreshes using autorefresh interval, the startdatetime should default to Now. Right now the startdatetime defaults to whatever the value is there before i hit view report. how to do that using stored procedure.

Thanks fro your help.

I don't know about Issue 2, but are you using "=Now()" to populate the StartDateTime report parameter? Also, is the report parameter type set to "datetime"? I think fixing Issue 1 should automatically solve Issue 2 since it looks like the report isn't populating with the correct default date value.|||

hey, Thanks for u'r reply. Now it started working. I mean using "=Now()" I could set the default value of StartDateTime.

Regarding Issue2, Whatever the value is there in the StartDateTime, its retaining for autorefresh. Its not taking the CurrentDateTime.

|||

Suppose the user enters a date for the startdate, and then the report refreshes. I assume that in that case you do not want the start date to change when the report .

If my assumption is correct, I think the best way for you to accomplish this would be to set the parameter to allow a null value, and use null to indicate that the user wants to run the report using the current time. This way, when the report refreshes the startdate will still be null, and the report data will adjust.

Unfortunately, there is no way to have the parameter show the current time and return null, and still allow the user to enter a date manually.

|||

Thanks for your reply. There is more clarification from the customer on the requirements. Little bit of changes to the original question I posted..

We are trying to use a SSRS report as a dashboard on a TV screen. I am facing two issues that I need advice on

Issue 1 : We are using End Date Time and Duration as user selectable parameters. We autorefresh the report once every 15 minutes using report properties refresh. Essentially the customer wants a rolling time window report the auto refreshes.

If we use Now() function for End Date Time, at the end of the 15 minutes it still uses the the time from first time instead of the time at the refresh. How do I fix this?

Issue 2: We are using a custom ASP.NET appliction in which the SSRS Report Viewer displays the report. When the report refreshes, it does not retain the window scroll position. How can I fix it?

Thanks in advance.

|||

Sorry it took so long for me to get back to you. hopefully you found a solution already, but if not here are my thoughts:

The first issue is actually two issues:

How to get a report to run using a 'rolling time period' on auto-refresh, and

how to get the parameters to change at refresh so that they match the reporting period.

The solution to the first is outlined above. Instead of using now(), use getdate() in your sql query.

The solution to the latter is more difficult, if not impossible. I would suggest not displaying the reporting period in the parameters (you can put it in the body of the report or something.)

I have had some luck in the past getting parameters to refresh if I put them in the available values instead of in the default values, but that doesn't seem to be working for me when I ran a quick test, so either I've forgotten how to do it, or I never actually got it to work the first time.

I'm not sure what the best way to approach the second problem is. Can you intercept the scroll events, or are they happening inside the ssrss viewer?

sql

datetime report parameter default value

Issue 1:

I have a report parameter StartDateTime. I set the default value to Now(). When I go to preview, the StartDateTime parameter is empty and its been locked. I am not even able to set it to different value in preview.

Can anyone help me how to set the datetime parameter to default value(Now).

Issue 2:

I have a stored procedure which takes StartDateTime parameter. Whenever the report refreshes using autorefresh interval, the startdatetime should default to Now. Right now the startdatetime defaults to whatever the value is there before i hit view report. how to do that using stored procedure.

Thanks fro your help.

I don't know about Issue 2, but are you using "=Now()" to populate the StartDateTime report parameter? Also, is the report parameter type set to "datetime"? I think fixing Issue 1 should automatically solve Issue 2 since it looks like the report isn't populating with the correct default date value.|||

hey, Thanks for u'r reply. Now it started working. I mean using "=Now()" I could set the default value of StartDateTime.

Regarding Issue2, Whatever the value is there in the StartDateTime, its retaining for autorefresh. Its not taking the CurrentDateTime.

|||

Suppose the user enters a date for the startdate, and then the report refreshes. I assume that in that case you do not want the start date to change when the report .

If my assumption is correct, I think the best way for you to accomplish this would be to set the parameter to allow a null value, and use null to indicate that the user wants to run the report using the current time. This way, when the report refreshes the startdate will still be null, and the report data will adjust.

Unfortunately, there is no way to have the parameter show the current time and return null, and still allow the user to enter a date manually.

|||

Thanks for your reply. There is more clarification from the customer on the requirements. Little bit of changes to the original question I posted..

We are trying to use a SSRS report as a dashboard on a TV screen. I am facing two issues that I need advice on

Issue 1 : We are using End Date Time and Duration as user selectable parameters. We autorefresh the report once every 15 minutes using report properties refresh. Essentially the customer wants a rolling time window report the auto refreshes.

If we use Now() function for End Date Time, at the end of the 15 minutes it still uses the the time from first time instead of the time at the refresh. How do I fix this?

Issue 2: We are using a custom ASP.NET appliction in which the SSRS Report Viewer displays the report. When the report refreshes, it does not retain the window scroll position. How can I fix it?

Thanks in advance.

|||

Sorry it took so long for me to get back to you. hopefully you found a solution already, but if not here are my thoughts:

The first issue is actually two issues:

How to get a report to run using a 'rolling time period' on auto-refresh, and

how to get the parameters to change at refresh so that they match the reporting period.

The solution to the first is outlined above. Instead of using now(), use getdate() in your sql query.

The solution to the latter is more difficult, if not impossible. I would suggest not displaying the reporting period in the parameters (you can put it in the body of the report or something.)

I have had some luck in the past getting parameters to refresh if I put them in the available values instead of in the default values, but that doesn't seem to be working for me when I ran a quick test, so either I've forgotten how to do it, or I never actually got it to work the first time.

I'm not sure what the best way to approach the second problem is. Can you intercept the scroll events, or are they happening inside the ssrss viewer?

DateTime problem

I need to UPDATE DateTime in database. But input paramter (@.DatumPozadovany) is not in Default format 'mon dd yyyy hh:miAM', but in Europian one 'dd mon yyyy HH:mi'.

This code don't work, becuse it converts standart input into unstandart output. I need the oposite of it.

UPDATE DoslaObjednavka SET DatumPozadovany = CONVERT(datetime, @.DatumPozadovany, 13), Oznaceni = @.Oznaceni WHERE (Id = @.Id)

Please, help.

SELECT CONVERT(DATETIME, GETDATE(),109)

--returns 2007-02-12 21:09:56.970

SELECT CONVERT(nvarchar(26), GETDATE(),109)

--returns Feb 12 2007 9:09:56:970PM

From the SQL Server 2005 Books Online topic
CAST and CONVERT (Transact-SQL)
"In the following table, the two columns on the left represent the style values for converting datetime or smalldatetime data to character data. Add 100 to a style value to obtain a four-place year that includes the century (yyyy).

|||Interestingly, you should try without the convert. If you let SQL do the conversion implicitly, it may well recognise the format you're using anyway. If you specify a format, it will have to match it exactly.

Rob|||Thaks everyone for reply. Error was at very different place.
In ASP.NET i didn't specify parametr datatype. It expected some strange datetime format and didn't work. Now it works fine.

DateTime Parameter

Reporting Services 2005 seems to have a problem with DateTime parameters.
One report has default datetime parameters. It uses the navigation feature
to call a second report, with the same parameters passed to the second
report. In the designer this works fine but when deployed the second report
crashes with an rsReportParameterTypeMismatch error.
This only happens when the dates being passed are non/ambiguous US/English.
In other words 6/1/06 will be passed OK 16/1/06 will not. Similarly 6-Jan-06
works, 16-Jan-06 doesn't.
So it's not really a type mismatch error but a dataconversion issue.
Both reports are set to language en-GB
Any help welcome!
Bob
--
--
BobBut if the parms are being passed as DateTimes instances, then there should
be no string format issues. Am I missing something?
--
William Stacey [MVP]
"Bob" <bmorris@.swiftkenya.com(nospamm)> wrote in message
news:9A881BF6-AFD2-4213-889A-F26EC8209F74@.microsoft.com...
| Reporting Services 2005 seems to have a problem with DateTime parameters.
|
| One report has default datetime parameters. It uses the navigation feature
| to call a second report, with the same parameters passed to the second
| report. In the designer this works fine but when deployed the second
report
| crashes with an rsReportParameterTypeMismatch error.
|
| This only happens when the dates being passed are non/ambiguous
US/English.
| In other words 6/1/06 will be passed OK 16/1/06 will not. Similarly
6-Jan-06
| works, 16-Jan-06 doesn't.
|
| So it's not really a type mismatch error but a dataconversion issue.
|
| Both reports are set to language en-GB
|
| Any help welcome!
|
| Bob
| --
| --
| Bob

datetime default value

Hi I am designing a table with vs.net 2003 and have a column that has a
datetime datatype. Just wondering if anyone knows if there is a way to set
its' default value to the current system time, like Now().
thanks.
--
Paul G
Software engineer.getdate()
Walter
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> Hi I am designing a table with vs.net 2003 and have a column that has a
> datetime datatype. Just wondering if anyone knows if there is a way to
> set
> its' default value to the current system time, like Now().
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks. I ended up setting a constraint with SQL in the query analyzer
but your method appears much easier.
--
Paul G
Software engineer.
"Walter Mallon" wrote:

> getdate()
> Walter
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
>
>

datetime default value

Hi I am designing a table with vs.net 2003 and have a column that has a
datetime datatype. Just wondering if anyone knows if there is a way to set
its' default value to the current system time, like Now().
thanks.
--
Paul G
Software engineer.getdate()
Walter
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> Hi I am designing a table with vs.net 2003 and have a column that has a
> datetime datatype. Just wondering if anyone knows if there is a way to
> set
> its' default value to the current system time, like Now().
> thanks.
> --
> Paul G
> Software engineer.|||ok thanks. I ended up setting a constraint with SQL in the query analyzer
but your method appears much easier.
--
Paul G
Software engineer.
"Walter Mallon" wrote:
> getdate()
> Walter
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:B298C250-A8F0-4280-8DFF-B80B2A93FA64@.microsoft.com...
> > Hi I am designing a table with vs.net 2003 and have a column that has a
> > datetime datatype. Just wondering if anyone knows if there is a way to
> > set
> > its' default value to the current system time, like Now().
> > thanks.
> > --
> > Paul G
> > Software engineer.
>
>

DATETIME Default getDate() query

Hi,
I have created a datagrid within an ASP.NET page that links to an SQL
table - this works just fine. One of the columns retrieves the date which a
particular row was populated. However, the format
which the date is returned is:
19.12.2004 13:03:25
I only want to display the date that the row was populated, not the exact
time as well. When I created the table I used the following code (I've cut
out the rest of the fields):
CREATE TABLE Users
(
Paul,
You can use this as your default:
Default dateadd(d,0,datediff(d,0,getdate()))
It works by finding the number of day boundaries between day 0 (January
1, 1900) and now, and adds that many days back to day 0.
Steve Kass
Drew University
Paul Evans wrote:

>Hi,
>I have created a datagrid within an ASP.NET page that links to an SQL
>table - this works just fine. One of the columns retrieves the date which a
>particular row was populated. However, the format
>which the date is returned is:
>19.12.2004 13:03:25
>I only want to display the date that the row was populated, not the exact
>time as well. When I created the table I used the following code (I've cut
>out the rest of the fields):
>CREATE TABLE Users
>(
> .
> .
> .
> .
> u_entrydate DATETIME Default getDate()
>)
>
>Is this to do with the DATETIME Default getDate() code for creating the
>column? Is there something else I could use to get just the date only, with
>out the time of day the row was populated?
>I'm using a microsoft SQL server.
>Thanks for your time
>Paul Evans
>P.S. or is it a problem with my ASP.NET code?
>
>

datetime column formula

Hello all:

Using EM to add a column Date_Entered with data type dateTime, what is the syntax to default the date to current date when record is added and to ensure it does not update if the record is modified at some other time in the future. Is it also possible to exclude the time when the column is updated (instead of 4/18/2003 9:32:56 PM the colum would be derived as 4/18/2003)

Is there a publication with listing of all legal suntax used in SQL2000?

Thank youbol has the syntax.

I don't advise using e-m to update the schema.

The sql would be something like

alter table x add dte datetime not null default convert(varchar(8),getdate(),112)

The column is not updated - only defaulted on insert.

If you want it to be set to the current date on update you can do it in a trigger.

Datetimes always include a time - the above will set it to midnight. It is up to you the format in which you display it.

DATETIME as Primary Key

Hello All,
We have a strange issue.
Environment:
SQL 2K SP3a
TableA Definitioin:
Col1 varchar(10) NOT NULL
Col2 datetime NOT NULL default getdate()
Primary Key col1 and col2
Many inserts are occuring and we are receiving primary key vilolations.
The Insert statement does not explicitly select the datetime just uses the
default on the column declaration.
I.E INSERT TABLE1(col1)
SELECT 'TEST' FROM MyTable
Anyone have an idea why?Fred,
datetime measures time only in increments of 1/300 of a
second. Many rows can be inserted within a single "tick"
of datetime, and apparently this is the case for you, some
of them having matching Col1 values.
It sounds like (Col1, Col2) is not a good choice of primary key.
Steve Kass
Drew University
FredG wrote:

>Hello All,
>We have a strange issue.
>Environment:
>SQL 2K SP3a
>TableA Definitioin:
>Col1 varchar(10) NOT NULL
>Col2 datetime NOT NULL default getdate()
>Primary Key col1 and col2
>Many inserts are occuring and we are receiving primary key vilolations.
>The Insert statement does not explicitly select the datetime just uses the
>default on the column declaration.
>I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
>Anyone have an idea why?
>
>|||Hi Steve,
That makes great sense. So, if the inserts are happening very frequently,
within that 1/300 of a second window, this would cause the error.
"Steve Kass" wrote:

> Fred,
> datetime measures time only in increments of 1/300 of a
> second. Many rows can be inserted within a single "tick"
> of datetime, and apparently this is the case for you, some
> of them having matching Col1 values.
> It sounds like (Col1, Col2) is not a good choice of primary key.
> Steve Kass
> Drew University
> FredG wrote:
>
>|||That's because rows (with the same Col1 value) are being inserted
within 300 milliseconds of each other
Denis the SQL Menace
http://sqlservercode.blogspot.com/
FredG wrote:
> Hello All,
> We have a strange issue.
> Environment:
> SQL 2K SP3a
> TableA Definitioin:
> Col1 varchar(10) NOT NULL
> Col2 datetime NOT NULL default getdate()
> Primary Key col1 and col2
> Many inserts are occuring and we are receiving primary key vilolations.
> The Insert statement does not explicitly select the datetime just uses the
> default on the column declaration.
> I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
> Anyone have an idea why?|||FredG wrote:
> Hello All,
> We have a strange issue.
> Environment:
> SQL 2K SP3a
> TableA Definitioin:
> Col1 varchar(10) NOT NULL
> Col2 datetime NOT NULL default getdate()
> Primary Key col1 and col2
> Many inserts are occuring and we are receiving primary key vilolations.
> The Insert statement does not explicitly select the datetime just uses the
> default on the column declaration.
> I.E INSERT TABLE1(col1)
> SELECT 'TEST' FROM MyTable
> Anyone have an idea why?
If your inserts occur less than 1/300th of a second apart or if you do
multiple row inserts then you will get duplicates. Also you've used
local time, which means you could get duplicates if your local time is
adjusted for DST and then back again.
If you need to record time more precisely then you'll have to populate
some other time value without relying on GETDATE() and DATETIME.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
You mention that duplicate values would be generated because of DST.
How would you get around this problem? I've never really thought about
it until seeing your post.
I don't have a specific example or problem - just curious really.
Thanks
Barry|||Barry wrote:
> Hi David,
> You mention that duplicate values would be generated because of DST.
> How would you get around this problem? I've never really thought about
> it until seeing your post.
Always make sure that your system is down for maintenance during that
one hour window each year :-)
-Tom.|||Ha! I like your style...just kick the plug out ;-)|||Barry wrote:
> Hi David,
> You mention that duplicate values would be generated because of DST.
> How would you get around this problem? I've never really thought about
> it until seeing your post.
> I don't have a specific example or problem - just curious really.
> Thanks
> Barry
Use GETUTCDATE() instead.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Ah right ok.
Thanks
Barry

DatePicker as a Report Parameter

Is there a way in RS2000 that you can change the default textbox for a date
field parameter to a datepicker? As this would simplify input obviously for
the end user!
MarkNo, that's not possible with RS. But I've read that you can do your own web
service and add that functionality from there (don't ask me how)
--
Please mark the correct/helpful answers!|||Yea ive decided to create my own front end in asp.net, and use postback to
get the info from RS.
I read about the datepicker functionality being available in RS2005? Is
this true?
"F. Dwarf [MCP]" wrote:
> No, that's not possible with RS. But I've read that you can do your own web
> service and add that functionality from there (don't ask me how)
> --
> Please mark the correct/helpful answers!
>|||Honestly: I don't know
--
Please mark the correct/helpful answers!
"Mark" wrote:
> Yea ive decided to create my own front end in asp.net, and use postback to
> get the info from RS.
> I read about the datepicker functionality being available in RS2005? Is
> this true?
> "F. Dwarf [MCP]" wrote:
> > No, that's not possible with RS. But I've read that you can do your own web
> > service and add that functionality from there (don't ask me how)
> > --
> > Please mark the correct/helpful answers!
> >
> >|||RS 2005 has a datepicker (plus multi-select parameters and end user
sorting).
RS will be released in November (getting very close now)
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:94E02A91-C84B-4E5B-96D9-3C432378ED6C@.microsoft.com...
> Yea ive decided to create my own front end in asp.net, and use postback to
> get the info from RS.
> I read about the datepicker functionality being available in RS2005? Is
> this true?
> "F. Dwarf [MCP]" wrote:
>> No, that's not possible with RS. But I've read that you can do your own
>> web
>> service and add that functionality from there (don't ask me how)
>> --
>> Please mark the correct/helpful answers!
>>|||Thanks Bruce, but knowing our company we wont upgrade to SQL2005 until march
at the earliest. So i guess im going to have to custom write somethign
instead :)
--
Mark
"Bruce L-C [MVP]" wrote:
> RS 2005 has a datepicker (plus multi-select parameters and end user
> sorting).
> RS will be released in November (getting very close now)
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:94E02A91-C84B-4E5B-96D9-3C432378ED6C@.microsoft.com...
> > Yea ive decided to create my own front end in asp.net, and use postback to
> > get the info from RS.
> >
> > I read about the datepicker functionality being available in RS2005? Is
> > this true?
> >
> > "F. Dwarf [MCP]" wrote:
> >
> >> No, that's not possible with RS. But I've read that you can do your own
> >> web
> >> service and add that functionality from there (don't ask me how)
> >> --
> >> Please mark the correct/helpful answers!
> >>
> >>
>
>|||Keep in mind that you can go to RS 2005 without upgrading the database to
2005 (you have to have a license for 2005 though). It is soooo much easier
and less costly to have RS 2005 versus developing your own frontend.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:5E9DA913-E9F2-4190-BB27-2A109F46AEBF@.microsoft.com...
> Thanks Bruce, but knowing our company we wont upgrade to SQL2005 until
> march
> at the earliest. So i guess im going to have to custom write somethign
> instead :)
> --
> Mark
>
> "Bruce L-C [MVP]" wrote:
>> RS 2005 has a datepicker (plus multi-select parameters and end user
>> sorting).
>> RS will be released in November (getting very close now)
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mark" <Mark@.discussions.microsoft.com> wrote in message
>> news:94E02A91-C84B-4E5B-96D9-3C432378ED6C@.microsoft.com...
>> > Yea ive decided to create my own front end in asp.net, and use postback
>> > to
>> > get the info from RS.
>> >
>> > I read about the datepicker functionality being available in RS2005?
>> > Is
>> > this true?
>> >
>> > "F. Dwarf [MCP]" wrote:
>> >
>> >> No, that's not possible with RS. But I've read that you can do your
>> >> own
>> >> web
>> >> service and add that functionality from there (don't ask me how)
>> >> --
>> >> Please mark the correct/helpful answers!
>> >>
>> >>
>>|||Does that mean you can install RS2005 on SQL 2000?
--
Mark
"Bruce L-C [MVP]" wrote:
> Keep in mind that you can go to RS 2005 without upgrading the database to
> 2005 (you have to have a license for 2005 though). It is soooo much easier
> and less costly to have RS 2005 versus developing your own frontend.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:5E9DA913-E9F2-4190-BB27-2A109F46AEBF@.microsoft.com...
> > Thanks Bruce, but knowing our company we wont upgrade to SQL2005 until
> > march
> > at the earliest. So i guess im going to have to custom write somethign
> > instead :)
> > --
> > Mark
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> RS 2005 has a datepicker (plus multi-select parameters and end user
> >> sorting).
> >>
> >> RS will be released in November (getting very close now)
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> >> news:94E02A91-C84B-4E5B-96D9-3C432378ED6C@.microsoft.com...
> >> > Yea ive decided to create my own front end in asp.net, and use postback
> >> > to
> >> > get the info from RS.
> >> >
> >> > I read about the datepicker functionality being available in RS2005?
> >> > Is
> >> > this true?
> >> >
> >> > "F. Dwarf [MCP]" wrote:
> >> >
> >> >> No, that's not possible with RS. But I've read that you can do your
> >> >> own
> >> >> web
> >> >> service and add that functionality from there (don't ask me how)
> >> >> --
> >> >> Please mark the correct/helpful answers!
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Yes it does. You need a license but you do not need 2005 db. As a matter of
fact, that is what I will be doing.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:908C8151-DF21-4EAF-A44A-35AFF365D8F2@.microsoft.com...
> Does that mean you can install RS2005 on SQL 2000?
> --
> Mark
>
> "Bruce L-C [MVP]" wrote:
>> Keep in mind that you can go to RS 2005 without upgrading the database to
>> 2005 (you have to have a license for 2005 though). It is soooo much
>> easier
>> and less costly to have RS 2005 versus developing your own frontend.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mark" <Mark@.discussions.microsoft.com> wrote in message
>> news:5E9DA913-E9F2-4190-BB27-2A109F46AEBF@.microsoft.com...
>> > Thanks Bruce, but knowing our company we wont upgrade to SQL2005 until
>> > march
>> > at the earliest. So i guess im going to have to custom write somethign
>> > instead :)
>> > --
>> > Mark
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> RS 2005 has a datepicker (plus multi-select parameters and end user
>> >> sorting).
>> >>
>> >> RS will be released in November (getting very close now)
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Mark" <Mark@.discussions.microsoft.com> wrote in message
>> >> news:94E02A91-C84B-4E5B-96D9-3C432378ED6C@.microsoft.com...
>> >> > Yea ive decided to create my own front end in asp.net, and use
>> >> > postback
>> >> > to
>> >> > get the info from RS.
>> >> >
>> >> > I read about the datepicker functionality being available in RS2005?
>> >> > Is
>> >> > this true?
>> >> >
>> >> > "F. Dwarf [MCP]" wrote:
>> >> >
>> >> >> No, that's not possible with RS. But I've read that you can do your
>> >> >> own
>> >> >> web
>> >> >> service and add that functionality from there (don't ask me how)
>> >> >> --
>> >> >> Please mark the correct/helpful answers!
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||Can anyone point me in the direction where I can find more documentation on
this? My company is interested in upgrading to reporting serviceus 2005 and
keeping our exisiting slq server 2000 installation. So far we have been
geeting mixed signals.
Thanks
--
kmatth007
"Bruce L-C [MVP]" wrote:
> Yes it does. You need a license but you do not need 2005 db. As a matter of
> fact, that is what I will be doing.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> news:908C8151-DF21-4EAF-A44A-35AFF365D8F2@.microsoft.com...
> > Does that mean you can install RS2005 on SQL 2000?
> >
> > --
> > Mark
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Keep in mind that you can go to RS 2005 without upgrading the database to
> >> 2005 (you have to have a license for 2005 though). It is soooo much
> >> easier
> >> and less costly to have RS 2005 versus developing your own frontend.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> >> news:5E9DA913-E9F2-4190-BB27-2A109F46AEBF@.microsoft.com...
> >> > Thanks Bruce, but knowing our company we wont upgrade to SQL2005 until
> >> > march
> >> > at the earliest. So i guess im going to have to custom write somethign
> >> > instead :)
> >> > --
> >> > Mark
> >> >
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> RS 2005 has a datepicker (plus multi-select parameters and end user
> >> >> sorting).
> >> >>
> >> >> RS will be released in November (getting very close now)
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "Mark" <Mark@.discussions.microsoft.com> wrote in message
> >> >> news:94E02A91-C84B-4E5B-96D9-3C432378ED6C@.microsoft.com...
> >> >> > Yea ive decided to create my own front end in asp.net, and use
> >> >> > postback
> >> >> > to
> >> >> > get the info from RS.
> >> >> >
> >> >> > I read about the datepicker functionality being available in RS2005?
> >> >> > Is
> >> >> > this true?
> >> >> >
> >> >> > "F. Dwarf [MCP]" wrote:
> >> >> >
> >> >> >> No, that's not possible with RS. But I've read that you can do your
> >> >> >> own
> >> >> >> web
> >> >> >> service and add that functionality from there (don't ask me how)
> >> >> >> --
> >> >> >> Please mark the correct/helpful answers!
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||I know it is true. I have even corrected a Microsoft employee posting here.
When I did that I notified people within MS to get out the word. So I know
this is true and I have done it myself. I will ask MS about getting
something official about this on their website (or if it is already there I
will find out where it is).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"kmatth007" <kevin@.networkoptions.net> wrote in message
news:69C21C93-CBD5-49A8-8771-8930E07B267D@.microsoft.com...
> Can anyone point me in the direction where I can find more documentation
> on
> this? My company is interested in upgrading to reporting serviceus 2005
> and
> keeping our exisiting slq server 2000 installation. So far we have been
> geeting mixed signals.
> Thanks
> --
> kmatth007
>
> "Bruce L-C [MVP]" wrote:
>> Yes it does. You need a license but you do not need 2005 db. As a matter
>> of
>> fact, that is what I will be doing.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "Mark" <Mark@.discussions.microsoft.com> wrote in message
>> news:908C8151-DF21-4EAF-A44A-35AFF365D8F2@.microsoft.com...
>> > Does that mean you can install RS2005 on SQL 2000?
>> >
>> > --
>> > Mark
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Keep in mind that you can go to RS 2005 without upgrading the database
>> >> to
>> >> 2005 (you have to have a license for 2005 though). It is soooo much
>> >> easier
>> >> and less costly to have RS 2005 versus developing your own frontend.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "Mark" <Mark@.discussions.microsoft.com> wrote in message
>> >> news:5E9DA913-E9F2-4190-BB27-2A109F46AEBF@.microsoft.com...
>> >> > Thanks Bruce, but knowing our company we wont upgrade to SQL2005
>> >> > until
>> >> > march
>> >> > at the earliest. So i guess im going to have to custom write
>> >> > somethign
>> >> > instead :)
>> >> > --
>> >> > Mark
>> >> >
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> RS 2005 has a datepicker (plus multi-select parameters and end user
>> >> >> sorting).
>> >> >>
>> >> >> RS will be released in November (getting very close now)
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "Mark" <Mark@.discussions.microsoft.com> wrote in message
>> >> >> news:94E02A91-C84B-4E5B-96D9-3C432378ED6C@.microsoft.com...
>> >> >> > Yea ive decided to create my own front end in asp.net, and use
>> >> >> > postback
>> >> >> > to
>> >> >> > get the info from RS.
>> >> >> >
>> >> >> > I read about the datepicker functionality being available in
>> >> >> > RS2005?
>> >> >> > Is
>> >> >> > this true?
>> >> >> >
>> >> >> > "F. Dwarf [MCP]" wrote:
>> >> >> >
>> >> >> >> No, that's not possible with RS. But I've read that you can do
>> >> >> >> your
>> >> >> >> own
>> >> >> >> web
>> >> >> >> service and add that functionality from there (don't ask me how)
>> >> >> >> --
>> >> >> >> Please mark the correct/helpful answers!
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

datefirst in syslanguages

The accounting calandar for my project requires that weeks start on Saturday, not Sunday (which is the default for English). I want to set datefirst to 6 (Saturday) in syslanguages for English so that I don't have to set it for each date transaction.

In SQL 2000 I hacked it by editing the British language to set datefirst to 6 and dateformat to mdy. Then I set all users of my database to use the British language. This has worked fine for years.

In 2005 I don't know how to edit sys.syslanguages to make these changes and I don't know any other way to globally set the datefirst to 6 (for English preferrably).

I am very frustrated with SQL Server 2005.

Any help would be appreciated.

Thanks,

Sue

Great question. Not sure if there is anything you could do other than setting datefirst for each connection.

I'll post in the private group and see if anyone knows of a way.

Modifying system tables are no longer possible in sql2k5.|||

I appreciate this. Everything about my application revolves around the first day of the week being Saturday, not Sunday. I guess I will look into how much work it will be to start the set command within all calculations, functions, computed columns, etc.

Unfortunately, I don't think I can do a set in a view and this is REALLY gonna hurt.

Any information you can provide would be appreciated.

Sue

|||Look like there isn't anything you can set it globally. Sorry.|||

Wow.

Do you know a way to add a new language that I define? Then I can assign users to this new language.

Thank you.

|||No. It's not possible to do so. The data for syslanguages comes from OpenRowset(TABLE
SYSLANG) which is an internal call to a dll file.|||

Simply amazing......

Ok, for others who may need to do this, I saw a suggestion in another newsgroup for a way to tackle this problem.

Create a table full of the dates for all the days of the years you are interested in and create your own wk reference column.

It looks like I can set datefirst within a stored procedure, but not a function, view, or computed column, so the table solution may be my only option.

The ramifications of this are large for me. If anyone has other suggestions, I'd be glad to hear them.

DateAdd() in Default Value

My goal is to set the default value of a smalldatetime field to the GetDate() plus 4 hours. I figure I need to use DateAdd() for this, but can't figure out how to place it in a default value. I can put GetDate() in default value, but DateAdd(hour,4,GetDate()) throws a syntax error.

Is there a simple way to accomplish this?

bes7252,

What error are you seeing, exactly? I have no problem executing the following code:

create table t(
i int,
d datetime default (dateadd(hour,3,getdate()))
)
go

drop table t

Have you left out a ) somewhere?

Steve Kass
Drew University
http://www.stevekass.com
|||

Logically it doesn’t make any sense to have this expression on your column. Suppose if you generate the expression on your stored date then it is acceptable.

|||

Steve,

I tried it again and didn't have any problems. I don't remember the error I was seeing yesterday, but must have had a typo-o in my expression. (I typed the one on the post from memory, not copy/paste).

Thanks!

Brian

DateAdd expression works in tsql but doesn't work in ssis

Hi There,

I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?

dateadd("dd", -1, datediff("dd", 0, getdate()))

Thanks.

Some more info please. What do you mean by "it doesn't work"? Do you get an error or the wrong result?

If the latter, tell us what you result you get and also what result you are expecting to get.

Thanks

-Jamie

|||DateDiff returns an integer while DateAdd expects a datetime in that position. T-SQL is able to implicitly cast dates to integers, while SSIS cannot.

|||

Ok..If you run the below query in query analyzer..

select dateadd("dd", -1, datediff("dd", 0, getdate()))

it gives me.."2007-05-08 00:00:00.000". I would like to get the same value in ssis. In ssis, if I use the above as an expression for a variable, I get a design time error. "The expression for variable failed evaluation, there was an error in the expression".

Thanks for responding.

|||

Ok..you are right..so can i cast it like this..

dateadd("dd", -1, (DT_DBTIMESTAMP)(datediff("dd", 0, getdate()))). This doesn't work either. How do I cast it?

Thanks.

|||

Sam_res03 wrote:

Ok..you are right..so can i cast it like this..

dateadd("dd", -1, (DT_DBTIMESTAMP)(datediff("dd", 0, getdate()))). This doesn't work either. How do I cast it?

Thanks.

You'd have to use DateAdd to perform the cast from integer to date and thus define 0 as 1/1/1900 the way T-SQL does.

dateadd("dd", -1,
dateadd("dd",
datediff("dd",
dateadd("dd",0,(DT_DBDATE)"1/1/1900")
, getdate())
,(DT_DBDATE)"1/1/1900")
)

|||

Hi Jay,

Thanks for your reply. I really appreciate it. Event though your sol works, I thought I would use this instead..

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 00:00:00") as this was much readable. I am sure this works for all situations.

So

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 00:00:00")

gives 5/8/2007 00:00:00

and

(DT_DBTIMESTAMP)((DT_WSTR,4) Year( DateAdd("d",-1,getdate())) + "-"+(DT_WSTR,4) Month( DateAdd("d",-1,getdate()))+"-"+(DT_WSTR,4) Day(DateAdd("d",-1,getdate())) + (DT_WSTR,12)" 23:59:59")

gives 5/8/2007 11:59 PM

I am not sure which one is efficient though, probably yours...

Thanks