problem with inserting or updating dates in mssql 2000

Hello.I've read many topics about this problem but i couldn't figure it out.
I use form where user must insert 2 dates using texboxes.-One is required and other is optional.
Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report.
Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?
question 2. What to do if user left optional texbox date empty.

I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)
and parameters @.date1, sqldbtype date =texbox1.textYou can get the date as;

DateTime myDate = DateTime.ParseExact(txtDate.Text, "dd.M.yyyy", System.Globalization.CultureInfo.InvariantCulture);

Now that you have your text box's date as a DateTime variable you can supply that to a SQL parameter query in any format you want, such as dd MMM yyyy

|||I understand that.
I wanna know how to change format of date wich is inserted as d.M.yyyy in texbox to yyyy.M.d because it is passed to sp parameter and it must be yyyy.M.d to do successful insert/update?

Problem with handling dates

hi all
I have a stored procedure that gets date from a particular field using the DATEPART function. However, somewhere in between it has to do an update which is why it adds hours, days or months to the function. Here is the code
SELECT @.var_NewNextRunDate=(CAST(DATEPART(YYYY,@.DT) AS CHAR(4)) + '/'
+ RIGHT(CAST(100+DATEPART(MM,@.DT) AS CHAR(3)),2) + '/'
+ RIGHT(CAST(100+DATEPART(DD,@.DT) AS CHAR(3)),2) + ' '
+ RIGHT(CAST(100+DATEPART(HH,@.DT) + @.var_Frequency AS CHAR(3)),2) + ':'
+ RIGHT(CAST(DATEPART(MI,@.DT) AS CHAR(2)),2) + ':'
+ RIGHT(CAST(DATEPART(SS,@.DT) AS CHAR(2)),2)+ ':'
+ RIGHT(CAST(DATEPART(MS,@.DT) AS CHAR(3)),3) )
Now the problem is that when there a change in day the hours get stuck because it simply adds the hours so if it is 23:40 and it adds 3 it becomes 26:40 which makes no sense. Can someone please help me find a solution to this? I have the same situation with days, and months as well.
Thank you.you should do all data/time manipulations on a date/time datatypes not chars.
if you are extracting a portion of an existing date/time which results in a char datatype, you should cast it back to date/time before you start manipulating it.|||Although this is an old thread I just thought I'd add what I did. I just used the dateadd function to resolve the issue. It worked just perfect.

Problem With DTS Package - To Delay Process

Hello All SQL Experts.

Need your advise on this. I have a DTS package which check for 2 dates and execute tasks when the date do not matched. The problem I am facing now is I could make the next step to start only if the previous step is completed. When the DTS package is executed, all steps being completed almost at the same time. See below / attached DTS package.

In the disgram, I have labelled 5 steps A ~ E, each step needs info from the finished product from previous step to produce correct result in it's own step. I couldn't schedule each step to run at different time because the DTS kicks off based on a file that comes in and each step doesn't have a fixed processing time to complete.

I have tried using On Success or On Complete and both options start the next step immediately not not wait for the job the complete or success. I guess this is because I have transferred the command to external when using command. Is there a way to control by some delay between each task?

Please advise. Thank you.

Each of the step has something like below (refreshing of excel file with macro build in):- I cannot build all macros into one file and run from the main excel.

declare @.MainUpdate datetime
declare @.TempUpdate datetime

select @.MainUpdate=Main_Update_CET from APMEAPV_Compare
select @.TempUpdate=Temp_Update_CET from APMEAPV_Compare
--select @.MainUpdate, @.TempUpdate

if @.MainUpdate<>@.TempUpdate
begin
DECLARE @.commandK varchar(1000)
SET @.commandK='Start Excel.exe "D:\Daily_Status_Report_EDWH\EDWH_Runbook_BTS.xls"'
exec master..xp_cmdshell @.commandK, No_Output

ENDIf you don't have the ability to crate a JOB on the server that will run steps
and create each step as a different DTS or executable command.

You can do a couple of things technically.

Put a step in the DTS to loop a number 36000 = 1 second I think.

Or You could use ACCESS (UGH) to create a virtual DTS and control each step from there You would need to make each step in the DTS a seperate DTS to do this.
opackage.LoadFromSQLServer Server, , , DTSSQLStgFlag_UseTrustedConnection, "", "", "", DTSPackage, 0

opackage.Execute

opackage.UnInitialize

Set opackage = Nothing

I would ask what are you trying to accomplish and is this the best tool to do this.

Hope this helps.|||Hello rbackmann.

Thank you for your advise. I have introduced a count in each step to delay the start and they worked ok for me.

Thank you.

problem with dates in parameter

