date range query not returning anticipated results

Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
Query:
select startdate
from auditlog
where startdate between '6/18/2004' and '6/20/2004'
I get results like this, with dates outside the specified range:
6/18/2004
6/18/2004
6/2/2004
6/19/2003
6/19/2003
6/2/2004
6/2/2004
6/2/2004
Any suggestions?
Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Any difference if you do it this way?
select startdate
from auditlog
where startdate between '20040618' and '20040620'
Andrew J. Kelly SQL MVP
"Yayahim" <Yayahim@.discussions.microsoft.com> wrote in message
news:C9A1AD19-40FE-42CE-BAC1-C69353F5F458@.microsoft.com...
> Newbie question. When I query a field (datetime datatype) the results
appear as if it were looking at text. There is no time data stored in the
field.
> Query:
> select startdate
> from auditlog
> where startdate between '6/18/2004' and '6/20/2004'
> I get results like this, with dates outside the specified range:
> 6/18/2004
> 6/18/2004
> 6/2/2004
> 6/19/2003
> 6/19/2003
> 6/2/2004
> 6/2/2004
> 6/2/2004
> Any suggestions?
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>
|||Are you using SQL Server 7? I don't think SQL Server 2000 will do this,
but in any case, try
where startdate between cast('20040618' as datetime) and cast('20040620'
as datetime)
Steve Kass
Drew University
Yayahim wrote:

>Newbie question. When I query a field (datetime datatype) the results appear as if it were looking at text. There is no time data stored in the field.
>Query:
>select startdate
>from auditlog
>where startdate between '6/18/2004' and '6/20/2004'
>I get results like this, with dates outside the specified range:
>6/18/2004
>6/18/2004
>6/2/2004
>6/19/2003
>6/19/2003
>6/2/2004
>6/2/2004
>6/2/2004
>Any suggestions?
>

Date parameters with annoying and unwanted Timestamp.

I have two date parameters which will be used as data range in my
report.
But when I enter same date in both date parameter text boxes it appends
the timestamp (12:00:00 AM).
Example: When I enter 07/24/2004 and 07/24/2004 it converts that into
07/24/2004 12:00:00 AM and 07/24/2004 12:00:00 AM.
If I'm generating a report with those parameters I shouldn't get any
data back if I can't take care of that datetime in my stored proc. But
for how many end users do I need to explain and ask them for not to
worry about it when it shows in the front end?
Isn't it really annoying?
Is there anyway to get rid of 12:00:00 AM?The only way is to change the report parameter from date to string.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RK" <am_i_dotnet@.hotmail.com> wrote in message
news:1122317815.803912.144810@.o13g2000cwo.googlegroups.com...
>I have two date parameters which will be used as data range in my
> report.
> But when I enter same date in both date parameter text boxes it appends
> the timestamp (12:00:00 AM).
> Example: When I enter 07/24/2004 and 07/24/2004 it converts that into
> 07/24/2004 12:00:00 AM and 07/24/2004 12:00:00 AM.
> If I'm generating a report with those parameters I shouldn't get any
> data back if I can't take care of that datetime in my stored proc. But
> for how many end users do I need to explain and ask them for not to
> worry about it when it shows in the front end?
> Isn't it really annoying?
> Is there anyway to get rid of 12:00:00 AM?
>

Date Parameter and text box question

I have a report that runs transcripts for people. I have a date parameter setup along with last and first name. The date parameter field is used to specify what year (ex. 1/1/2006 - 12/31/2006) to pull the information for.

Now..I have a text box at the bottom (Footer) of my report that says something to the affect of "Official transcripts for 2006". There will be times when I have to run the report using a different year other then "2006" in the date parameter field. How do I have the text at the bottom change to reflect the year I'm reporting on? If I use dates say in 2004 I need the text at the bottom to reflect that year and not 2006. Can this be accomplished?

Thx,

Bill

Sounds like you need to parse the parameter value (Parameters!ParameterName.Value) to get to the year portion. You can add a code-behind VB.NET function to help with this as demonstrated at the beginning of this article.|||

="Official transcripts for 2006" & Parameters!YourDateParameterName.Value

