sqlserver2000 date: date

Showing posts with label date. Show all posts

Friday, March 30, 2012

Increase one month

Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
MichaelTry with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
--
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
--|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> > Hi,
> > I have a date value of 4/1/2005, I wanted to increase just one month
> > to 5/1/2005. I have millions of records like that to increase just one
> > month. Is there a function I can use?
> > Thanks a lot!!
> > Michael
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> 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
> --
thanks a lot all!!!

Increase one month

Hi,
I have a date value of 4/1/2005, I wanted to increase just one month
to 5/1/2005. I have millions of records like that to increase just one
month. Is there a function I can use?
Thanks a lot!!
Michael"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>
Lookup the DATEADD function in Books Online.
You will find that you can get more detailed and useful answers if you
supply more information. Read my signature.
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
--|||Try with dateadd(mm,1, datecolumn)
MC
"Michael" <michaelnx@.gmail.com> wrote in message
news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
> Hi,
> I have a date value of 4/1/2005, I wanted to increase just one month
> to 5/1/2005. I have millions of records like that to increase just one
> month. Is there a function I can use?
> Thanks a lot!!
> Michael
>|||On May 1, 3:58 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.org> wrote:
> "Michael" <michae...@.gmail.com> wrote in message
> news:1178048676.991639.192910@.n76g2000hsh.googlegroups.com...
>
>
>
> Lookup the DATEADD function in Books Online.
> You will find that you can get more detailed and useful answers if you
> supply more information. Read my signature.
> --
> 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
> --
thanks a lot all!!!

Monday, March 19, 2012

incorrect format of date report parameters via datepicker

OK - truck loads of folk have reported this problem - but I cannot solve it - so apologies for repeating the question...

My SQL Server 2005 Report is developed on the same server as it is run.

I have SQL Server 2005 SP2 installed. The report language is set as en-GB.

The browser language setting is en-NZ.

The report previews fine - I pick May 22 from the picker and it appears as 22/05/2007 in the report parameter text box - which since I am in NZ is exactly as I want it.

I deploy the report - If I run it from the report manager virtual directory the date picker performs as above i.e correctly.

I run the deployed report from the report server virtual directory and here is where the problem occurs - selecting May 22 from the date picker produces 05/22/2007 in the report parameter text box and produces the error

"The value provided for the report parameter 'StartScheduledDate' is not valid for its type. (rsReportParameterTypeMismatch)"

thanks for any help here - this problem has made this a ridiculously expensive report!

honestbob wrote:

I deploy the report - If I run it from the report manager virtual directory the date picker performs as above i.e correctly.

I run the deployed report from the report server virtual directory and here is where the problem occurs

Pardon me, what's the difference between the 2 sentenses? They both sound like you run from http://localhost/Reports?

I'd suggest looking at the Regional settings in your machine, and server, to make sure they're the same

e.g. Canada default is DD/MM/YYYY, while USA default is MM/DD/YYYY

|||

bob,

what i have done with this in the past is always do a CDate() on the output of the calendar control before trying to use that output, then i can Format() it any way i like. I also found when trying to force it to a non-US date format i couldn't then use the Preview tab in the IDE to check my report, as that version of the control insists on rendering/outputting everything in US format, regardless of what culture you have your report/IE/server set to.

So in summary, i find the web version of that control works fine if you CDate everything before using it. And like the previous poster said, check the regional settings on your report server.

|||

I was having the same problem but i managed to fix it.

edit the file "ReportViewer.aspx" on the reporting services machine.

it's in the "C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\Pages" by default,

and add the highlighted part with the desired culture value.

It solved the problem in my case.

<%@. Register TagPrefix="RS" Namespace="Microsoft.ReportingServices.WebServer" Assembly="ReportingServicesWebServer" %>

<%@. Page Language="C#" AutoEventWireup="true" Inherits="Microsoft.ReportingServices.WebServer.ReportViewerPage" Culture="pt-PT"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<html>

<title>Report Viewer</title>

</head>

<RS:ReportViewerHost ID="ReportViewerControl" runat="server" />

</form>

</body>

</html>

Hope this will solve your problem too.

Tiago Certal

|||

yes, that solved my problem too. I, too have tried fiddling with the regional settings on the database, the server, the local, the client computers etc. I would keep having the problem in IE, but not in Firefox. Firefox was fine.

so I tried to add the culture thing in, like Tiago suggested. only problem is that I think PT is portugese, so I had to use "en-CA" instead. Which then magically made the reports displayable in IE. Thank you Tiago!

problem is that now Firefox is having the same error. WHAT IS GOING ON?