Report query uses @.startDate and @.endDate for current date and tomorrow.
For currentdate I use =Today and that seems ok but if the report is run at
3PM the time shows 3PM rather than 00:00:00.
EndDate is more problematic. I've tried many different suggestions from
within this forum for obtaining tomorrow's date, including
=DateTime.Now.AddHours(24) and =DateTime.Now.AddDay(1) but cannot get it
right.
Can someone please help correct the syntax? Thanks!Do you need to use the full date and time, or is it sufficient to use date?
If you only need the date, you could get your dates by doing this sql query:
select convert(varchar(10), getdate(), 104) as today, convert(varchar(10),
dateadd("d", 1, getdate()), 104) as tomorrow
Just switch 104 to get the right date format.
Kaisa M. Lindahl Lervik
"Brian L" <BrianL@.discussions.microsoft.com> wrote in message
news:5D023CCF-CCB6-4A2B-960E-74091184774F@.microsoft.com...
> Report query uses @.startDate and @.endDate for current date and tomorrow.
> For currentdate I use =Today and that seems ok but if the report is run at
> 3PM the time shows 3PM rather than 00:00:00.
> EndDate is more problematic. I've tried many different suggestions from
> within this forum for obtaining tomorrow's date, including
> =DateTime.Now.AddHours(24) and =DateTime.Now.AddDay(1) but cannot get it
> right.
> Can someone please help correct the syntax? Thanks!

Problem with dates in Dataset Select statement

I'm using the designer to create/modify strongly typed datasets. I have one select statement that I'm having considerable trouble with. The user selects search parameters from a form. These search parameters are used to filter the data sent to a gridview control. Three of these parameters are almost enough to make me abandon the dataset in this case.

The first two are minimum and maximum age. I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated. In ASP I'd do something like this:

strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")"

I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.

The third parameter is astrological sign. Again, I calculate it based on the birth date. I don't have a field for it. I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign. I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify?

I need to get the age issue fixed. The sign is a nice to have, since I can do it another way if I have to.

BTW: I did decide to abandon the dataset in favor of a SqlDataSource control. This allowed me to build my select string in the code behind the way I would have in ASP. This resulted in paging and sorting not working properly. Sorting would be nice, paging is necessary. I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn). Any comments or feedback on this would be avidly read and appreciated.

Diane

You have to put the Datediff portion in the Filter column of the Query Designer. Then SQL will use it in the WHERE clause.|||

Please show us your code. That would be easier for us to figure out why it does not owrk for you.

Also, please take a look the following link, it shows how to use SqlDataSource with parameters.

http://quickstarts.asp.net/QuickStartv20/aspnet/doc/ctrlref/data/sqldatasource.aspx

Here is an example from the above link:

<asp:DropDownList ID="DropDownList1" ... runat="server"/>
...
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:Pubs %>"
SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @.state">
<SelectParameters>
<asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>

Hope this helps

|||

Yes, I tried that. I get an error, so I must be doing something wrong. I tried

(@.lowAge < DATEDIFF(yyyy, [DATE OF BIRTH], date))

And I get an error: 'date' in expression not part of the query

Diane

|||

I can't use the SqlDataSource control this way. I have a huge select statement and many of the parameters can be empty. For example. users can select to search by state or not. So in my code behind I check if a state has been selected. If so, I add it to the select string. I have to check first, or I'll have a string that looks for records with no state in them, which is not what I want. So I build the select string in the code behind. If strSQL is the string I've built, I set SqlDataSource1.SelectCommand = strSQL. This works great for the first page, but the grid disappears if I try to do any paging. Google and forum searches turned up others with the same problem, but no answers. I suspect there's an answer for this, but that it's much more complicated than using the dataset which I'd rather use anyway.

I'd hate to say it, but at this point, faster is better. I'm at the end of a long project. My customer would like this done so he can move on to the next phase, and I'm ready for something new.

Diane

|||

I've narrowed down my problem.If I want to do this: (DATEDIFF(yyyy, [DATE OF BIRTH], Date) <= @.rUpperAge), how do I specify the current date in the dataset designer? It doesn't recognize Date, Date() Now, Now() or DateTime.now. I even tried to make today's date a parameter (DATEDIFF(yyyy, [DATE OF BIRTH], @.thisDate) <= @.rUpperAge). Didn't accept it. So how do I specify that I'm looking for a date difference between the field value and today?

Diane

|||

I never used the query designer. So I cannot give you any suggestion on that.

Regarding get the today's date, since it is used to build the query, so you shoudl use the sql function: GetDate().

|||

I second that

You should use getdate() to get the current date since you are generating sql query.

Now() is a VB keyword and would be used within VB code.

|||

Thanks everyone, GetDate() as part of the select string did it. I had tried to use it this way:

strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], '" & GetDate() & "') <= " & Request.Form("UpperAge") & ")"

But this worked:

strSQL = strSQL & " AND (DateDiff(yyyy, [DATE OF BIRTH], GetDate()) <= " & Request.Form("UpperAge") & ")"

Diane

Problem with dates format passed as parameters...

Hi,

I am having the "classical problem" of the forums; A local date changed to the american format (normally in the development environment) which I call "Switching months with days"... well it switches both again if you press "view report" again :)