Try this. . .

date insert problem

hi...

my form has a text box which displays system date.

i am inserting date into MS SQL Server from this date textbox.

but it displays me error..

String was not recognized as a valid DateTime.

Line 154: myCommand1.ExecuteNonQuery()

i have written code as

myCommand1.Parameters.Add(New SqlParameter("@.date", SqlDbType.DateTime, 8))

myCommand1.Parameters("@.date").Value = FormatDateTime(datetxt.Text, DateFormat.GeneralDate)

and also tried to change date format with many other ways.

how should i solve this problem?

i also want to take time form a user with the help of web form and want to store it in other field called 'timein' and 'timeout'....

FormatDateTime returns a string. Use DateTime.Parse instead to create a valid datetime, and assign the datetime object parsed. Alternatively, you can use Convert.ToDateTime, but you get an exception for invalid dates. Tie your textbox in to a CompareValidator to ensure valid dates from the browser. I recommend turning off client validation for date validation as it does not validate medium date (e.g. 22-Apr-2007) correctly|||

Heh, who the hell ever types 22-Apr-2007? Personally, I wouldn't consider it valid input to begin with.

Since you have already stated that your sqldbtype is a datetime, just shove the string into the parameter.

EX:

cmd.parameter("@.MyDateTime).value=MyDateString

That of course assumes that you have already validated it as a valid date/time string format. Just make sure that you validate it both client and server side. Too many people I know drop the validators down on the client, then forget to wrap their "save" in a If page.IsValid(), and it works... Until a hacker comes along and posts some invalid data to the server.

Oh, and I would drop off the size parameter on the sqlparameter. Not sure if 8 is even the correct size for a datetime, but it's ignored anyway since datetime is a fixed size.

|||

The reason I suggest the use of medium date format is this. I've workd for a number of world-wide companies over many years, often on large projects. Hundreds of thousands of pounds have been wasted by developers and databases engineers, simply because of a US/UK date confusion, or forgetting to set the date locale to UK, then, some months later, finding a database full of data that is invalid, then having to fix it, and having systems off-line.

Medium date also still works in the local language, if you set the locale of a web site (e.g. Norway).

Developers should also be listening to business, who increasing, and in my view, rightly, want medium format. The whole of the rest of the world does not live in the US, that is why our web sites should be locale aware

Software development is also a team effort. It isn't down to the developer alone to ensure a web-site isn't hacked. Architects, Managers and Testers are all there to make it happen (safely).

As always, these are my opinions and suggestions, soI don't expect everyone to like or agree with them.

Happy coding all...

|||

That's all well and good, and I agree that applications should be built with globalization in mind, but that really has nothing to do with medium dates. We don't use them in the US, and they aren't used anywhere else in the world except for geeky tech documents. If you want to use a truly international standard, then use the ISO format (YYYY-MM-DD). It's the same format in every culture.

As for having incorrect dates in your database, that's why you should always use the datetime datatype. The value is the value no matter what culture you are in, infact I normally store all date/times in a database based on UTC. That way no matter where you are in the world, I can tell you what time and date a specific event occurred localized in your specific date format, and give you the time relative to your timezone.

Now, it may be in your company, that they have decided that medium format is best, but I can say that is an oddity, and not a rule. There is a standard date format, it's the ISO format that was approved by the international standards organization, and when people need a format that they don't want translated to their native culture's format, that is the one that should be used. After all, 01-Apr-2006 isn't correct for any other place but the US. They don't have "April" let alone "Apr" in other countries.

|||

More business documents in the UK are using Medium and even Long Dates than previously. You don't see YYYY-MM-DD used in the UK as most people would find it geeky (whatever that means). In my software I generally let the user override their locale anyway, and set a date format for the web site based on ISO8601, ISO, UK/US, etc.

Medium and Long dates do correctly translate into the local language. E.g. I just tried today's (long format - since Med format displays the same result) date on a simple web page, which returned 12-febrero-2006 when I changed the culture from UK English to international Spanish (es-ES).

BTW., It wasn't my database or even my company that had the incorrect dates. I spent 15 years contracting around the UK, working for various major companies. What I observed (and sometimes got asked for advice on) were issues where UK formatted dates (parsed as text using dd/mm/yy) were stored in a database that was assuming US format since the default language had never been changed. Eventually the systems broke, which gave rise to many issues and fixing. What I advised is this. If they had used medium format from the start, the issue would not have arose, because the text would have been corretcly converted into the underlying datetime type.

I am sure we can disagree long into the night about date formats. However, it's a minor issue, and you are correct to raise the point that storing dates in the underlying format is the correct way to go.

Date help

Hi -
I have a column in a table that is supposed to be a date, it's data type is
text since it's source is a text file. Which looks like this:
Chg_Date
19990105
20000323
20010526
00000000
00000000
as you can see, there are some dates with 0's which of course, are invalid,
I need to insert this date field in another table even if the date is all
0's, because there is not always a chg_date associated with a record.
Here is what I have tried since I know the 0's are invalid:
select chg_date
from table
where ISDATE(chg_date ) <> 0
and of course, I only get the records that are not 0's - but how do I get
all of the dates whether they are valid or not?Patrice,
Try using a reference date for the 0's. In SQL Server, integer values can be
converted to datetime data type, so 0 (zero) will be converted to 01/01/1900
(19000101).
insert into t2(c1)
select case when isdate(c1) then c1 else '19000101' end
from t1
go
AMB
"Patrice" wrote:

> Hi -
> I have a column in a table that is supposed to be a date, it's data type i
s
> text since it's source is a text file. Which looks like this:
> Chg_Date
> 19990105
> 20000323
> 20010526
> 00000000
> 00000000
> as you can see, there are some dates with 0's which of course, are invalid
,
> I need to insert this date field in another table even if the date is all
> 0's, because there is not always a chg_date associated with a record.
> Here is what I have tried since I know the 0's are invalid:
> select chg_date
> from table
> where ISDATE(chg_date ) <> 0
> and of course, I only get the records that are not 0's - but how do I get
> all of the dates whether they are valid or not?|||You want to make the distinction in your select clause, not your where
clause. That was you are still getting the rows. Putting it in the where
clause is for filtering rows. Manipulate your select clause to work with
columns.
SELECT CASE WHEN ISDATE(chg_date) <> 0 THEN chg_date ELSE NULL END
FROM [table]
HTH,
John Scragg
"Patrice" wrote:

> Hi -
> I have a column in a table that is supposed to be a date, it's data type i
s
> text since it's source is a text file. Which looks like this:
> Chg_Date
> 19990105
> 20000323
> 20010526
> 00000000
> 00000000
> as you can see, there are some dates with 0's which of course, are invalid
,
> I need to insert this date field in another table even if the date is all
> 0's, because there is not always a chg_date associated with a record.
> Here is what I have tried since I know the 0's are invalid:
> select chg_date
> from table
> where ISDATE(chg_date ) <> 0
> and of course, I only get the records that are not 0's - but how do I get
> all of the dates whether they are valid or not?|||Correction:
insert into t2(c1)
select case when isdate(c1) = 1 then c1 else '19000101' end
from t1
go
AMB
"Alejandro Mesa" wrote:
> Patrice,
> Try using a reference date for the 0's. In SQL Server, integer values can
be
> converted to datetime data type, so 0 (zero) will be converted to 01/01/19
00
> (19000101).
> insert into t2(c1)
> select case when isdate(c1) then c1 else '19000101' end
> from t1
> go
>
> AMB
> "Patrice" wrote:
>

Date formatting issue

I've Googled and MSDNed to no avail. All I want is to display, in a
concatenated expression with other text, say today's date as 2008-03-14,
regardless of the Windows local settings (I want to hard code the date
format). If all I had in a textbox were a date, I would use the Format
property and set it to yyyy-MM-dd. But I want an VB.NET function that will
take a date value as a parameter (and I suppose a second parameter like
"yyyy-MM-dd") and return 2008-03-14. Pretty simple, huh?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comFormat(Date,"yyyy-mm-dd")

Date Formatting

How can I format the current date as yyyy-mm-dd and display it in a text box on the report. The FormatDateTime function only allows certain NamedFormat for the short and long dates but does not satisfy the above requirements. Any ideas?

=Format(Globals!ExecutionTime,"yyyy/MM/dd")

|||Thanks, it worked!

Date Format Conversions in SSIS

Hi

I am quite new to SSIS and have been given the task of importing some data from a text file into the database. The data contains dates which are in the American format of mm/dd/yyyy. I need them in the datadase in the format of dd mon yy.

I realise I could load it and do a SQL task to convert once it is in the database but ideally i would like this data transformed before it is loaded into the tables.

any suggestions will be gratefully recieved.

Best regards

It's just string manipulation. Add a derived column transformation, and manipulate the input string (using SUBSTRING, etc.) into a new column, casting it as the appropriate type.

Greg.

Date format Conversion

First of all, I'm fairly new to SQL Server 2000, so please be patient and explicit.

I have a text file that I'm going to import using DTS on a scheduled interval. The text file has three different date fields that are all formatted as:

YYYYMMDD example: 20031004

I need to get this data formated as:

MM/DD/YYYY, example 10/04/2003

DTS does not do this. If I set the field type to DATETIME it gives errors and will not import the data.

HELP!

Thanks,
Troy D. YoungHi, Set up a dts transform data task.
the go to th etransformation tab and remove the transformations that

cover datetime fileds.
Make suer yoiu have highlighted both the source and destination fields.
Add a new transform of type date.
Modify the date time formates and hit preview.
thats should do it.

regards, brian|||Originally posted by contiguous1
Hi, Set up a dts transform data task.
the go to th etransformation tab and remove the transformations that

cover datetime fileds.
Make suer yoiu have highlighted both the source and destination fields.
Add a new transform of type date.
Modify the date time formates and hit preview.
thats should do it.

regards, brian

It took some time, but I figured it out. For some reason it only lets me do one datetime field at a time in a transformation. I had to create a transformation for each datetime field in the table.

Thanks,
Troy D. Young

Date Format accepting MDY instead of DMY

I have a unbound text box that gets its date from the system (date.now).

The problem is that when I write it to SQL (via a SQL Insert Command) It throws an error.

It transpires that the format is wrong. It accepts the date such as 07/17/2007 just fine but not 17/07/2007 which is automatically generated.

My IIS has a locale setting with is correct for the UK.

How can I change SQL 2005 so that it accepts DMY for a date/time field

Thanks.

Hmmm,

I actually fixed this already. As my date box was to be updated by the system clock and not the user I thought the easiest thing would be to format the date in US format before submission.

I used this VB code:

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load

'Create a var. named rightNow and set it to the current date/time

Dim rightNowAs DateTime =Date.Now

Dim sAsString'create a string

s = rightNow.ToString("MM/dd/yyyy hh:mm:ss")

LogDate.Text = s

EndSub

That has done the trick.

But would like to know how to change SQL anyway.

|||

http://msdn2.microsoft.com/en-us/library/aa259188(sql.80).aspx

http://msdn2.microsoft.com/en-us/library/ms174398.aspx -- probably better

If you search hard enough, there are a couple of ways of specifying it in your connection string as well. I believe (but could be wrong) that it's like:

Persist Security Info=False;Trusted_Connection=True;database=AdventureWorks;server=(local);Language=British

Date Conversion Problem

I am importing a text file into a SQL table, using DTS. My problem is concerning the date fields. The source fields are in the yyyymmdd format. I have tried using datetime transformation, using yyyyMMdd as the source format, and MM/dd/yyyy as the destination formation. If there is a valid date, this works fine. However, many of the dates are either null or contain spaces, and the DTS will not handle them. Any suggestions as to how to handle this?Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?|||Originally posted by Brett Kaiser
Is the column defined as NOT NULL?

DTS the table a stage table with all of the columns a varchar...

Then manipulate it with sql and do an insert?

-----------

The Allow Nulls option is turned on in the table definition. If I manually add a record in Enterprise Manager, it will accept nulls. It's just the DTS that doesn't like them.

I can use an intermediary table if that's the only way. I was just hoping that it could be done during the initial import.

Thanks for your suggestions.|||Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?|||Originally posted by Brett Kaiser
Sounds like a fixed width file...

Actually I' m suprised it's not working...

Where's the file coming from?

Mainframe?

Got any unprintable chars there?

Yes, it is a mainframe file, with fixed width fields. There are no unprintable characters. It's just that some of the date fields are either null or contain spaces (I'm not sure which), and DTS keeps choking on them.|||Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?|||Originally posted by Brett Kaiser
Can you post the transformation code?