INCORRECT DATE WHEN USING MOLAP AND HOLAP STORAGE.

When designing storage for my OLap cubes . I notice that the data is not correct when using MOLAP and HOLAP storage , but is absolutely correct when I use ROLAP .

I have a total of 204 aggregations.If I process only 18 of those aggregations ,the data is correct on all MOLAP HOLAP and HOLAP cubes . but if I go above 18 only ROLAP retrurns the correct data. If the data is correct in ROLAP shouldnt it be the same in MOLAP and HOLAP as well ?

No the data must be the same. I think you have wrong something else.
Do you notice that data is double or greater?
Check partitions!!!
Try making only one partition first.
I hope it helps you.|||Well, the data is slightly different , I am using the following version of SQL

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service

,only one Partition was created and that is the one done by the system while processing the cube . I am unable to create any additional ones beacuse this is the Standard version and it does not permit user defined partitions (I need the enerprise version to do that ) Could it be a problem with the version ?

I wish I could show you, its the weirdest thing ever . The Rolap cube is Spot on , processed to a %100 performance . POLAP and HOLAP are out by a little by about 2 % less that the correct data.It seems to be filtering out some of the data.

My problem is , it is Slooooooooooooowww , and not practical considering I'm working with a database with Millions of Records.

HELLLLLPPPP !!!!|||Let me give you an advice. Never proccess a cube more than 30% of performance.
If you have a large cube (more than 2 o 3 millons of records) and 15 or 20 dimensions then 5% - 15% of performance it's ok.
In my last job I have a cube with 30 millons of records and I have partitioned it monthly without aggregations. And the performance was good.
If you can't make partitions then proccess cube without aggregations.
Validate the cube structure and optimize it.
Have you many virtual dimensions? They are not good for performance.
Tell me if it improve the performance.
|||

To tell the truth , the cube was not optimized , I'll do that and get back to you .
I have no virtual dimensions present .

Incorrect date in column for 25,000 rows

I have a table with a column in it called Date, which is of the type DateTime, and for the last two years I have been adding data which I found out was incorrect.

My dates are all a day in the future, so I need to reduce each date by one day.

I can easily use a select script to reveal the 25,000 rows which are all incorrect dates. But I can't figure out how to update each and every row to subtract one day from each date.

So where I have:

26/01/2005

I would like to have:

25/01/2005