The problem I'm having is that when I Navigate from one report to other, the dates get switched...
Everything's been set in the locale uk date format (dd/mm/aaaa), in the operating system, in the database and in the reports... and the reports are working properly through the web interface.. the only thing that is not working properly is the Navigation which switches the date month with the day...

Changing all the Reporting Services server, database server to the american format date is not an acceptable solution.

Thanks for any guidance on this, as I'm pretty lost...
Jose

where are you switching the dates.

may be create and set a workflag and set it to 0 or 1

based if its 1 dont do anything if its 0 switch the dates to american format. so even if the user clicks the view report again, it will check for 1 and does nothing.

Thanks

Kishore.

|||Hi Raja,

Thanks for your help, I'll try to explain what you ask:
I have a report with two parameters :
BeginningDate|||Try using string instead of datetime parameter, formatted as yyyyMMdd.

This is something like Convert(varchar(8),mydate,112)

Here is a good link with examples in SQL.
http://databases.aspfaq.com/database/what-are-the-valid-styles-for-converting-datetime-to-string.html

In Reporting Services you would use the Format function. eg. Format(MyDate, "yyyyMMdd")

cheers,
Andrew|||Hi!

That would work if the report wasn't called directly from the user, then, with that approach we are unable to present him the data selector, so he will have to write down the date this way... that is not acceptable by my client...

If the report was accessible only through the Navigation Link your approach would work wonderfully...

The point is how to make dates work right in other languages in SQL Server 2005 Reporting Services.... we are at the 2nd Service pack and it still doesn't work properly.. sigh...

Have to say that I have tried everything here, changing the user that executes the reporting services service, the database language, the system locale, the web.config locale, the report, table and all is set to spain (spain) and still it twists the date when we pass it as a paremeter

"13/02/2006" to "02/13/2006" , giving the properly errors, though...

Well, any help on this?
Thanks!!!
Jose

Problem with Dates and OpenXML command

My data is being updated correctly for all fields except a date field.
The program is crashing when a date is encountered with OpenXML. Is
there a way to convert the date within SQL to handle this...I have tried
the CONVERT statement below, but it does little
UPDATE EducationSheetSchool
SET
EducationSheetSchool.Address= XMLEducationSheetSchool.Address,
EducationSheetSchool.[Name]= XMLEducationSheetSchool.[Name],
EducationSheetSchool.LEA= XMLEducationSheetSchool.LEA,
EducationSheetSchool.Tel= XMLEducationSheetSchool.Tel,
EducationSheetSchool.Email= XMLEducationSheetSchool.Email,
EducationSheetSchool.Start=CONVERT(varchar(30),XML EducationSheetSchool.S
tart, 126),
*** Sent via Developersdex http://www.codecomments.com ***
Can you show a sample of the XML and the OPENXML syntax you're using?
Adam Machanic
Pro SQL Server 2005, available now
www.apress.com/book/bookDisplay.html?bID=457
"Paul Barnett" <barnettpaul@.gmail.com> wrote in message
news:eCtAwfM4FHA.1188@.TK2MSFTNGP12.phx.gbl...
>
> My data is being updated correctly for all fields except a date field.
> The program is crashing when a date is encountered with OpenXML. Is
> there a way to convert the date within SQL to handle this...I have tried
> the CONVERT statement below, but it does little
>
> UPDATE EducationSheetSchool
> SET
> EducationSheetSchool.Address= XMLEducationSheetSchool.Address,
> EducationSheetSchool.[Name]= XMLEducationSheetSchool.[Name],
> EducationSheetSchool.LEA= XMLEducationSheetSchool.LEA,
> EducationSheetSchool.Tel= XMLEducationSheetSchool.Tel,
> EducationSheetSchool.Email= XMLEducationSheetSchool.Email,
> EducationSheetSchool.Start=CONVERT(varchar(30),XML EducationSheetSchool.S
> tart, 126),
> *** Sent via Developersdex http://www.codecomments.com ***

Problem with dates

I am having a problem inserting dates into SQL server 2000. I have an aspx page which contains a simple form.

This form data is inserted into the database on submission. On submission to SQL Server the date field is changed to 01/01/1900

This is the concatenated INSERT statement which is generated:

INSERT INTO Applicant (appTitle, appFname, appSname, appDOB, appEmail, appTel, appTelB, appAdd1, appAdd2, appAdd3, appAdd4, appPostcode, appPassword) VALUES ('Mr', 'John', 'Smith', 01/04/78, 'j.smith@.hello.com', '01258 698523', '', '1 The Smithy', 'Old Town', 'Oldville', '', 'OS6 8IU', 'Smith3969')

I have tried wrapping the date in ' ', but this doesn't work either.

Any help would be greatly appreciated

ThanksIt will not work without the quotes. Absent that, you are inserting 1/4 (0.25) /78 (a really small number).

I woulduse parameters instead, which isolates you from the strangeness with handling dates otherwise.|||Thank you for this.