You do know that putting the code in the package like that slows everything down..

You're much better off getting all the data in, then using set based methods to transform the data...

much, much fatser...

Ever use bcp?

No, I haven't used bcp before. I'll check it out.

In addition to choosing datetime transformation and setting the formats, I've also tried using an ActiveX script. Here is the ActiveX code I've tried for the transformation:

Function Main()
If Not IsNull(DTSSource("Col010")) AND LEN(TRIM(DTSSource ("Col010"))) > 0 Then (Checking for null or spaces)
DTSDestination("AWARD_DATE") = MID(DTSSource("Col010"),7,2)&"-"&MID(DTSSource("Col010"),5,2)&"-"&LEFT(DTSSource("Col010"),4)
Main = DTSTransformStat_OK
End If
End Function

The error returned is: Invalid procedure call or argument - DTSSource|||Just wondering is your System a AS400 cause i had also ran into this before.|||Originally posted by hillcat
Just wondering is your System a AS400 cause i had also ran into this before.

No, PC with Windows XP Pro & SQL Server 2000|||but is the the mainframe file a rpg file|||Originally posted by hillcat
but is the the mainframe file a rpg file

I'm not familiar with rpg; all I know is, the file is a text file from a mainframe, with fixed width fields. I was given a printout of the file layout to indicate starting and ending point of the fields.|||well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.|||Originally posted by hillcat
well Is not null function will not work thats for sure since this is a unprintable caracter and this caracter as a value. if this unprintable caracter is at the begining of a string try to trim the first caracter from the string.