and of course for every record. Obviously way too many to do manually :-(

Can anyone show me a script that will get what I'm after.

Tia

Tailwag

After one your 25000 rows with invalid data, that is not nice to find out. But it is easy to solve, just run this query:

UPDATE [MyTable] SET [MyDate] = DATEADD(year, -1, [MyDate])

Don't forget to replace MyTable with your table name and MyDate with your datetime field name.
I have tested it before posting this solution, because i don't want to be responsible for lozing 25000 rows on friday.

|||

Also, replace year with day!

So the update query must be:

UPDATE [MyTable] SET [MyDate] = DATEADD(day, -1, [MyDate])

- Jeroen Boiten

|||Indeed! Thanks Jeroen, i overlooked that one. Thought only the years where incorrect.|||

Thank you PJ, and also Jeroen. I ran the query and 'Viola' it worked flawlessly, you guys are now extremely high on my best friends of all time list

Tia.

Tailwag

P.S. The B_ _ _ _ Friday curse has finally been thwarted!!!

Incorrect Date Format

I am moving data from an Interbase DB to SQL Server 2005. The date
columns for whatever reason in Interbase are not valid dates and they
have vChar field types. This is how they are formatted
2006-03-13-00.02.04 notice the extra hyphen between the date and time.
I have created an SSIS package import and would like to correct the
date then. I have looked at but not figured out how to use the 'derived
column' function in SSIS.
Does anyone have any tips on how to do this. Or maybe an easier way.This seems to convert the string into a format that can be converted to
datetime. Hopefully you find it helpful.
DECLARE @.str varchar(50)
SET @.str = '2006-03-13-00.02.04'
SELECT SUBSTRING(@.str, 1,10) + ' ' + SUBSTRING(@.str,12,
(datalength(@.str)-11))
SELECT convert(datetime,REPLACE((SUBSTRING(@.str
, 1,10) + ' ' +
SUBSTRING(@.str,12, (datalength(@.str)-11))),'.', ':'))
Keith Kratochvil
"Skip" <mike_sylvester@.voughtaircraft.com> wrote in message
news:1144252421.220884.54000@.j33g2000cwa.googlegroups.com...
>I am moving data from an Interbase DB to SQL Server 2005. The date
> columns for whatever reason in Interbase are not valid dates and they
> have vChar field types. This is how they are formatted
> 2006-03-13-00.02.04 notice the extra hyphen between the date and time.
> I have created an SSIS package import and would like to correct the
> date then. I have looked at but not figured out how to use the 'derived
> column' function in SSIS.
> Does anyone have any tips on how to do this. Or maybe an easier way.
>|||Thanks. I have come up with a similar conversion, but used in SSIS as a
drived column function. It converts to a valid date minus adding AM or
PM. Problem is that I add data conversion to change it into a date
field it fails? Does it actually need AM or PM added to it? Almost
there...
Here is my version
REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")|||I do not think has has anything to do with AM or PM. I think the field
conversion generates the typical data conversion loss statement, and
that is what is failing the SSIS package. Any thoughts on this..|||As long as the data is in 24 hour format you should not need AM/PM. Do you
have the data that you want to convert to a datetime stored within a table?
Can you try running my T-SQL against the data to see if it converts
correctly?
Keith Kratochvil
"Skip" <mike_sylvester@.voughtaircraft.com> wrote in message
news:1144261325.341459.91850@.i40g2000cwc.googlegroups.com...
> Thanks. I have come up with a similar conversion, but used in SSIS as a
> drived column function. It converts to a valid date minus adding AM or
> PM. Problem is that I add data conversion to change it into a date
> field it fails? Does it actually need AM or PM added to it? Almost
> there...
> Here is my version
> REPLACE(SUBSTRING([Data Conversion 1].START_OF_EXECUTION,1,10) + " " +
> RIGHT([Data Conversion 1].START_OF_EXECUTION,8),".",":")
>

Incorrect Date Conversion - Help Please

Dear Group

Some but not all of the dates are incorrect converted (incremented by 1
day) with the following SQL statement.

SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
ORDER BY ReturnedByDate ASC

'ReturnedByDate' is of type smalldatetime in the table. I need the
result set to return me the ReturnByDate once as actual date values
(for the value of a dropdown) and once as text values (for the text of
the same dropdown). It all works fine, it's just that some actual date
values are incremented by one day. The third column in the result set
sample represents the actual database value as a reference to give you
an idea of the original value.

ReturnedByDate LabelReturnedByDate ReturnedByDate
-------- ------ ------
2004-11-05 00:00:00.000 04.11.2004 2004-11-04 15:22:00
2004-11-11 00:00:00.000 11.11.2004 2004-11-11 00:00:00
2004-11-12 00:00:00.000 12.11.2004 2004-11-12 00:29:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 20:23:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 20:24:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 21:10:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 14:50:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:02:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:03:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:09:00
2004-12-09 00:00:00.000 09.12.2004 2004-12-09 08:09:00
Any help is very appreciated!

Thanks for your time & efforts!

MartinDECLARE @.ReturnedByDate smalldatetime
SET @.ReturnedByDate = '2004-11-04 15:22:00'

SELECT DISTINCT cast(@.ReturnedByDate AS real) AS Y,
cast(@.ReturnedByDate AS int) AS X,
CONVERT(datetime,cast(@.ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),@.ReturnedByDate,104) AS
LabelReturnedByDate, @.ReturnedByDate
-- FROM i2b_keytransactionhistory ORDER BY ReturnedByDate ASC

select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)|||DECLARE @.ReturnedByDate smalldatetime
SET @.ReturnedByDate = '2004-11-04 15:22:00'

select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)

-- Happy Holidays!
-- Kurt|||(theintrepidfox@.hotmail.com) writes:
> Some but not all of the dates are incorrect converted (incremented by 1
> day) with the following SQL statement.
> SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
> ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
> LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
> ORDER BY ReturnedByDate ASC

Converting datetime to int is not a very bright idea. For some reason,
that comes with rounding, so hours in the afternoon gets round up to
the next day.

Stick to convering your datetime to character.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 28 Dec 2004 18:26:55 -0800, theintrepidfox@.hotmail.com wrote:

>Some but not all of the dates are incorrect converted (incremented by 1
>day) with the following SQL statement.
>SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
>ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
>LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
>ORDER BY ReturnedByDate ASC

Hi Martin,

What Erland says :-)

But if you somehow REALLY need the number of days since 19000101 in your
frontend, the following should prove more reliable:

SELECT DATEDIFF(day, '19000101', ReturnedByDate)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, February 24, 2012

include the report date