I have almost got it working now using parameters. However I have noticed that dates submitted in the format DD/MM/YYYY or DD/MM/YY are being flipped in SQL Server to the incorrect format MM/DD/YYYY.

This is creating incorrect dates and in some cases is generating the following error:
"Arithmetic overflow error converting expression to data type datetime. The statement has been terminated."

Any ideas?|||Inside SQL Server, the dates (if DateTime type) are binary types, without any formatting applied to them. You can explicitly format the datetime retrieved from SQL Server to a useful representation using DateTime.ToString() (one of the overloads allows a format string that specifies the display format).|||Thats ok, so I can format them correctly in my application.

But what is causing the error:
"Arithmetic overflow error converting expression to data type datetime. The statement has been terminated."

It only occurs on some dates and not on others. This is really confusing me. I may have to resort to storing the dates as text, and then perform any calculations I require by changing them back to dates in my application.

If you have some ideas on this they would be greatfully received.

Thanks

Charlie|||You are likely sending a date to SQL Server (as a string) in a format it does not expect (DD/MM/YYYY rather than MM/DD/YYYY).

Show the code around when the error is occuring. On SQL Server, I would use SQL Profiler and see what is being sent to SQL Server.

Problem with dates

My dilema would probably be nothing to some of you guys but I'm still new to tsql. I have a bunch of info that I need to have ordered by date. I have tried the following:

ORDER BY Due_Date DESC

I have my dates returned in the format : dd/mm/yyyy

But it only orders the days not months or years.

heres the output:

28/02/2004
20/02/2004
19/02/2004
10/12/2003 // as you can see this one should be at the top
05/02/2004
05/02/2004
05/02/2004
13/02/2004
05/02/2004
01/02/2004

Any help would be well appreciatedIs your date field a datetime or a varchar? If a varchar, this is the reason you should not store dates as a varchar. You can try:

ORDER BY CONVERT(datetime,Due_Date) DESC

This presumes your SQL Server is set up to properly parse dates in that format.|||All of my fields are datetime.|||28/02/2004
20/02/2004
19/02/2004
10/12/2003 // as you can see this one should be at the top
05/02/2004
05/02/2004
05/02/2004
13/02/2004
05/02/2004
01/02/2004

... that's all out of order if you think it's the order by.

it's not even order bying anything.

Are you doing a group by?|||There is another post on this issue where more information was provided.view post 466200

Please do not cross-post!

Let's drop this post and concentrate our efforts on the other one since it provides a fuller picture of the problem.

Terri

Problem with dates

Hello,

I am trying to insert some date values into a table. I am using ODBC
prepared statements to (potentially) improve performance. The
statement syntax I am using is this:

INSERT INTO date_tests (date_col) VALUES ( CONVERT(DATETIME, ?, 121) )

The table is defined as "CREATE TABLE date_tests (date_col DATETIME)".
Then, I call this prepared statement in a loop to insert some date
values from an array. The values in the array look like this:
"1994-05-14 11:00:00:000" They are all valid dates and conform to the
ODBC date style supported by Sqlserver.

But I get the following error: [Microsoft][ODBC SQL Server
Driver]Optional feature not implemented.

I have tried different date formats and date values; but each time I
get the same error message. What is interesting is that if I use
straight "INSERT INTO " statements and not use a prepared statement,
there is no problem.

Will appreciate any suggestions and ideas to resolve this.Hi

Using the style parameter in the CONVERT function when converting to
datatime is not a documented option.

From BOL:
style

Is the style of date format used to convert datetime or smalldatetime data
to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data
types), or the string format when converting float, real, money, or
smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or
nvarchar data types).

John

"php newbie" <newtophp2000@.yahoo.com> wrote in message
news:124f428e.0407121442.70ceb5ae@.posting.google.c om...
> Hello,
> I am trying to insert some date values into a table. I am using ODBC
> prepared statements to (potentially) improve performance. The
> statement syntax I am using is this:
> INSERT INTO date_tests (date_col) VALUES ( CONVERT(DATETIME, ?, 121) )
> The table is defined as "CREATE TABLE date_tests (date_col DATETIME)".
> Then, I call this prepared statement in a loop to insert some date
> values from an array. The values in the array look like this:
> "1994-05-14 11:00:00:000" They are all valid dates and conform to the
> ODBC date style supported by Sqlserver.
> But I get the following error: [Microsoft][ODBC SQL Server
> Driver]Optional feature not implemented.
> I have tried different date formats and date values; but each time I
> get the same error message. What is interesting is that if I use
> straight "INSERT INTO " statements and not use a prepared statement,
> there is no problem.
> Will appreciate any suggestions and ideas to resolve this.

Problem with dates