I'm not sure whether it is null or spaces; that's why I used both the the 'not isnull' and the 'trim', so that I'd be covered either way. If either is not true (value is null, or value is spaces), then the statements inside the if clause should be bypassed|||My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0|||Originally posted by Brett Kaiser
My guess here is that if you DTS a column that has space and no transformation, it'll put in null..

But because of the transformation, I guess it thinks there should be a valid value, and then fails.

The other thing is that it might not be space, but other data that doesn't transform to a valid date.

Use a stage table and do some analysis.

Soemthing like

SELECT * FROM myStage99 WHERE ISDATE(yourDateCol) = 0

I'll give it a try. Much thanks . . .|||Like:

USE Northwind
GO

CREATE TABLE myTable99(Col1 varchar(8))
GO

INSERT INTO myTable99(Col1)
SELECT 'yyyymmdd' UNION ALL
SELECT '20040317' UNION ALL
SELECT ' '
GO

-- Show me Valid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=1

-- Show me InValid Dates
SELECT * FROM myTable99 WHERE ISDATE(Col1)=0

--Move to it's Final Destination

CREATE TABLE myTable00(Col1 datetime)
GO

INSERT INTO myTable00(Col1)
SELECT Col1 FROM myTable99 WHERE ISDATE(Col1)=1

SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like “0001-01-01”. Before I import directly my AS400 tables into Access without problems because Access read those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value “0001-01-01” to “1901-01-01” and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like “0001-01-01”. I could not do modification to the AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
Montreal
JP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison
|||Thanks,
but not all the record have this value. When I import with package, I could go around it. But I need to be live, so, from my SQL Server, I link into the AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it said “conversion failure
.
Regards,
Jean-Paul
|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date Conversion

I am having trouble with a simple date conversion in a table. I have
a field that is stored in a legacy system as a text value. When I
bring the data into reporting services I need to convert it to a date,
however, some records have invalid date formats, which cannot be
converted. I tried to solve the problem using the following
expression, but I still get the dreaded "#Error" in the fields that
cannot be converted to date.
=IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
ABACCD.Value),nothing)Perhaps you could use regular expressions such as:
System.Text.RegularExpressions.Regex.IsMatch(StartDate,
"(((0[1-9]|[1-9])|1[012])[- /.]((0[1-9]|[1-9])|[12][0-9]|3[01])[-
/.](19|20)\d\d)|((January|February|March|April|May|June|July|August|September|October|November|December)(\s)((0[1-9]|[1-9])|[12][0-9]|3[01])(,\s)(19|20)\d\d)|((Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)(\s)((0[1-9]|[1-9])|[12][0-9]|3[01])(,\s)(19|20)\d\d)")
This returns false if the format is incorrect
"repettry@.fcbinc.com" wrote:
> I am having trouble with a simple date conversion in a table. I have
> a field that is stored in a legacy system as a text value. When I
> bring the data into reporting services I need to convert it to a date,
> however, some records have invalid date formats, which cannot be
> converted. I tried to solve the problem using the following
> expression, but I still get the dreaded "#Error" in the fields that
> cannot be converted to date.
> =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> ABACCD.Value),nothing)
>|||On Apr 11, 10:32 pm, repet...@.fcbinc.com wrote:
> I am having trouble with a simple date conversion in a table. I have
> a field that is stored in a legacy system as a text value. When I
> bring the data into reporting services I need to convert it to a date,
> however, some records have invalid date formats, which cannot be
> converted. I tried to solve the problem using the following
> expression, but I still get the dreaded "#Error" in the fields that
> cannot be converted to date.
> =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> ABACCD.Value),nothing)
You're probably going to need to use custom code to do what you're
attempting to do with the IIF. Since IIF is a function call, the
entire statement is evaluated at run time so it's throwing the error
simply because it's seeing the invalid date value in the statement.
Something like this should work:
Public function ConvertDate (exp1)
If IsDate(exp1) = False Then
ConvertDate = Nothing
Else ConvertDate = CDate(exp1)
End If
End function
Then insert
=code.ConvertDate(Fields!AVACCD.Value)
in the appropriate text box.|||On Apr 13, 1:53 pm, "toolman" <t...@.infocision.com> wrote:
> On Apr 11, 10:32 pm, repet...@.fcbinc.com wrote:
> > I am having trouble with a simple date conversion in a table. I have
> > a field that is stored in a legacy system as a text value. When I
> > bring the data into reporting services I need to convert it to a date,
> > however, some records have invalid date formats, which cannot be
> > converted. I tried to solve the problem using the following
> > expression, but I still get the dreaded "#Error" in the fields that
> > cannot be converted to date.
> > =IIF((Isdate(CDate(Fields!AVACCD.Value)))=True,CDate(Fields!
> > ABACCD.Value),nothing)
> You're probably going to need to use custom code to do what you're
> attempting to do with the IIF. Since IIF is a function call, the
> entire statement is evaluated at run time so it's throwing the error
> simply because it's seeing the invalid date value in the statement.
> Something like this should work:
> Public function ConvertDate (exp1)
> If IsDate(exp1) = False Then
> ConvertDate = Nothing
> Else ConvertDate = CDate(exp1)
> End If
> End function
> Then insert
> =code.ConvertDate(Fields!AVACCD.Value)
> in the appropriate text box.
Thanks. The custom code worked great! I used the same concept to
convert numbers as well.