Best case, I include the report date in the report header. Worst case, in
the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
anybody know how I can do this? nothing crazy, just like this:
ReportName xx/xx/xxxx
-- LynnI tried to add a text box into the header, just filling it w/this expression:
select LEFT(Dateadd(mm,-0,getdate()),11)
it fails indicating
'the value expression for the textbox 'textbox7' contains and error:
[BC30201] Expression expected.'
please advise
-- Lynn
"Lynn" wrote:
> Best case, I include the report date in the report header. Worst case, in
> the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
> anybody know how I can do this? nothing crazy, just like this:
> ReportName xx/xx/xxxx
> -- Lynn|||Please disregard, I got it. I included a text box with this as the
expression, everything is fine now, thanks anyway:
=Format(today()," dd MMM yy" )
-- Lynn
"Lynn" wrote:
> I tried to add a text box into the header, just filling it w/this expression:
> select LEFT(Dateadd(mm,-0,getdate()),11)
> it fails indicating
> 'the value expression for the textbox 'textbox7' contains and error:
> [BC30201] Expression expected.'
> please advise
> -- Lynn
>
> "Lynn" wrote:
> > Best case, I include the report date in the report header. Worst case, in
> > the subject line of the subscription email. I'm using SQL/RS v2000, VS 2003,
> > anybody know how I can do this? nothing crazy, just like this:
> >
> > ReportName xx/xx/xxxx
> >
> > -- Lynn|||Just an FYI, you can use expression builder, look at the global variables.
One of those has what you want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
> Best case, I include the report date in the report header. Worst case, in
> the subject line of the subscription email. I'm using SQL/RS v2000, VS
> 2003,
> anybody know how I can do this? nothing crazy, just like this:
> ReportName xx/xx/xxxx
> -- Lynn|||Will do, Bruce, thank you. I will check it out.
-- Lynn
oh, oh, just a slightly related question -- i've created my browser role and
i'm trying to send out this : http://webserver/reports
such that certain people can have direct access to a number of my reports
right now there's a 'Users Folders' in there along with my reports. I'm
unable to hide that. Do you know how to get rid of it?
"Bruce L-C [MVP]" wrote:
> Just an FYI, you can use expression builder, look at the global variables.
> One of those has what you want.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
> > Best case, I include the report date in the report header. Worst case, in
> > the subject line of the subscription email. I'm using SQL/RS v2000, VS
> > 2003,
> > anybody know how I can do this? nothing crazy, just like this:
> >
> > ReportName xx/xx/xxxx
> >
> > -- Lynn
>
>|||Yes, you can remove the browser role from that particular folder. Normally
the folder inherits from the parent so all folders initially have the same
roles assigned to them. Click on the folder, properties, security and remove
the browser role. Or, if it is just that you don't want them distracted by
it, you could hide in list view (properties, general).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:CDBC9A73-7FD8-4271-9BCE-73D03A0BD3D1@.microsoft.com...
> Will do, Bruce, thank you. I will check it out.
> -- Lynn
> oh, oh, just a slightly related question -- i've created my browser role
> and
> i'm trying to send out this : http://webserver/reports
> such that certain people can have direct access to a number of my reports
> right now there's a 'Users Folders' in there along with my reports. I'm
> unable to hide that. Do you know how to get rid of it?
> "Bruce L-C [MVP]" wrote:
>> Just an FYI, you can use expression builder, look at the global
>> variables.
>> One of those has what you want.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
>> news:DA80718F-E475-4891-93A5-EAEB1CB3D2D7@.microsoft.com...
>> > Best case, I include the report date in the report header. Worst case,
>> > in
>> > the subject line of the subscription email. I'm using SQL/RS v2000, VS
>> > 2003,
>> > anybody know how I can do this? nothing crazy, just like this:
>> >
>> > ReportName xx/xx/xxxx
>> >
>> > -- Lynn
>>

Sunday, February 19, 2012

in sqlserver2000 date ?

date(m/dd/yyyy)

9/18/2006

In this above table i want to select only the month in this format

Sep 2006 . How to do that ?

Where @.DATE is your date variable

try SELECT DATENAME(month, @.DATE) + ' ' + DATENAME(year, @.DATE)

Friday, March 30, 2012

Increase one month

Increase one month

Increase one month

Monday, March 19, 2012

incorrect format of date report parameters via datepicker

INCORRECT DATE WHEN USING MOLAP AND HOLAP STORAGE.

Incorrect date in column for 25,000 rows

Incorrect Date Format

Incorrect Date Conversion - Help Please

Friday, February 24, 2012

include the report date

Sunday, February 19, 2012

in sqlserver2000 date ?

sqlserver2000 date

Blog Archive

About Me