The 3rd party database I have been "gifted" stores dates as varchar(10)
formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
To ease use I am trying to write a function that will join the two
fields and return it as a single smalldatetime field.
The gist of the function is below
[code]
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
DECLARE @.l_str_datevarchar(19)
SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
@.i_str_time
SELECT @.l_dte_return = @.l_str_date
SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
print @.l_dte_return
[/code]
This function always returns the return value as "Aug 9 2005 10:05AM"
with SQL if I do a print. No matter what I specify in the final
convert statement. If I put this into the function and pull it back it
returns "2005-09-08 10:05:00"
Any suggestions please.
Thanks
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print.
Print returns a string. Someone in the SQL Server dev team (as MS or more probably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datetime data, where it is
the client that make it into a readable format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123754445.406625.316790@.g44g2000cwa.googlegr oups.com...
> The 3rd party database I have been "gifted" stores dates as varchar(10)
> formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
> To ease use I am trying to write a function that will join the two
> fields and return it as a single smalldatetime field.
> The gist of the function is below
> [code]
> DECLARE @.i_str_date varchar(10)
> DECLARE @.i_str_time varchar(8)
> SET @.i_str_date = '2005-09-08'
> SET @.i_str_time = '10:04:42'
> DECLARE @.l_dte_return smalldatetime
> DECLARE @.l_str_date varchar(19)
> SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
> SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
> @.i_str_time
> SELECT @.l_dte_return = @.l_str_date
> SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
> print @.l_dte_return
> [/code]
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print. No matter what I specify in the final
> convert statement. If I put this into the function and pull it back it
> returns "2005-09-08 10:05:00"
> Any suggestions please.
> Thanks
>
|||The code looks OK to me. PRINT performs an implict conversion to a
string using the default date conversion format - what matters is that
your function returns the correct datetime value. The reason that your
result is rounded to the nearest minute is because that's the precision
supported by SMALLDATETIME. If you need seconds then make the output
DATETIME and change the CONVERT function to DATETIME also.
David Portas
SQL Server MVP
|||That explains the seconds rounding up thanks.
but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
format I have specified which should be dd/mm/yyyy hh:mm:ss
Any ideas?
|||It returns datetime, which doesn't have a format. Datetime is formatted by the client application.
See http://www.karaszi.com/SQLServer/info_datetime.asp for more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123766631.480840.219740@.g47g2000cwa.googlegr oups.com...
> That explains the seconds rounding up thanks.
> but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
> format I have specified which should be dd/mm/yyyy hh:mm:ss
> Any ideas?
>
|||cheers very useful
|||On 11 Aug 2005 03:00:45 -0700, graz79 wrote:
Hi graz79,
Your actual question has already been asnwered, I believe. But...
(snip)
>Any suggestions please.
>Thanks
Ask, and you shall be given...

>The gist of the function is below
>[code]
>DECLARE @.i_str_date varchar(10)
>DECLARE @.i_str_time varchar(8)
>SET @.i_str_date = '2005-09-08'
>SET @.i_str_time = '10:04:42'
>DECLARE @.l_dte_return smalldatetime
>DECLARE @.l_str_datevarchar(19)
>SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
>SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
>@.i_str_time
This loads the date+time in the varchar column, but in this format:
"dd/mm/yyyy hh:mm:ss". That format is not safe for conversions.
Americans will think that your date is September 8th.

>SELECT @.l_dte_return = @.l_str_date
Here, you are doing an implicit conversion from varchar to
smalldatetime. It is completely dependent on the localization settings
what the result will be.

>SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
This converts from smalldatetime to smalldatetime. Too late to supply a
style parameter 103 now - if the conversion went wrong in the previous
statement, it won't be corrected here.

>To ease use I am trying to write a function that will join the two
>fields and return it as a single smalldatetime field.
User-defined functions can be slow. There's absolutely no need for a UDF
in this case. Since the date is already in the "yyyy-mm-dd" format and
the time is in the "hh:mm:ss" format, it's very easy to get to one of
the guaranteed safe and unambiguous formats: "yyyy-mm-ddThh:mm:ss":
-- Use variables to demonstrate the technique
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
-- This is where the actual work is done
SET @.l_dte_return = @.i_str_date + 'T' + @.i_str_time
-- Show results
PRINT @.l_dte_return
Instead of making a user-defined function and calling that, simply pop
the actual formula for the conversion "column1 + 'T' + column2" where
you need it in the query.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with dates

The 3rd party database I have been "gifted" stores dates as varchar(10)
formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
To ease use I am trying to write a function that will join the two
fields and return it as a single smalldatetime field.
The gist of the function is below
[code]
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
DECLARE @.l_str_date varchar(19)
SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
@.i_str_time
SELECT @.l_dte_return = @.l_str_date
SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
print @.l_dte_return
[/code]
This function always returns the return value as "Aug 9 2005 10:05AM"
with SQL if I do a print. No matter what I specify in the final
convert statement. If I put this into the function and pull it back it
returns "2005-09-08 10:05:00"
Any suggestions please.
Thanks> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print.
Print returns a string. Someone in the SQL Server dev team (as MS or more probably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datetime data, where it is
the client that make it into a readable format.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123754445.406625.316790@.g44g2000cwa.googlegroups.com...
> The 3rd party database I have been "gifted" stores dates as varchar(10)
> formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
> To ease use I am trying to write a function that will join the two
> fields and return it as a single smalldatetime field.
> The gist of the function is below
> [code]
> DECLARE @.i_str_date varchar(10)
> DECLARE @.i_str_time varchar(8)
> SET @.i_str_date = '2005-09-08'
> SET @.i_str_time = '10:04:42'
> DECLARE @.l_dte_return smalldatetime
> DECLARE @.l_str_date varchar(19)
> SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
> SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
> @.i_str_time
> SELECT @.l_dte_return = @.l_str_date
> SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
> print @.l_dte_return
> [/code]
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print. No matter what I specify in the final
> convert statement. If I put this into the function and pull it back it
> returns "2005-09-08 10:05:00"
> Any suggestions please.
> Thanks
>|||The code looks OK to me. PRINT performs an implict conversion to a
string using the default date conversion format - what matters is that
your function returns the correct datetime value. The reason that your
result is rounded to the nearest minute is because that's the precision
supported by SMALLDATETIME. If you need seconds then make the output
DATETIME and change the CONVERT function to DATETIME also.
--
David Portas
SQL Server MVP
--|||That explains the seconds rounding up thanks.
but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
format I have specified which should be dd/mm/yyyy hh:mm:ss
Any ideas?|||It returns datetime, which doesn't have a format. Datetime is formatted by the client application.
See http://www.karaszi.com/SQLServer/info_datetime.asp for more information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123766631.480840.219740@.g47g2000cwa.googlegroups.com...
> That explains the seconds rounding up thanks.
> but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
> format I have specified which should be dd/mm/yyyy hh:mm:ss
> Any ideas?
>|||cheers very useful|||On 11 Aug 2005 03:00:45 -0700, graz79 wrote:
Hi graz79,
Your actual question has already been asnwered, I believe. But...
(snip)
>Any suggestions please.
>Thanks
Ask, and you shall be given...
>The gist of the function is below
>[code]
>DECLARE @.i_str_date varchar(10)
>DECLARE @.i_str_time varchar(8)
>SET @.i_str_date = '2005-09-08'
>SET @.i_str_time = '10:04:42'
>DECLARE @.l_dte_return smalldatetime
>DECLARE @.l_str_date varchar(19)
>SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
>SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
>@.i_str_time
This loads the date+time in the varchar column, but in this format:
"dd/mm/yyyy hh:mm:ss". That format is not safe for conversions.
Americans will think that your date is September 8th.
>SELECT @.l_dte_return = @.l_str_date
Here, you are doing an implicit conversion from varchar to
smalldatetime. It is completely dependent on the localization settings
what the result will be.
>SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
This converts from smalldatetime to smalldatetime. Too late to supply a
style parameter 103 now - if the conversion went wrong in the previous
statement, it won't be corrected here.
>To ease use I am trying to write a function that will join the two
>fields and return it as a single smalldatetime field.
User-defined functions can be slow. There's absolutely no need for a UDF
in this case. Since the date is already in the "yyyy-mm-dd" format and
the time is in the "hh:mm:ss" format, it's very easy to get to one of
the guaranteed safe and unambiguous formats: "yyyy-mm-ddThh:mm:ss":
-- Use variables to demonstrate the technique
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
-- This is where the actual work is done
SET @.l_dte_return = @.i_str_date + 'T' + @.i_str_time
-- Show results
PRINT @.l_dte_return
Instead of making a user-defined function and calling that, simply pop
the actual formula for the conversion "column1 + 'T' + column2" where
you need it in the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with dates

The 3rd party database I have been "gifted" stores dates as varchar(10)
formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
To ease use I am trying to write a function that will join the two
fields and return it as a single smalldatetime field.
The gist of the function is below
[code]
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
DECLARE @.l_str_date varchar(19)
SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
@.i_str_time
SELECT @.l_dte_return = @.l_str_date
SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
print @.l_dte_return
[/code]
This function always returns the return value as "Aug 9 2005 10:05AM"
with SQL if I do a print. No matter what I specify in the final
convert statement. If I put this into the function and pull it back it
returns "2005-09-08 10:05:00"
Any suggestions please.
Thanks> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print.
Print returns a string. Someone in the SQL Server dev team (as MS or more pr
obably Sybase) has
decided to format a datetime string in that way. SELECT, OROH, returns datet
ime data, where it is
the client that make it into a readable format.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123754445.406625.316790@.g44g2000cwa.googlegroups.com...
> The 3rd party database I have been "gifted" stores dates as varchar(10)
> formated yyyy-mm-dd and times as varchar(8) hh:mm:ss.
> To ease use I am trying to write a function that will join the two
> fields and return it as a single smalldatetime field.
> The gist of the function is below
> [code]
> DECLARE @.i_str_date varchar(10)
> DECLARE @.i_str_time varchar(8)
> SET @.i_str_date = '2005-09-08'
> SET @.i_str_time = '10:04:42'
> DECLARE @.l_dte_return smalldatetime
> DECLARE @.l_str_date varchar(19)
> SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
> SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
> @.i_str_time
> SELECT @.l_dte_return = @.l_str_date
> SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
> print @.l_dte_return
> [/code]
> This function always returns the return value as "Aug 9 2005 10:05AM"
> with SQL if I do a print. No matter what I specify in the final
> convert statement. If I put this into the function and pull it back it
> returns "2005-09-08 10:05:00"
> Any suggestions please.
> Thanks
>|||The code looks OK to me. PRINT performs an implict conversion to a
string using the default date conversion format - what matters is that
your function returns the correct datetime value. The reason that your
result is rounded to the nearest minute is because that's the precision
supported by SMALLDATETIME. If you need seconds then make the output
DATETIME and change the CONVERT function to DATETIME also.
David Portas
SQL Server MVP
--|||That explains the seconds rounding up thanks.
but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
format I have specified which should be dd/mm/yyyy hh:mm:ss
Any ideas?|||It returns datetime, which doesn't have a format. Datetime is formatted by t
he client application.
See http://www.karaszi.com/SQLServer/info_datetime.asp for more information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"graz79" <graz79@.yahoo.co.uk> wrote in message
news:1123766631.480840.219740@.g47g2000cwa.googlegroups.com...
> That explains the seconds rounding up thanks.
> but it is still returning the date as YYYY-MM-DD hh:mm:ss and not
> format I have specified which should be dd/mm/yyyy hh:mm:ss
> Any ideas?
>|||cheers very useful|||On 11 Aug 2005 03:00:45 -0700, graz79 wrote:
Hi graz79,
Your actual question has already been asnwered, I believe. But...
(snip)
>Any suggestions please.
>Thanks
Ask, and you shall be given...

>The gist of the function is below
>[code]
>DECLARE @.i_str_date varchar(10)
>DECLARE @.i_str_time varchar(8)
>SET @.i_str_date = '2005-09-08'
>SET @.i_str_time = '10:04:42'
>DECLARE @.l_dte_return smalldatetime
>DECLARE @.l_str_date varchar(19)
>SELECT @.l_str_date = SUBSTRING(@.i_str_date,9,2) + '/' +
>SUBSTRING(@.i_str_date,6,2) + '/' + SUBSTRING(@.i_str_date,1,4) + ' ' +
>@.i_str_time
This loads the date+time in the varchar column, but in this format:
"dd/mm/yyyy hh:mm:ss". That format is not safe for conversions.
Americans will think that your date is September 8th.

>SELECT @.l_dte_return = @.l_str_date
Here, you are doing an implicit conversion from varchar to
smalldatetime. It is completely dependent on the localization settings
what the result will be.

>SELECT @.l_dte_return = CONVERT(smalldatetime, @.l_dte_return, 103)
This converts from smalldatetime to smalldatetime. Too late to supply a
style parameter 103 now - if the conversion went wrong in the previous
statement, it won't be corrected here.

>To ease use I am trying to write a function that will join the two
>fields and return it as a single smalldatetime field.
User-defined functions can be slow. There's absolutely no need for a UDF
in this case. Since the date is already in the "yyyy-mm-dd" format and
the time is in the "hh:mm:ss" format, it's very easy to get to one of
the guaranteed safe and unambiguous formats: "yyyy-mm-ddThh:mm:ss":
-- Use variables to demonstrate the technique
DECLARE @.i_str_date varchar(10)
DECLARE @.i_str_time varchar(8)
SET @.i_str_date = '2005-09-08'
SET @.i_str_time = '10:04:42'
DECLARE @.l_dte_return smalldatetime
-- This is where the actual work is done
SET @.l_dte_return = @.i_str_date + 'T' + @.i_str_time
-- Show results
PRINT @.l_dte_return
Instead of making a user-defined function and calling that, simply pop
the actual formula for the conversion "column1 + 'T' + column2" where
you need it in the query.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Problem with Date Output in Excel 2007

When we export data mining output including dates through Reporting Services to an Excel spreadsheet, Excel 2007 subtracts 4 years from the dates. Example, the date 11/16/2006 appears in Excel 2007 as 11/16/2002. How should this be handled? Workaround, bug fix, patch,etc.? Thanks, Sam

Sounds very weird to me.

The data renders correctly in Reporting Services, but incorrectly when exported?

Does this only happen with reports built over DMX queries? Do other reports, using SQL or MDX, render correctly in Reporting Services and in Excel?

thanks for any additional clarification you can give.

|||

Thanks, Don. The dates appear correct in the output through Reporting Services that we use as a Front End. When we export to Excel, it comes into Excel 2007 as 4 years earlier. Are the dates in Excel 2007 based on 1900 rather than 1904, and could that be the source of the problem? I will check with the developers on Tuesday with the answer to your other questions about MDX, etc. and will experiment with my SQL queries to see how Excel 2007 handles them so we can figure out if it is an Analysis Services export problem or an Excel 2007 problem.

Sam

|||

Don, Excel 2007 has no trouble bringing in dates from Excel 2003 spreadsheets or from the text output of SQL queries.

I posed your question to our developer. His reply:

Exporting to Excel or any other form is a built-in feature of Microsoft reporting services.I guess it has nothing to do with the way data is generated for the report.Moreover reports are accessing data from SQL server tables and not from the MDX/DMX.

The sequence is data is pumped from analysis server to SQL server table by using the DMX queries .Reports access data from these tables.

I hope this is helpful information--it would be surprising if no one else is having this difficulty witht the built-in export from Microsoft reporting services. Does this provide you with sufficient information to attempt to replicate the problem?

Sam

|||

Thanks. Let's see if we can narrow it down further. (We cannot reproduce the problem here, so I'm sorry but we have to ask you quite a few of these questions to get to the root of the matter.)

Are the results of the DMX queries correct when stored in SQL Server? If yes, and the results are correct when viewed in the report, then I would conclude that the problem is in the Reporting Services Excel export functionality. In that case, we would move this thread to the reporting services forum. (We have already alerted them to issue.) It would not be related to the data mining functionality.

However, if the results stored in the SQL Server are wrong, then it may well be an issue somewhere with the DMX query and we could look into that further.

hth

Problem with date dimension

One of my tables contains the transaction dates. So based on this table
I tried to create a Time dimension (using the first option - I think
it's called single table or something in the Wizard of Analysis
Manager)...
I select everything else correctly and selected the Year, Quarter, Month
option and finally saved he dimension.
However when I browse the dimension, when I do the first click I get a
level with '0' as the year and blank child nodes for quarter and
months...
Any clues?
I'll appreciate help.
Thanks.
Hi,
first of all, my suggestion is do not use AS Wizard for creating Time
dimension.
Read a very good article that Tom Chester wrote about Time dimensions.
http://www.sqljunkies.com/Article/D1...D60951395.scuk
Hope it helps!
Peace,
Andrej
"Learner" <wantnospam@.email.com> wrote in message
news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
> One of my tables contains the transaction dates. So based on this table
> I tried to create a Time dimension (using the first option - I think
> it's called single table or something in the Wizard of Analysis
> Manager)...
>
> I select everything else correctly and selected the Year, Quarter, Month
> option and finally saved he dimension.
> However when I browse the dimension, when I do the first click I get a
> level with '0' as the year and blank child nodes for quarter and
> months...
> Any clues?
> I'll appreciate help.
> Thanks.
|||Great Article!!! Thanks
Regards.

> Hi,
> first of all, my suggestion is do not use AS Wizard for creating Time
> dimension.
> Read a very good article that Tom Chester wrote about Time dimensions.
> http://www.sqljunkies.com/Article/D1...D60951395.scuk
>
> Hope it helps!
> Peace,
> Andrej
>
> "Learner" <wantnospam@.email.com> wrote in message
> news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
>
>

Problem with date dimension

One of my tables contains the transaction dates. So based on this table
I tried to create a Time dimension (using the first option - I think
it's called single table or something in the Wizard of Analysis
Manager)...
I select everything else correctly and selected the Year, Quarter, Month
option and finally saved he dimension.
However when I browse the dimension, when I do the first click I get a
level with '0' as the year and blank child nodes for quarter and
months...
Any clues?
I'll appreciate help.
Thanks.Hi,
first of all, my suggestion is do not use AS Wizard for creating Time
dimension.
Read a very good article that Tom Chester wrote about Time dimensions.
http://www.sqljunkies.com/Article/D...0D60951395.scuk
Hope it helps!
Peace,
Andrej
"Learner" <wantnospam@.email.com> wrote in message
news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
> One of my tables contains the transaction dates. So based on this table
> I tried to create a Time dimension (using the first option - I think
> it's called single table or something in the Wizard of Analysis
> Manager)...
>
> I select everything else correctly and selected the Year, Quarter, Month
> option and finally saved he dimension.
> However when I browse the dimension, when I do the first click I get a
> level with '0' as the year and blank child nodes for quarter and
> months...
> Any clues?
> I'll appreciate help.
> Thanks.|||Great Article!!! Thanks
Regards.

> Hi,
> first of all, my suggestion is do not use AS Wizard for creating Time
> dimension.
> Read a very good article that Tom Chester wrote about Time dimensions.
> [url]http://www.sqljunkies.com/Article/D1E44392-592C-40DB-B80D-F20D60951395.scuk[/url
]
>
> Hope it helps!
> Peace,
> Andrej
>
> "Learner" <wantnospam@.email.com> wrote in message
> news:O8FbRMZFEHA.624@.TK2MSFTNGP10.phx.gbl...
>
>