Date Conversion

Hi,

Please help me on this conversion. I am using DTS to import data from text file to SQL Server 2000. I have these Date fields in the textfile

Date1 yyyymmdd
Date2 yyyymm

What corresponding data type should I define in SQL server. Datetime and smalldate does not work.

ThanksHowdy

The problem you have ( and I assume your data is text in a text file ) is that datetime expects a certain format for the data.

If you were importing date data in format '2003-09-30 14:00:00.000'
( including the single quotes ) all would work well. I use UK date format. If you are in the US its '2003-30-09 14:00:00.000'

Date format in BOL is not documented well, sadly.

So, may need to alter your text data as part of the DTS package ( not easy, and time consuming), or alternatively , import the data straight into a new table using DTS ( easier ), then modify it to insert the " - " etc to make it the correct format then copy it into another table if needed.

Let me know if I have interpreted your problem correctly.

Cheers,

SG.|||i would not alter the text file, rather, i would DTS it into a table where the datatype of the date fields is char(8) and char(6)

once you have the data loaded, you can then use SELECT INTO syntax to create your "final" table

e.g. if you've loaded yyyymmdd data into fieldx and yyyymm into fieldy, then you'd say

select
cast( left(fieldx,4)
+'-'+substring(fieldx,5,2)
+'-'+substring(fieldx,7,2) as datetime ) as fieldxdate
, cast( left(fieldy,4)
+'-'+substring(fieldy,5,2)
+'-01' as datetime ) as fieldydate
, ...
into newtable
from loadedtable

edit: cut & paste typo
rudy
http://r937.com/|||the temporary table idea works for sure, I have implemented that before. Plus you can use the Date Time String conversion in the Transformation tab. Where the source would be in yyyyMMdd format and the destination would be any of your desired formats.

Hope this helps.

Date conversion

Im trying to save the contents of a text box into a date column in SQL server 2000. Youd think this would be straighforward, its not !

My date is formatted as dd/mm/yyyy, SQL server expects mm/dd/yyyy

how can I convert to this ? formatting it as a string still gives me an error when I convert back to a date.

My insert query uses a parameter of type DBDate, no matter what I try im getting a conversion error if dates exceed 12 in the dd part as SQL is expecting US format.

Can I convert to UTC ?? Ive tried several ways to convert the format, but they always give me a date conversion errorTry format it as yyyy/mm/dd

Cheers
Ken|||had to write a function to split the date up and swap the month and day around. Also had to change the parameter type in the insert SQL from date to char and let SQL convert it for me. Not the best way I know, but it works.|||::had to write a function to split the date up and swap the month and day around

Gosh :-) What about having it as a Date and using the formatting functions provided by .NET? Mving dates around as strings is wrong, as is using string manipulation to get a formatted output.

BTW - There is a culture independant form that SQL Server accepts REGARDLESS OF SETTINGS.

We use the following code in our EntityBroker O/R mapper to provide a country invariant date:

public virtual string EncodeDateTime (object Value) {
return String.Format ("'{0:yyyy-MM-dd HH:mm:ss}'", Value);
}

yyyy-MM-dd is the country invairant form. You will find this a little more elegant than yxour current appraoch. Note that the code also teaches you how to actually use String.Format to format a DateTime into a specific form.|||Thanks for your help, I tried your suggestion but got the following error

Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error converting datetime from character string.

Source Error:

Line 426://try
Line 427: //{
Line 428: cmd.ExecuteNonQuery();
Line 429: //Message.InnerHtml = "Record Added<br>" + insertCmd.ToString();
Line 430://conn.Close();

this was the call to your procedure

String TicketDate = EncodeDateTime (DateInsertTextBox.Text);

public virtual string EncodeDateTime (object Value)
{
return String.Format ("'{0:yyyy-MM-dd HH:mm:ss}'", Value);
}

DateInsertTextBox contains a string in the format dd/mm/yyyy

This was the problem Ive been getting all along, I just cant seem to reformat the string in the textbox.

Please excuse my ignorance, Im very new to C# and am just learning the syntax.|||You may try the following code:

Imports System.Globalization

'set as Global variable
Dim GBformat As New CultureInfo("en-GB", True)

'set as local variable
Dim TestDate As Date
Dim StrDate As String = "29/11/2003"

TestDate = DateTime.Parse(StrDate, GBformat).ToShortDate

And then you use the "TestDate" as your parameter assignment. Good Luck!!!

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with a package to a text file, there are dates like â'0001-01-01â'. Before I import directly my AS400 tables into Access without problems because Access read those date as â'1901-01-01â'. Now we have to move to a SQL server. If I try to import with SQL Package it crash unless I first change the fields in my SQL table to varChar(15), do the importation, change the value â'0001-01-01â' to â'1901-01-01â' and then alter the data type back to smalldatetime. I have a lot of tables with numerous date fields. I try to link the AS400 source to my SQL Server, I still could not read tables that have dates like â'0001-01-01â'. I could not do modification to the AS400 source. Is there a way to solve that problem?
Thanks.
Jean-Paul
MontrealJP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date conversion

Hi all,
In my AS400 source I have tables with date fields, if I import a table with
a package to a text file, there are dates like “0001-01-01”. Before I i
mport directly my AS400 tables into Access without problems because Access r
ead those date as “1901-0
1-01”. Now we have to move to a SQL server. If I try to import with SQL Pa
ckage it crash unless I first change the fields in my SQL table to varChar(1
5), do the importation, change the value “0001-01-01” to “1901-01-01
and then alter the data t
ype back to smalldatetime. I have a lot of tables with numerous date fields.
I try to link the AS400 source to my SQL Server, I still could not read tab
les that have dates like “0001-01-01”. I could not do modification to th
e AS400 source. Is there a
way to solve that problem?
Thanks.
Jean-Paul
MontrealJP,
have a look at
select convert (datetime, '2' + substring('0001-01-01',2,20),20)
I have had to remove the first zero and replace it with a 2 for this to
work.
Regards,
Paul Ibison|||Thanks,
but not all the record have this value. When I import with package, I could
go around it. But I need to be live, so, from my SQL Server, I link into the
AS/400. If I try to open a AS/400 table that has “0001-01-01” date, it
said “conversion failure
.
Regards,
Jean-Paul|||JP,
how about trying openquery and use as/400 syntax to convert to a valid sql
datetime format? I don't know AS400 syntax for this, but I'm hoping you
might :-)
HTH,
Paul Ibison

Date and text fields in 'Measures' Area

Hi,

I have a fact table like this:

Account No. CreatedDate CreatedBy Amount

1 1/1/2007 serene 1000

2 2/2/2007 mike 2000

.

.

What I want to see in my Cube Browser is an Account on the row side and CreatedDate, CreatedBy and Amount on the 'Measures' Area. Is it possible?

cherriesh

You would not want to setup the CreatedDate and CreatedBy fields as measures, these fields are not aggregateable and really only relate to the Account dimension. What you would want to do is to set them up as attributes in the Account dimension. You could disable them if you did not want explicit analysis done on these fields and only wanted them accessed as member properties.

You might also be able to setup the CreatedDate as a referenced dimension, if you wanted to analyze the creation dates at different levels (like years/quarters/months etc) - if you already have a Date dimension you might be able to reuse it.

|||

Hi,

how does the first scenario work? i should make my fact table as dimension also? how to disable the attribute? thanks a lot for the help!

cherriesh

|||

I'm assuming that you would have some sort of Account dimension table with fields like

AccountId

AccountNo

CreatedBy

CreatedDate

And then a fact table with the dimension keys and the measures

AccountId

DateId

Amount

I'm not sure what your "Amount" is relating to in this example, but if it was a transaction at a point in time then you would not want to be storing the repeated CreatedBy and CreatedDate fields each time a transaction occurred.|||

It's like this...

I have a header table, say, ServiceJob, with the following structure:

ServiceJob ReceivedDate CollectedDate

1 1/1/2007 1/3/2007

2 2/2/3007 2/3/2007

And I have a Service Job Item table:

ServiceJob ServiceJobLineNumber Item Labor Cost Others Cost

1 1 A 1000 200

2 B 700 50

2 1 A 300 900

I want to have this look in my Cube Browser:

Service Job | ReceivedDate CollectedDate LaborCost Others Cost

1 | 1/1/2007 1/3/2007 1700 250

2 | 2/2/3007 2/3/2007 300 900

Service Job is on row side while received date, collected date labor cost and others cost are in the measure side.

can i do that?

cherriesh

|||

You can have multiple attributes on the row axis, so your result set would look like the following, with 3 attributes in the row axis and 2 measures.

Service Job ReceivedDate CollectedDate | LaborCost Others Cost

1 1/1/2007 1/3/2007 | 1700 250

2 2/2/3007 2/3/2007 | 300 900

ReceivedDate and CollectedDate should probably either be dimensions in their own right or attributes of the ServiceJob. Dates don't really "work" well as measures as you cannot aggregate them.