db error: values

Showing posts with label values. Show all posts

Tuesday, March 27, 2012

dayly table update

hello,
i must dayly update a table in my database with the values of a CSV file
(~300000 entries)
example of the tabel (artNr ,productname ,price )
000001 monitor 234,66
000003 pc 699,44
....
245433 router 126,33
Now dayly the table-content is deleted and the csv-file is imported
Is it possible a better way - to update only the modified values and insert
the new.
How can this be done?
thanksOne recommendation could be
1. Create a staging table called get_bcp_h_daily_csv
2. Truncate the table
3. DTS the csv file into staging table
4. Write the first entry to a surrogate table called ot_su_daily_csv as in
a) below.
5. Write a sProc that incrementally loads what's in the surrogate table into
a lookup table called ot_lu_daily_csv for your database as in b) below:
6. Schedule a job to run this DTS Each day
7. Sorted
a)
INSERT INTO ot_su_daily_csv (ColName1, ColName2)
SELECT ColName1, ColName2
FROM get_bcp_h_daily_csv BCP
WHERE NOT EXISTS ( SELECT * FROM ot_su_daily_csv SURR
WHERE SURR.Col1= BCP.Col1 )
b.)
INSERT INTO ot_lu_daily_csv
(Col1, Col2)
SELECT Col1, Col2
FROM ot_su_daily_csv SURR(nolock)
ORDER BY Col1|||thanks for the recommendation - it works well if only each day new values in
the csv-file are attached.
But in my csv file some colums of the articles are changed - like in the
example
example: - day1
000001 monitor 234,66
000003 pc 699,44
the next day - day 2
000001 monitor 230,03 (price is modified...)
000003 pc-3,4GHz 699,44 (product description is modified)
245433 router 126,33 -> ok will be detected and updated
....
how to make a correct update in this situation ...
thanks
Xavier|||On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:

>thanks for the recommendation - it works well if only each day new values i
n
>the csv-file are attached.
>But in my csv file some colums of the articles are changed - like in the
>example
>example: - day1
>000001 monitor 234,66
>000003 pc 699,44
>the next day - day 2
>000001 monitor 230,03 (price is modified...)
>000003 pc-3,4GHz 699,44 (product description is modified)
>245433 router 126,33 -> ok will be detected and updated
>....
>how to make a correct update in this situation ...
>thanks
>Xavier
Hi Xavier,
Load the new data in a staging table. Then run a procedure that updates
existing data and adds new data, as follows:
UPDATE t
SET Descr = s.Descr,
Price = s.Price,
.. (other columns)
FROM TheTable AS t
INNER JOIN StagingTable AS s
ON s.KeyColumn = theTable.keyColumn
WHERE t.Descr <> s.Descr
OR t.Price <> s.Price
OR ... (other columns)
INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
SELECT KeyColumn, Descr, Price, ... (other columns)
FROM Stagins AS s
WHERE NOT EXISTS
(SELECT *
FROM TheTable AS t
WHERE t.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks,
Xavier
"Hugo Kornelis" wrote:

> On Sun, 6 Nov 2005 07:14:50 -0800, Xavier wrote:
>
> Hi Xavier,
> Load the new data in a staging table. Then run a procedure that updates
> existing data and adds new data, as follows:
> UPDATE t
> SET Descr = s.Descr,
> Price = s.Price,
> ... (other columns)
> FROM TheTable AS t
> INNER JOIN StagingTable AS s
> ON s.KeyColumn = theTable.keyColumn
> WHERE t.Descr <> s.Descr
> OR t.Price <> s.Price
> OR ... (other columns)
> INSERT INTO TheTable (KeyColumn, Descr, Price, ... (other columns))
> SELECT KeyColumn, Descr, Price, ... (other columns)
> FROM Stagins AS s
> WHERE NOT EXISTS
> (SELECT *
> FROM TheTable AS t
> WHERE t.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

Thursday, March 22, 2012

datetime vs varchar

If I insert datatime values in a varchar datatype as opposed to datetime,
what am I losing out on ?
Would I be able to perform the same functions against a varchar datatype as
opposed to datetime such as datepart, datediff,etc. ?Hi Hassan
Why don't you try it and see? Something like this should give you a start:
declare @.today varchar(30)
select @.today = getdate()
select @.today
select dateadd(mm, 1, @.today)
And then read Tibor's excellent article:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype
> as opposed to datetime such as datepart, datediff,etc. ?
>
>|||Kalen,
Does not seem to be any difference with that example.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hi Hassan
> Why don't you try it and see? Something like this should give you a start:
> declare @.today varchar(30)
> select @.today = getdate()
> select @.today
> select dateadd(mm, 1, @.today)
> And then read Tibor's excellent article:
> http://www.karaszi.com/sqlserver/info_datetime.asp
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Hassan" <hassan@.test.com> wrote in message
> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime,
>> what am I losing out on ?
>> Would I be able to perform the same functions against a varchar datatype
>> as opposed to datetime such as datepart, datediff,etc. ?
>>
>|||Downsides of using varchar include:
There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
Performing various datetime calculation might mean bad performance, since you might end up with a
convert on the column side in your predicate.
You need to decide on a format. If you want to retrieve the value and present it in a different
format from what it is stored with you have more work to do.
...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
> Kalen,
> Does not seem to be any difference with that example.
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to datetime
>> such as datepart, datediff,etc. ?
>>
>>
>|||Hassan would have found all that out if he read the article I pointed him
to.
;-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Downsides of using varchar include:
> There's nothing stopping you from inserting invalid date (like Feb 30), or
> time values.
> Performing various datetime calculation might mean bad performance, since
> you might end up with a convert on the column side in your predicate.
> You need to decide on a format. If you want to retrieve the value and
> present it in a different format from what it is stored with you have more
> work to do.
> ...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Hassan" <hassan@.test.com> wrote in message
> news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a
>> start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message
>> news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to
>> datetime, what am I losing out on ?
>> Would I be able to perform the same functions against a varchar
>> datatype as opposed to datetime such as datepart, datediff,etc. ?
>>
>>
>>
>|||On Feb 11, 6:46=A0am, "Hassan" <has...@.test.com> wrote:
> If I insert datatime values in a varchar datatype as opposed to datetime,
> what am I losing out on ?
> Would I be able to perform the same functions against a varchar datatype a=s
> opposed to datetime such as datepart, datediff,etc. ?
Nothing but you would end up with too much convertions if you want to
manipulate varchars
(ex order by, usage of datediff, dateadd,etc)
Always use proper DATETIME datatype to store dates and let your front
end application do the formation|||> Hassan would have found all that out if he read the article I pointed him to.
Ah, that ol' article ;-)
Thanks Kalen :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OkINH5MbIHA.1376@.TK2MSFTNGP02.phx.gbl...
> Hassan would have found all that out if he read the article I pointed him to.
> ;-)
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uB0OLMIbIHA.1376@.TK2MSFTNGP02.phx.gbl...
>> Downsides of using varchar include:
>> There's nothing stopping you from inserting invalid date (like Feb 30), or time values.
>> Performing various datetime calculation might mean bad performance, since you might end up with a
>> convert on the column side in your predicate.
>> You need to decide on a format. If you want to retrieve the value and present it in a different
>> format from what it is stored with you have more work to do.
>> ...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Hassan" <hassan@.test.com> wrote in message news:evp1jaFbIHA.4180@.TK2MSFTNGP06.phx.gbl...
>> Kalen,
>> Does not seem to be any difference with that example.
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:ODKGXIFbIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> Hi Hassan
>> Why don't you try it and see? Something like this should give you a start:
>> declare @.today varchar(30)
>> select @.today = getdate()
>> select @.today
>> select dateadd(mm, 1, @.today)
>> And then read Tibor's excellent article:
>> http://www.karaszi.com/sqlserver/info_datetime.asp
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Hassan" <hassan@.test.com> wrote in message news:uDdP0$EbIHA.748@.TK2MSFTNGP04.phx.gbl...
>> If I insert datatime values in a varchar datatype as opposed to datetime, what am I losing out
>> on ?
>> Would I be able to perform the same functions against a varchar datatype as opposed to
>> datetime such as datepart, datediff,etc. ?
>>
>>
>>
>>
>

DateTime Values in SQL Express ASPNETDB.MDF

greets again folks,

The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.

The date is usually correct but the time is always hours off.

Is there some way to get the time part of the DateTime to be correct?

Do I have to write code to set the time when the user logs in?

Thanks a mil!

It sounds as if GetUtcDate() is being called instead of GetDate. GetUTCDate records the UTC or GMT date time whereas GetDate() get the local date time.

|||

hypercode:
greets again folks,
The values LastLoginDate and LastActivityDate in my SQl Express membership dBase are always off.
The date is usually correct but the time is always hours off.
Is there some way to get the time part of the DateTime to be correct?
Do I have to write code to set the time when the user logs in?
Thanks a mil!

check database coumn type ... is it set to DateTime ....

|||

Kamrul,

Although unlikely, the column does not have to be a DateTime to have GetDate assigned to it. E.g.SELECTCONVERT(VARCHAR(20),GetDate(), 113) returned "19 Apr 2007 17:07:06". but could have inserted the valud into a CHAR(20) column.

|||

The date is OFF in the ASPNETDB itself.

Beoroe I write any code to retireve the values, they are already in the dBase off to begin with.

Is there some way to tell the dBase to record the correct times?

|||

Look in the table definition, do the collumns have the default property change the GetUtcDate() to GetDate(). Do the same in the stored procedures and all date/time from then will be in local rather than universal time.

Incidentally was the time an exact number of hours off from the server time?

|||

" Look in the table definition, do the collumns have the default property change the GetUtcDate() to GetDate(). Do the same in the stored procedures and all date/time from then will be in local rather than universal time. "

I just looked in the table definition for all of the columns which contain datetime date types. I don't see GetUtcDate or GetDate() anywhere in the table definition. Where should these values be displayed?

|||If the data is not being set by a default property, look in the stored procedures for them.|||

Hi Hypercode,

Actually,the datetime value is saved as UTC format in system or database. When there's a request from a user, the server will translate the time into local time which depends on the server's location and response the user's request. So pls be sure that the settings of the timezone on your server is correct ( or just as you want).

If the problem still exists, you have to translate the time manually.Here's the UDF that you might be interested in looking into

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28712

Hope it helps.

Thanks

|||

Thanks to you guys for pitchin in!

I still didn't get her straightened out yet. Been busy with other stuff (on the same project). I'll be getiing this straightened out though when I get a chance.

datetime to integer

I was just told that it is better to convert all datetime values to integers for performance reasons. Is this generally true? I am working with time series data so datetime values hold important information.Without knowing more my knee-jerk reaction would be to suggest sticking with the native datetime datatype. Can you give some more information describing what you are trying to accomplish?|||We're importing hundreds of thousands of records into hundreds of tables. All of it is time series data. The collected data is then analyzed using an external VB.NET program with dozens of custom analysis functions. The results of these calculations are saved into additional tables which are created on the fly. Some of the data in the resulting tables are reused in the custom functions. Results will eventually be compiled into a data warehouse from which additional analyses will be conducted (SQL Server Analysis Services, Data Mining, and SPSS). I should mention that the datetime values are unique (primary key candidate?); no table can have duplicate datetime values. Also, datetime values are essential in many of the analysis functions. I was told that if datetime values were converted to integers, the SQL Server 2005 performance improvement would more than outweigh the cost of conversion back and forth in the VB.NET functions.|||

Ahh, now we get down to it. When you say date/time is unique in all tables, my knee-jerk reaction is "Really? How are you assuring that" But know I am lead to believe that the source of the data is a data source that supplies more date/time precision than SQL Server records natively.

Key question: To what precision are you maintaining your date/time information?

Key question: How are you assuring that all date/time fields are unique? This is not a common guarantee in SQL Server.

|||

The data comes from several "clean" sources. The datetime field is set as a primary key. During import, checks for duplicate datetime values are made. After import, the data is cleaned again.

Most of the data is in one minute intervals.

When you say uniqueness is not a common guarantee, are you saying that setting a field to primary key or unique does not work properly in SQL Server?

|||If your granularity is finer than millisecond granularity then you will need an alternative to native datetime datatype. The native datetime datatype only tracks to 1/300th of a second.|||Most of the data is at one minute or higher (10 minute, hourly, etc.) levels. The possibility exists of sampling data as fine as one second intervals. Nothing will be done at the millisecond level.|||I think I would stick with native datetime; can we get additional opinions please?|||

If I recall correctly, internally SQL Server stores datetime values as a decimal number comprised of a (left side) four byte integer representing the number of days since Day 0 (Jan 01, 1900) , separating decimal, and a (right side) four byte integer representing the number of milliseconds since midnight.

So while not stored as an Integer, it is stored in the 'next best thing'.

|||Yes, however, that is NOT to the nearest millisecond but to the nearest 1/300 of a second; it is a legacy from Sybase.sql

Wednesday, March 21, 2012

datetime to date

Hi, everybody.
I have a parameter from datetime type.
Its' values are stored with date and time values together in the database.
But I only want to show its' date value to the user.
How can I do this?

You can modify the way a Datetime value is shown by adding the ToString() expression.

Take a look at the DateTime.ToString() reference: http://msdn2.microsoft.com/en-us/library/system.datetime.tostring.aspx

|||Thanks Jan.
But my parameter's value is not shown in the report.
So I can't write an expression or a method to this.( as Datetime.ToString() )
I think I should do this by SQL side.
But when I use a convert or cast function on sql side , there exists a type conflict.
Anyway, if you find a solution, share it with me please.|||

If you are using the datetime values as a parameter, the easiest way to do this is to create or modify your parameter query: add a column with only the date value of the other datetime values.

SELECT Date AS DBDate, CONVERT(VARCHAR(10), Date, 101) AS ViewDate
FROM <<Table>>

This query shows also the date in the format mm/dd/yyyy. Then use this query for the parameter: use the DBDate as Value and the ViewDate as label.

DATETIME question

What is the best way to encode a DATETIME value given three integer values:
Year, Month, Day (I only need precision up to a given day, no time values).

I know i can form a date string, but I am relunctant to use that since it is dependant on the Language settings of the current session.

for example:
SELECT CAST(CAST(2004 AS VARCHAR) + '/' + CAST(1 AS VARCHAR) + '/' + CAST(5 AS VARCHAR) AS DATETIME);

Is January 5, 2004 when the session's language is set toENGLISH/US_ENGLISH, but equals May 1, 2004 when the session's language is set to FRENCH;

Basically I'm looking for something like MakeDate(Year AS INT, Month AS TINYINT, Day AS TINYINT) that returns a DATETIME value.The string '2004-01-05' is always January 5, 2004 and the string '2004-05-01' is always May 1, 2004. The ISO standard date string format is a wonderful thing!

-PatP|||thanks Pat1...I had noticed the ASCII (YYYYMMDD) format always worked...but I didn't like the idea of manually padding the Months/days with a '0' for Months/days in the 1-9 range.....but the '-' gives me the proper separator for years/months/days....to think that any select statement that returns a date does so in the format you mentionned, and I didn't notice....I need some sleep.

thanks again.

Datetime problem

an existing application sends server an sql string like
insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
where c1 is an int, and c2 is a datetime field. This command returns an erro
r.
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an
out-of-range datetime value.
The statement has been terminated.
when I change that command like following
SET DATEFORMAT dmy
insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
it works fine.
I want to set server always accepts dates im dmy format.
What can I do for this.
Thanks in advanceCould you instead pass dates in the following format? It always works:
YYYYMMDD HH:MM:SS
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote in
message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> an existing application sends server an sql string like
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> where c1 is an int, and c2 is a datetime field. This command returns an
error.
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> when I change that command like following
> SET DATEFORMAT dmy
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> it works fine.
> I want to set server always accepts dates im dmy format.
> What can I do for this.
> Thanks in advance|||that way requires application change. Actually there is lots of data in dmy
format. When server changed to SQL 2000, application get following error
message from server. It was work fine with previous version SQL server, but
not SQL 2000
"Narayana Vyas Kondreddi" wrote:

> Could you instead pass dates in the following format? It always works:
> YYYYMMDD HH:MM:SS
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote
in
> message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> error.
>
>|||This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...
datetime.asp, more
specifically rl]
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
http://www.solidqualitylearning.com/
"Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote in
message
news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
> that way requires application change. Actually there is lots of data in dm
y
> format. When server changed to SQL 2000, application get following error
> message from server. It was work fine with previous version SQL server, bu
t
> not SQL 2000
> "Narayana Vyas Kondreddi" wrote:
>|||When I set logins default language by enterpirse manager, it runs normal.
Thanks for help :)
"Tibor Karaszi" wrote:

> This might shine some light on the problem: http://www.karaszi.com/SQLServer/in...o_datetime.asp, more
> specifically /url]
> --
> Tibor Karaszi, SQL Server MVP
> [url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://www.karaszi.com/SQLServer/in...ver/default.asp
> http://www.solidqualitylearning.com/
>
> "Levent Helvacioglu" <LeventHelvacioglu@.discussions.microsoft.com> wrote i
n message
> news:D5CB6259-F990-49CA-A7E1-FE263CFF1335@.microsoft.com...
>|||Create INSTEAD OF trigger on your table and reformat an input in it.
"Levent Helvacioglu" <Levent Helvacioglu@.discussions.microsoft.com> wrote in
message news:813C2E24-BF6A-417C-97A6-567004845256@.microsoft.com...
> an existing application sends server an sql string like
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> where c1 is an int, and c2 is a datetime field. This command returns an
> error.
> Server: Msg 242, Level 16, State 3, Line 1
> The conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value.
> The statement has been terminated.
> when I change that command like following
> SET DATEFORMAT dmy
> insert into dbo.test (c1, c2) values (1, '22.1.2006 10:10:10')
> it works fine.
> I want to set server always accepts dates im dmy format.
> What can I do for this.
> Thanks in advance

Sunday, March 11, 2012

datetime format problem

Is there any standard function for inserting datetime values to an sql table. I'm having a problem because some operating systems are in english and some operating systemes are in spanish.. When I insert a value '2005-02-15 12:00:00' it works on the english operating system, but it doesn't in the spanish one... any ideas?try GETDATE ( )|||I am getting the date/time from a date time picker in visual basic.. it's not the current date.|||Define date time picker. Sounds like you need one or more of the following:

A smarter date time picker that converts all datetimes to a standard format.
To write a routine that adds intelligence to your picker.
Some VB function that does b. for you. The lack of strong-typing in VB may make this very difficult.

SQL Server has this interesting trait of trying to do exactly what you ask it to do. You may also want to read up on Cast/Convert in SQL BOL.|||???

If it is a VB control I would think it would be returning a VB datetime value, which is just a number with no formatting applied.

But if you are converting this to a string and submitting it to SQL Server as a datetime value (which you shouldn't be doing), then this format should be universally recognized by SQL Server:

yyyy-mm-dd hh:mi:ss

...where hh uses a 24 hour clock.|||If it is a VB control I would think it would be returning a VB datetime value, which is just a number with no formatting applied.Excellent point. So, diegocro, why is the value being sent a text string?|||Am I missing something (very possible)? How else can you send a date value from VB to SQL?|||Unlikely. You don't miss much.

But the VB code could be converting or storing the data in any number of odd ways before it is submitted to SQL Server. A lot can happen to data at point B while it is traveling from point A to point C...|||Or you could try CAST or CONVERT to change the text string to a DATE format.

e.g. CONVERT(datetime,'2005-02-15 12:00:00' ,120) (see BOL for more detail)|||you can do something like this

convert(datetime,datepickervalue,101)|||[sniped]
[sniped]
[sniped]
[sniped]
[sniped]|||ok
CONVERT(datetime,'2005-02-15 12:00:00' ,120) works, thanks a lot.

DateTime format during INSERT using MS JDBC driver

Hi,
Using the latest MS JDBC-driver:
The date format of the SQL Server causes me some trouble at the moment:
My INSERT statement uses DateTime values of the format "yyyy-mm-dd
hh:mm:ss.mmm" but they somehow get interpreted as"yyyy-dd-mm" causing a date
format out of range exception.
How can I "force" SQL Server to use my date format? Calling "SET DATEFORMAT
YMD" on each open connection before the statement is executed does not help
but this may be caused by additional connections being opened "behind the
scenes" (so I've been told). I'm not sure this is so even if my connection
pool keeps connections open.
The db-user opening the connection has the correct language setting and the
collation label of the databases is also correct.
So I'm wondering whether it's the regional setting of the Windows account
that runs the SQLServer-service (or SQLSERVERAGENT-Service?) that causes
this? But then - what region would use "yyyy-dd-mm" as it's date format?
Or is there a setting on the JDBC-driver that can modify this behaviour?
- Tim
Hi Tim,
You can either use the parameterized query and pass in a value of a
Java.Sql.Date type. You can thus convert the "yyyy-mm-dd" string to Date.
Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (?)");
st.setDate(1, Date.valueOf("1999-01-30"));
st.executeUpdate();
Or if you choose to hard code the "yyyy-mm-dd" in the query string, you can
use CONVERT function to convert the string to a datetime sql data type
according to the proper style of date format. 120 is the ODBC canonical
style that converts yyyy-mm-dd hh:mi:ss(24h). Below is the code snippet:
PreparedStatement st = connection1.prepareStatement("INSERT
Customers (ArchiveDate) VALUES (CONVERT (datetime, '1999-01-30', 120) )");
st.executeUpdate();
For more info on CONVERT, please refer to the SQL Server Books Online.
Yilei

Thursday, March 8, 2012

datetime datatype conversion

Hello

I have 1 column in table with char datatype that stores datetime values (e.g.: 20061207091510 which translates to 2006-12-07 09:15:10).

Is there a way to convert this string into datetime datatype to preserve the time part (hours:minutes:seconds?

Thanks,

Lena

SELECT CONVERT(DATETIME, LEFT('20061207091510', 8), 112)+CONVERT(DATETIME, SUBSTRING('20061207091510', 9, 2) + ':' + SUBSTRING('20061207091510', 11, 2) + ':' + SUBSTRING('20061207091510', 13, 2), 114)

|||thank you!

Wednesday, March 7, 2012

datetime compare - eastern and pacific

Hi,
I need to compare two datetime values, one is eastern and anther is pacific. Is there an existing function that I can turn a pacific time to eastern one? or I have to write it by myself?
Thanks,
Liliyou might try using the dateadd function to manipulate the time by +2 hours.|||It works! Thank you.

In case someone wanna know, I used:
SET @.NEWSTART = DATEADD(HH, 3, OLDSTARTTIME)

Sunday, February 19, 2012

Dateime valid ranges

I've got a linked server setup to DB2, and some of the
date fields in the DB contain 1/1/0001 values.

I've got views created in SQL2000 against the DB2 linked server.

When I run a query against a particular table that contains multiple field of datetime type.

I get the below error
Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

This only happens when I include in the select the field that contains 1/1/0001 values.

I assume since valid dates ranges in SQL are from
January 1, 1753 through December 31, 9999, this would be
what's causing this.

I tried to covert in the select but that failed as well. The only thing that I've been able to do, is to use a DTS to pull the data from the DB2 to a local SQL2000 table, with that fields type set as varchar. This works.

Using a DTS to pull the data to a local table in production isn't a viable workaround, since this table contains 1.8 million rows.

How is DTS converting this field, when convert fails in the select?

How do I get around this?
Thanksif you convert the DB2 attribute to (v)char rather than a date does your query work?

If that works could you convert to string then use a case statment to move the date to the epoc for SQL server?|||Convert the field to vchar on the DB2 side?

This is the only field from the DB2 that I'm having issue wth, I have other fields that are also datetime and all other fields can be retrieved.

I just can't retrieve this one in particular, because it contains 1/1/0001 values, outside the valid SQL2000 date range.|||How about...Add a predicate so the result set only returns valid data, and UNION it to a select where the date column is a literal...since it's unusable anyway...|||convert the attribute in your select. I am wondering if the above mentioned error occures when converting the db2 date to a sql date. If that is the case why not convert it to a (v)char? Or more precisly, use a case statment to move the 01/01/0001 date to (v)char that you cna test and if needed replace the date with an 01/01/1753 date?|||I tried to convert it to vchar, it failed as well.

I don't think your case idea will work either, since I won't be able to get teh value to test against.

I conditionally need this fields values, so I can include that condition, Union as was mentioned in a prior post and just have a place holder in my first slect.

This select is pretty nasty, it already contains one union, so now it looks like it's going to have three. Another for each of the existing selects, ouch.

With the size of this select, I may split it apart, that way I only have to do the union on the one specific field, and the db again as I go thru the first result set. Not prime I know, but sometimes you gotta work around issues.

Thanks for the ideas|||No Create a view on that table

CREATE VIEW myView99
AS
SELECT Col list
FROM myTable99
WHERE ISDATE(DB2dateCol)=1
UNION ALL
SELECT Col list
FROM myTable99
WHERE ISDATE(DB2dateCol)=0|||cool suggestion!

Would your isdate()=0 default the offending date field to a value other than '01/01/0001'?|||Let me try that Brett, I'll let you know the outcome|||CREATE VIEW dbo.blspt_test
AS
SELECT ordid, mgfordt
FROM blspt
WHERE ISDATE(mgfordt)=1
and blbatid = 790
UNION ALL
SELECT ordid,''
FROM blspt
WHERE ISDATE(mgfordt)=0
and blbatid = 790

select * from blspt_test

Same error

Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Great suggestion though.|||This is the select without the added unions that I'll have to add because of the date issue.

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
dbo.ORDLN.SPTCHR,
dbo.ORDLN.SPTPAT,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF RIGHT OUTER JOIN
dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
dbo.BLSPT INNER JOIN
dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)

UNION

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
8 as sptchr,
'' as sptpat,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF
RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
RIGHT OUTER JOIN dbo.BLSPT
INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)|||Originally posted by jtn916
CREATE VIEW dbo.blspt_test
AS
SELECT ordid, mgfordt
FROM blspt
WHERE ISDATE(mgfordt)=1
and blbatid = 790
UNION ALL
SELECT ordid,''
FROM blspt
WHERE ISDATE(mgfordt)=0
and blbatid = 790

select * from blspt_test

Same error

Server: Msg 8114, Level 16, State 8, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

Great suggestion though.

Damn...

OK...do you have any say over the DB2 Box?

Maybe you can put the view there...|||I can create tables on the db2 but, when it's updated, which is often, they wipe out all that doesn't belong to the DB, according to "them", thus any tables or views I have would be deleted.

I could however check for existence of the needed view, if it doesn't exist, create it in my app, and go on.

I think I'll just go with my SP that I have working.

if you curious

Thanks for your time.

I have another interesting problem in relationship to DB2 linked servers, views and SQL2000 on 2003 server. I posted it here as well the other day, but got no replies.

In short, a view created against a linked db2 server in sql2000 on 2003 server, will only return 512k of data in a result set when you run a query against it. This is not the case if done on 2000 server. I gave up on that one, and moved all my crap over to a 2000 box.

Thanks for your time, if you hear of a better solution, I'd be interested in hearing it. In relationship to this date issue.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--IF BLSPT.ORDLNTYP = 2 then it's a make good
--Since paradigm stores default values in the mgfordt field
-- as 1/1/0001, and sql pukes on that date, because it's outside
--the valid date range, we'll add unions to handle this.
--One select with a place holder 01/01/1900 for the mgfordt if it's not a
--makegood, and get mgfordt if it is

ALTER procedure dbo.sp_blspt_extract
@.batch as integer
as

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
cast('01/01/1900' as datetime) as MGFORDT,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
dbo.ORDLN.SPTCHR,
dbo.ORDLN.SPTPAT,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF RIGHT OUTER JOIN
dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
dbo.BLSPT INNER JOIN
dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = 790)
--AND (NOT (RTRIM(STNID) IN ('INTUSA', 'INTCAN')))
--CLIEN ACCESS pukes on this one, I'll wead out the few I don't need in code
AND DBO.BLSPT.ORDLNTYP <> 2 --Not a makegood

UNION

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
cast('01/01/1900' as datetime) as MGFORDT,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
8 as sptchr,
'' as sptpat,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF
RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
RIGHT OUTER JOIN dbo.BLSPT
INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)
AND DBO.BLSPT.ORDLNTYP <> 2 --Not a makegood

UNION --Now we'll get the makegoods

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
dbo.BLSPT.MGFORDT,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
dbo.ORDLN.SPTCHR,
dbo.ORDLN.SPTPAT,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF RIGHT OUTER JOIN
dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX RIGHT OUTER JOIN
dbo.BLSPT INNER JOIN
dbo.ORDLN ON dbo.BLSPT.ORDID = dbo.ORDLN.ORDID AND dbo.BLSPT.ORDLNNUM = dbo.ORDLN.ORDLNNUM AND dbo.BLSPT.ORDLNTYP = dbo.ORDLN.ORDLNTYP AND
dbo.BLSPT.ORDLNSEQ = dbo.ORDLN.ORDLNSEQ INNER JOIN
dbo.ORDHDR ON dbo.ORDLN.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID AND
dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID LEFT OUTER JOIN
dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID LEFT OUTER JOIN
dbo.BUYUNTHDR ON dbo.ORDLN.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)
--AND (NOT (RTRIM(STNID) IN ('INTUSA', 'INTCAN')))
--CLIEN ACCESS pukes on this one, I'll wead out the few I don't need in code
AND DBO.BLSPT.ORDLNTYP = 2 --makegood

UNION

SELECT dbo.BLSPT.STNID,
dbo.BLSPT.ORDID,
dbo.BLSPT.ORDLNNUM,
dbo.BLSPT.ORDLNTYP,
dbo.BLSPT.ORDLNSEQ,
dbo.BLSPT.ALTLOG,
dbo.BLSPT.SPTNUM,
dbo.BLSPT.SPTLEN,
dbo.BLSPT.EXTCPYNUM,
dbo.BLSPT.PREDSCR,
dbo.BLSPT.MGFORDT,
dbo.BLSPT.RT,
dbo.BLSPT.BCSTDT,
dbo.BLSPT.AIRTIM,
dbo.BLSPT.SPTCHR,
dbo.BLSPT.BCSTTIM,
dbo.BUYUNTHDR.BUYSNAM,
dbo.BUYUNTHDR.BUYLNAM,
dbo.BUYUNTHDR.STRTIM,
dbo.BUYUNTHDR.ETIM,
8 as sptchr,
'' as sptpat,
dbo.OFC.SOFNAM,
dbo.ORDHDR.ACCTTYP,
dbo.STF.FSTNAM,
dbo.STF.LSTNAM,
dbo.PROP_HDR.DEAL_ID
FROM dbo.STF
RIGHT OUTER JOIN dbo.PROP_HDR ON dbo.STF.USR = dbo.PROP_HDR.AEX
RIGHT OUTER JOIN dbo.BLSPT
INNER JOIN dbo.ORDLNNT ON dbo.BLSPT.ORDID = dbo.ORDLNNT.ORDID
INNER JOIN dbo.ORDHDR ON dbo.ORDLNNT.ORDID = dbo.ORDHDR.ORDID ON dbo.PROP_HDR.PROPOSAL_ID = dbo.ORDHDR.PROPOSAL_ID
AND dbo.PROP_HDR.VERSION_ID = dbo.ORDHDR.VERSION_ID
LEFT OUTER JOIN dbo.OFC ON dbo.ORDHDR.SOFID = dbo.OFC.SOFID
LEFT OUTER JOIN dbo.BUYUNTHDR ON dbo.BLSPT.BUYUNTID = dbo.BUYUNTHDR.BUYUNTID
WHERE (dbo.BLSPT.BLBATID = @.batch)
AND DBO.BLSPT.ORDLNTYP = 2 --makegood
ORDER BY dbo.BLSPT.BCSTDT ASC,
dbo.BLSPT.AIRTIM ASC

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO|||But I was suggesting you create the view on DB2 and reference it from sql server...

dateformat is ignored

Hello,

I receive a file containing some character fields along with a date.
The date values in the file are formatted as "dd/mm/yy", that is
2-digit day, 2-digit month, and 2-digit year. The separator could be
slash or a dash ("-"). The file is in a proprietary format, and bcp is
not an option.

So, I decided to load the file using a prepared statement. I open a
cursor with an INSERT statement, read from the file, parse out values,
and put it in the database using the cursor. All is OK; except that
the date values are mangled. This is despite the fact that I am issuing
a "set dateformat dmy" before running the INSERT statement.

It seems that the "set dateformat dmy" is not being accepted, or it is
being ignored. I set it at the beginning right after opening a
connection to the database. From what I understand, it should work.
Am I doing something wrong? Any suggestions on how to get this to
work?

Thanks!newtophp2000@.yahoo.com wrote:

> Hello,
> I receive a file containing some character fields along with a date.
> The date values in the file are formatted as "dd/mm/yy", that is
> 2-digit day, 2-digit month, and 2-digit year. The separator could be
> slash or a dash ("-"). The file is in a proprietary format, and bcp is
> not an option.
> So, I decided to load the file using a prepared statement. I open a
> cursor with an INSERT statement, read from the file, parse out values,
> and put it in the database using the cursor. All is OK; except that
> the date values are mangled. This is despite the fact that I am issuing
> a "set dateformat dmy" before running the INSERT statement.
> It seems that the "set dateformat dmy" is not being accepted, or it is
> being ignored. I set it at the beginning right after opening a
> connection to the database. From what I understand, it should work.
> Am I doing something wrong? Any suggestions on how to get this to
> work?
> Thanks!

You say BCP isn't an option but you didn't explain what other method
you are using to read the file or why a cursor is necessary. Don't rely
on SET DATEFORMAT. Use the CONVERT function with the style parameter to
specify the exact format. Looks like style 3 or 103 is what you need.

--
David Portas
SQL Server MVP
--|||David Portas wrote:
> You say BCP isn't an option but you didn't explain what other method
> you are using to read the file or why a cursor is necessary. Don't rely
> on SET DATEFORMAT. Use the CONVERT function with the style parameter to
> specify the exact format. Looks like style 3 or 103 is what you need.

I read from the file line by line and parse the line to extract the
fields. I then use the bound variables in the prepared Insert
statement to add it to the database. I wanted to change the DATEFORMAT
configuration as it seemed to be such a straight answer. I guess I
could use the CONVERT function if it is fast enough. I can do some
tests to see how it performs.

I am curius: is there a particular reason to shy away from setting
DATEFORMAT? Is it not reliable as implemented or something else?

Thanks a lot!

> --
> David Portas
> SQL Server MVP
> --|||Hi

If you are parsing a string then you constructing the date in CCYYMMDD
format will be a safe option.

John

<newtophp2000@.yahoo.com> wrote in message
news:1135777730.480129.321010@.z14g2000cwz.googlegr oups.com...
> David Portas wrote:
>> You say BCP isn't an option but you didn't explain what other method
>> you are using to read the file or why a cursor is necessary. Don't rely
>> on SET DATEFORMAT. Use the CONVERT function with the style parameter to
>> specify the exact format. Looks like style 3 or 103 is what you need.
>
> I read from the file line by line and parse the line to extract the
> fields. I then use the bound variables in the prepared Insert
> statement to add it to the database. I wanted to change the DATEFORMAT
> configuration as it seemed to be such a straight answer. I guess I
> could use the CONVERT function if it is fast enough. I can do some
> tests to see how it performs.
> I am curius: is there a particular reason to shy away from setting
> DATEFORMAT? Is it not reliable as implemented or something else?
> Thanks a lot!
>
>> --
>> David Portas
>> SQL Server MVP
>> --|||David and John,

Thank you very much for your input. I am now using the techniques that
you suggested and it works great!

dateformat

Hi every body
I am using a stored procedure to insert values into a table which contain a date datatype. I pass the date in MM-DD-YYYY format.
When i execute the querry it shows error invalid month.

create or replace procedure sample_I
(
P_no varchar2,
P_date date
)
as
begin
insert into sample values(P_no ,to_date(P_date,'MM-DD-YYYY' ));
end;Since p_date already is a date, you should not apply the TO_DATE function to it - doing so is the cause of your problem. Code should be:
create or replace procedure sample_I
(
P_no varchar2,
P_date date
)
as
begin
insert into sample values(P_no ,P_date);
end;
BTW, since this question is Oracle-specific, it would have got answered quicker if you had posted it in the Oracle forum!

datediff() alters other values in nested iif

i have a nested IIF statement, see below, that evaluates all possible field
values of a particular field, and outputs appropriate text. The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"
I convert the last possible value type to date by concatenation:
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
The report works fine like this , but once I introduce the DATEDIFF(),
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
generating a invalid date function. In other words, instead of the values
"FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
suitable values that can be converted to a date format, they too are
subjected to CDATE(). You can clearly see this in the error below as "BASIC"
is converted to "01/IC/BA"
complete IIF expression:
=IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))= "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,"OLD","FASHION")))
error generated when DATEDIFF() is introduced:
Warning 1 [rsRuntimeErrorInExpression] The Value expression for the textbox
â'textbox25â' contains an error: Conversion from string "01/IC/BA" to type
'Date' is not valid.
thanks for you help in advance.
anthonyinstead of using cdate, do you think datevalue may work better?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>i have a nested IIF statement, see below, that evaluates all possible field
> values of a particular field, and outputs appropriate text. The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"
> I convert the last possible value type to date by concatenation:
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> The report works fine like this , but once I introduce the DATEDIFF(),
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> generating a invalid date function. In other words, instead of the values
> "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> suitable values that can be converted to a date format, they too are
> subjected to CDATE(). You can clearly see this in the error below as
> "BASIC"
> is converted to "01/IC/BA"
>
> complete IIF expression:
> =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,"OLD","FASHION")))
>
> error generated when DATEDIFF() is introduced:
> Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> textbox
> 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> 'Date' is not valid.
> thanks for you help in advance.
> anthony
>
>|||thanks for the quick reply...cdate is not the issue it's the introduction of
datediff that appears to be called prior to the earlier conditions in the
nested iif being evaluated. as a result, the date expression is evaluated on
data that should have been accounted for before...see the error message
posted "01/IC/BA" will never be recognized as a date no matter what function
i call.
"Ben Watts" wrote:
> instead of using cdate, do you think datevalue may work better?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >i have a nested IIF statement, see below, that evaluates all possible field
> > values of a particular field, and outputs appropriate text. The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"
> >
> > I convert the last possible value type to date by concatenation:
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > The report works fine like this , but once I introduce the DATEDIFF(),
> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> > generating a invalid date function. In other words, instead of the values
> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving only
> > suitable values that can be converted to a date format, they too are
> > subjected to CDATE(). You can clearly see this in the error below as
> > "BASIC"
> > is converted to "01/IC/BA"
> >
> >
> > complete IIF expression:
> >
> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> > < -2,"OLD","FASHION")))
> >
> >
> > error generated when DATEDIFF() is introduced:
> >
> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> > textbox
> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to type
> > 'Date' is not valid.
> >
> > thanks for you help in advance.
> > anthony
> >
> >
> >
> >
>
>|||What are the values of styleseason, that you are trying to convert? So far
I know there is flash and basic, but what are the others?
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> thanks for the quick reply...cdate is not the issue it's the introduction
> of
> datediff that appears to be called prior to the earlier conditions in the
> nested iif being evaluated. as a result, the date expression is evaluated
> on
> data that should have been accounted for before...see the error message
> posted "01/IC/BA" will never be recognized as a date no matter what
> function
> i call.
> "Ben Watts" wrote:
>> instead of using cdate, do you think datevalue may work better?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >i have a nested IIF statement, see below, that evaluates all possible
>> >field
>> > values of a particular field, and outputs appropriate text. The
>> > possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"
>> >
>> > I convert the last possible value type to date by concatenation:
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > The report works fine like this , but once I introduce the DATEDIFF(),
>> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> > generating a invalid date function. In other words, instead of the
>> > values
>> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> > only
>> > suitable values that can be converted to a date format, they too are
>> > subjected to CDATE(). You can clearly see this in the error below as
>> > "BASIC"
>> > is converted to "01/IC/BA"
>> >
>> >
>> > complete IIF expression:
>> >
>> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> > < -2,"OLD","FASHION")))
>> >
>> >
>> > error generated when DATEDIFF() is introduced:
>> >
>> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> > textbox
>> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> > type
>> > 'Date' is not valid.
>> >
>> > thanks for you help in advance.
>> > anthony
>> >
>> >
>> >
>> >
>>|||from my original post:
-- The possible
field values for Fields!STYLESEASON, are
"FLASH","BASIC" and text consisting of year and month in "yymm" format. e.g.
"0604"--
I am not trying to convert all values only the ones that are not flash or
basic. The other values,which are in yymm format I am doing some string
manipulation and concatenation to get it into a mm/dd/yy format.
"01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
According to my IIF statement I am evaluating conditions for flash and basic
first wich should leave only yymm to convert to mm/dd/yy values. At this
point the report works as it should
BASIC to BASIC
FLASH to OLD
yymm to mm/dd/yy
Once I introduce any date function cdate,dateval or datediff into a sinlge
IIF in the nested IIF statements all styleseason values are evaluated by the
date function and obviously gives an error for the flash and basic values. I
end up with values like
"01/IC/BA" trying to be evaluated, which is BASIC run through the
concatenation. Please see full IIF statement.
CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
"Ben Watts" wrote:
> What are the values of styleseason, that you are trying to convert? So far
> I know there is flash and basic, but what are the others?
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> > thanks for the quick reply...cdate is not the issue it's the introduction
> > of
> > datediff that appears to be called prior to the earlier conditions in the
> > nested iif being evaluated. as a result, the date expression is evaluated
> > on
> > data that should have been accounted for before...see the error message
> > posted "01/IC/BA" will never be recognized as a date no matter what
> > function
> > i call.
> >
> > "Ben Watts" wrote:
> >
> >> instead of using cdate, do you think datevalue may work better?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >field
> >> > values of a particular field, and outputs appropriate text. The
> >> > possible
> >> > field values for Fields!STYLESEASON, are
> >> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> >> > e.g.
> >> > "0604"
> >> >
> >> > I convert the last possible value type to date by concatenation:
> >> >
> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > The report works fine like this , but once I introduce the DATEDIFF(),
> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >
> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> > generating a invalid date function. In other words, instead of the
> >> > values
> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> > only
> >> > suitable values that can be converted to a date format, they too are
> >> > subjected to CDATE(). You can clearly see this in the error below as
> >> > "BASIC"
> >> > is converted to "01/IC/BA"
> >> >
> >> >
> >> > complete IIF expression:
> >> >
> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> > < -2,"OLD","FASHION")))
> >> >
> >> >
> >> > error generated when DATEDIFF() is introduced:
> >> >
> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> > textbox
> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> > type
> >> > 'Date' is not valid.
> >> >
> >> > thanks for you help in advance.
> >> > anthony
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||I see a wayof trying it. You could either write some sort of CASE statment
in your select statement setting the value to a field. Like:
CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
Then write your nested if, something like this.
iif(Fields!Date.Value = 'neither' and
DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
< -2,
'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
I think you see where I am taking this. Basically the case statment will be
handled first then the rest of it will also be handled in turn. I hope this
helps.
Then enter that instead of your nested if and that should work.
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> from my original post:
> -- The possible
> field values for Fields!STYLESEASON, are
> "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> e.g.
> "0604"--
> I am not trying to convert all values only the ones that are not flash or
> basic. The other values,which are in yymm format I am doing some string
> manipulation and concatenation to get it into a mm/dd/yy format.
> "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> According to my IIF statement I am evaluating conditions for flash and
> basic
> first wich should leave only yymm to convert to mm/dd/yy values. At this
> point the report works as it should
> BASIC to BASIC
> FLASH to OLD
> yymm to mm/dd/yy
> Once I introduce any date function cdate,dateval or datediff into a
> sinlge
> IIF in the nested IIF statements all styleseason values are evaluated by
> the
> date function and obviously gives an error for the flash and basic values.
> I
> end up with values like
> "01/IC/BA" trying to be evaluated, which is BASIC run through the
> concatenation. Please see full IIF statement.
> CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>
>
>
> "Ben Watts" wrote:
>> What are the values of styleseason, that you are trying to convert? So
>> far
>> I know there is flash and basic, but what are the others?
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> > thanks for the quick reply...cdate is not the issue it's the
>> > introduction
>> > of
>> > datediff that appears to be called prior to the earlier conditions in
>> > the
>> > nested iif being evaluated. as a result, the date expression is
>> > evaluated
>> > on
>> > data that should have been accounted for before...see the error message
>> > posted "01/IC/BA" will never be recognized as a date no matter what
>> > function
>> > i call.
>> >
>> > "Ben Watts" wrote:
>> >
>> >> instead of using cdate, do you think datevalue may work better?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >i have a nested IIF statement, see below, that evaluates all possible
>> >> >field
>> >> > values of a particular field, and outputs appropriate text. The
>> >> > possible
>> >> > field values for Fields!STYLESEASON, are
>> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> > format.
>> >> > e.g.
>> >> > "0604"
>> >> >
>> >> > I convert the last possible value type to date by concatenation:
>> >> >
>> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > The report works fine like this , but once I introduce the
>> >> > DATEDIFF(),
>> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >
>> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
>> >> > generating a invalid date function. In other words, instead of the
>> >> > values
>> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
>> >> > only
>> >> > suitable values that can be converted to a date format, they too are
>> >> > subjected to CDATE(). You can clearly see this in the error below
>> >> > as
>> >> > "BASIC"
>> >> > is converted to "01/IC/BA"
>> >> >
>> >> >
>> >> > complete IIF expression:
>> >> >
>> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> > < -2,"OLD","FASHION")))
>> >> >
>> >> >
>> >> > error generated when DATEDIFF() is introduced:
>> >> >
>> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
>> >> > textbox
>> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
>> >> > type
>> >> > 'Date' is not valid.
>> >> >
>> >> > thanks for you help in advance.
>> >> > anthony
>> >> >
>> >> >
>> >> >
>> >> >
>> >>
>> >>
>> >>
>>|||i was actually trying to avoid doing any of the cases in sql, but it looks
like ill have to do it that way. don't you think it is a bug of some sort as
to why calling the function in the iif takes precedence over the previous
conditional statements. in any regards, thank you for your time and help.
"Ben Watts" wrote:
> I see a wayof trying it. You could either write some sort of CASE statment
> in your select statement setting the value to a field. Like:
> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as Date
> Then write your nested if, something like this.
> iif(Fields!Date.Value = 'neither' and
> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> < -2,
> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
> I think you see where I am taking this. Basically the case statment will be
> handled first then the rest of it will also be handled in turn. I hope this
> helps.
>
> Then enter that instead of your nested if and that should work.
> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
> > from my original post:
> > -- The possible
> > field values for Fields!STYLESEASON, are
> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
> > e.g.
> > "0604"--
> >
> > I am not trying to convert all values only the ones that are not flash or
> > basic. The other values,which are in yymm format I am doing some string
> > manipulation and concatenation to get it into a mm/dd/yy format.
> >
> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
> >
> > According to my IIF statement I am evaluating conditions for flash and
> > basic
> > first wich should leave only yymm to convert to mm/dd/yy values. At this
> > point the report works as it should
> >
> > BASIC to BASIC
> > FLASH to OLD
> > yymm to mm/dd/yy
> >
> > Once I introduce any date function cdate,dateval or datediff into a
> > sinlge
> > IIF in the nested IIF statements all styleseason values are evaluated by
> > the
> > date function and obviously gives an error for the flash and basic values.
> > I
> > end up with values like
> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
> > concatenation. Please see full IIF statement.
> >
> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >
> >
> >
> >
> >
> >
> > "Ben Watts" wrote:
> >
> >> What are the values of styleseason, that you are trying to convert? So
> >> far
> >> I know there is flash and basic, but what are the others?
> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
> >> > thanks for the quick reply...cdate is not the issue it's the
> >> > introduction
> >> > of
> >> > datediff that appears to be called prior to the earlier conditions in
> >> > the
> >> > nested iif being evaluated. as a result, the date expression is
> >> > evaluated
> >> > on
> >> > data that should have been accounted for before...see the error message
> >> > posted "01/IC/BA" will never be recognized as a date no matter what
> >> > function
> >> > i call.
> >> >
> >> > "Ben Watts" wrote:
> >> >
> >> >> instead of using cdate, do you think datevalue may work better?
> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
> >> >> >i have a nested IIF statement, see below, that evaluates all possible
> >> >> >field
> >> >> > values of a particular field, and outputs appropriate text. The
> >> >> > possible
> >> >> > field values for Fields!STYLESEASON, are
> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
> >> >> > format.
> >> >> > e.g.
> >> >> > "0604"
> >> >> >
> >> >> > I convert the last possible value type to date by concatenation:
> >> >> >
> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > The report works fine like this , but once I introduce the
> >> >> > DATEDIFF(),
> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> >
> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by CDATE()
> >> >> > generating a invalid date function. In other words, instead of the
> >> >> > values
> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions leaving
> >> >> > only
> >> >> > suitable values that can be converted to a date format, they too are
> >> >> > subjected to CDATE(). You can clearly see this in the error below
> >> >> > as
> >> >> > "BASIC"
> >> >> > is converted to "01/IC/BA"
> >> >> >
> >> >> >
> >> >> > complete IIF expression:
> >> >> >
> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
> >> >> > < -2,"OLD","FASHION")))
> >> >> >
> >> >> >
> >> >> > error generated when DATEDIFF() is introduced:
> >> >> >
> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for the
> >> >> > textbox
> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA" to
> >> >> > type
> >> >> > 'Date' is not valid.
> >> >> >
> >> >> > thanks for you help in advance.
> >> >> > anthony
> >> >> >
> >> >> >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||It really does seem like a bug, you could always put that portion of the if
statement first and see if it works that way. I have had weirder things
happen with if statements. Good luck
"nitz" <nitz@.discussions.microsoft.com> wrote in message
news:A9BF9E34-15F4-43C5-980E-A6AF59605C16@.microsoft.com...
>i was actually trying to avoid doing any of the cases in sql, but it looks
> like ill have to do it that way. don't you think it is a bug of some sort
> as
> to why calling the function in the iif takes precedence over the previous
> conditional statements. in any regards, thank you for your time and help.
> "Ben Watts" wrote:
>> I see a wayof trying it. You could either write some sort of CASE
>> statment
>> in your select statement setting the value to a field. Like:
>> CASE WHEN STYLESEASON = 'BASIC' THEN 'BASIC'
>> WHEN STYLESEASON = 'FLASH, THEN 'OLD' ELSE neither END as
>> Date
>> Then write your nested if, something like this.
>> iif(Fields!Date.Value = 'neither' and
>> DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> < -2,
>> 'OLD', iif(Fields!Date.Value <> 'neither', Fields!Date.Value, 'FASHION'))
>> I think you see where I am taking this. Basically the case statment will
>> be
>> handled first then the rest of it will also be handled in turn. I hope
>> this
>> helps.
>>
>> Then enter that instead of your nested if and that should work.
>> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> news:4610FAAC-4D75-48C1-ABBE-A9E82B1C96B3@.microsoft.com...
>> > from my original post:
>> > -- The possible
>> > field values for Fields!STYLESEASON, are
>> > "FLASH","BASIC" and text consisting of year and month in "yymm" format.
>> > e.g.
>> > "0604"--
>> >
>> > I am not trying to convert all values only the ones that are not flash
>> > or
>> > basic. The other values,which are in yymm format I am doing some
>> > string
>> > manipulation and concatenation to get it into a mm/dd/yy format.
>> >
>> > "01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2))
>> >
>> > According to my IIF statement I am evaluating conditions for flash and
>> > basic
>> > first wich should leave only yymm to convert to mm/dd/yy values. At
>> > this
>> > point the report works as it should
>> >
>> > BASIC to BASIC
>> > FLASH to OLD
>> > yymm to mm/dd/yy
>> >
>> > Once I introduce any date function cdate,dateval or datediff into a
>> > sinlge
>> > IIF in the nested IIF statements all styleseason values are evaluated
>> > by
>> > the
>> > date function and obviously gives an error for the flash and basic
>> > values.
>> > I
>> > end up with values like
>> > "01/IC/BA" trying to be evaluated, which is BASIC run through the
>> > concatenation. Please see full IIF statement.
>> >
>> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >
>> >
>> >
>> >
>> >
>> >
>> > "Ben Watts" wrote:
>> >
>> >> What are the values of styleseason, that you are trying to convert?
>> >> So
>> >> far
>> >> I know there is flash and basic, but what are the others?
>> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> news:51BA1380-A242-4778-98AC-E5735D729A7C@.microsoft.com...
>> >> > thanks for the quick reply...cdate is not the issue it's the
>> >> > introduction
>> >> > of
>> >> > datediff that appears to be called prior to the earlier conditions
>> >> > in
>> >> > the
>> >> > nested iif being evaluated. as a result, the date expression is
>> >> > evaluated
>> >> > on
>> >> > data that should have been accounted for before...see the error
>> >> > message
>> >> > posted "01/IC/BA" will never be recognized as a date no matter what
>> >> > function
>> >> > i call.
>> >> >
>> >> > "Ben Watts" wrote:
>> >> >
>> >> >> instead of using cdate, do you think datevalue may work better?
>> >> >> "nitz" <nitz@.discussions.microsoft.com> wrote in message
>> >> >> news:B3F0447A-6670-4F5F-A428-9C0DC6B0FBF3@.microsoft.com...
>> >> >> >i have a nested IIF statement, see below, that evaluates all
>> >> >> >possible
>> >> >> >field
>> >> >> > values of a particular field, and outputs appropriate text. The
>> >> >> > possible
>> >> >> > field values for Fields!STYLESEASON, are
>> >> >> > "FLASH","BASIC" and text consisting of year and month in "yymm"
>> >> >> > format.
>> >> >> > e.g.
>> >> >> > "0604"
>> >> >> >
>> >> >> > I convert the last possible value type to date by concatenation:
>> >> >> >
>> >> >> > CDATE("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > The report works fine like this , but once I introduce the
>> >> >> > DATEDIFF(),
>> >> >> > DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> >
>> >> >> > all instances of Fields!STYLESEASON.Value are evaluated by
>> >> >> > CDATE()
>> >> >> > generating a invalid date function. In other words, instead of
>> >> >> > the
>> >> >> > values
>> >> >> > "FLASH" & "BASIC" being matched in the prior IIF conditions
>> >> >> > leaving
>> >> >> > only
>> >> >> > suitable values that can be converted to a date format, they too
>> >> >> > are
>> >> >> > subjected to CDATE(). You can clearly see this in the error
>> >> >> > below
>> >> >> > as
>> >> >> > "BASIC"
>> >> >> > is converted to "01/IC/BA"
>> >> >> >
>> >> >> >
>> >> >> > complete IIF expression:
>> >> >> >
>> >> >> > =IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))=>> >> >> > "FLASH","OLD",IIF(UCASE(RTRIM(Fields!STYLESEASON.Value))="BASIC","BASIC",IIF(DATEDIFF("m",Fields!START_DATE.Value,CDate("01/"+Right(RTrim(Fields!STYLESEASON.Value),2)+"/"+Left(RTrim(Fields!STYLESEASON.Value),2)))
>> >> >> > < -2,"OLD","FASHION")))
>> >> >> >
>> >> >> >
>> >> >> > error generated when DATEDIFF() is introduced:
>> >> >> >
>> >> >> > Warning 1 [rsRuntimeErrorInExpression] The Value expression for
>> >> >> > the
>> >> >> > textbox
>> >> >> > 'textbox25' contains an error: Conversion from string "01/IC/BA"
>> >> >> > to
>> >> >> > type
>> >> >> > 'Date' is not valid.
>> >> >> >
>> >> >> > thanks for you help in advance.
>> >> >> > anthony
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>

Friday, February 17, 2012

Datediff

Hello
What's the easiest way to get the number of months between two values like
this?
Present date 200505 (YYYYMM) and 199905 (YYYYMM)
Steve
Thank youAdd '01'
SELECT DATEDIFF
(
MONTH,
'199905'+'01',
'200505'+'01'
)
"Steve Read" <SteveRead@.discussions.microsoft.com> wrote in message
news:0B2957AF-9136-4750-87D8-A08E4644FB31@.microsoft.com...
> Hello
> What's the easiest way to get the number of months between two values like
> this?
> Present date 200505 (YYYYMM) and 199905 (YYYYMM)
> Steve
> Thank you|||Thank you very much Aaron, works nicely.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Add '01'
> SELECT DATEDIFF
> (
> MONTH,
> '199905'+'01',
> '200505'+'01'
> )
>
>
> "Steve Read" <SteveRead@.discussions.microsoft.com> wrote in message
> news:0B2957AF-9136-4750-87D8-A08E4644FB31@.microsoft.com...
>
>

Tuesday, February 14, 2012

Date_Time Convert(24) to DateTime Format

Please help me modify the sql statement so that I average w

ly values for
FullScan and CPU.
My Date_Time and samples are written to a table with the
char(24) format.
I need to convert the Date_Time field from char(24) to datetime format.
Please help me with this task.
Thanks,
Date_Time char(24)
2005-01-24 16:06:48.966
2005-01-24 16:07:48.966
2005-01-24 16:08:48.966
select
dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202') as HourStart,
avg([Full_Scan_Sec]) as FullScan,
avg([CPU_Processor_Time]) as CPU
from Server_Data
where date_time BETWEEN '20040802' and '20050202'
group by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20040802')
order by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')Joe
Have you tried CONVERT system function?
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:42A097A1-85E1-4252-A39E-5A7320E65268@.microsoft.com...
> Please help me modify the sql statement so that I average w

ly values
for
> FullScan and CPU.
> My Date_Time and samples are written to a table with the
> char(24) format.
> I need to convert the Date_Time field from char(24) to datetime format.
> Please help me with this task.
> Thanks,
>
> Date_Time char(24)
> 2005-01-24 16:06:48.966
> 2005-01-24 16:07:48.966
> 2005-01-24 16:08:48.966
> select
> dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202') as
HourStart,
> avg([Full_Scan_Sec]) as FullScan,
> avg([CPU_Processor_Time]) as CPU
> from Server_Data
> where date_time BETWEEN '20040802' and '20050202'
> group by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20040802')
> order by dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')
>|||I tried to convert the Date_Time from char(24) to smalldatetime received
syntax error.
select counterDateTime from CounterData (NOLOCK)
where convert(smalldatetime,counterdatetime)
Line 2: Incorrect syntax near ')'.
Please help me resolve this problem.
Thank You,
"Uri Dimant" wrote:

> Joe
> Have you tried CONVERT system function?
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:42A097A1-85E1-4252-A39E-5A7320E65268@.microsoft.com...
> for
> HourStart,
>
>|||> select counterDateTime from CounterData (NOLOCK)
> where convert(smalldatetime,counterdatetime)
Abive is not a valid WHERE clause. It is similar to saying:
WHERE colname
A WHERE clause need some predicate, like:
WHERE colname = 23
What do you want to achieve? Return only the rows in where you have a string
in the column that can
be converted to datetime? If so, try:
WHERE ISDATE(counterdatetime) = 1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:84F6AF1A-E00B-416D-95CB-F5A3C05054BD@.microsoft.com...
> I tried to convert the Date_Time from char(24) to smalldatetime received
> syntax error.
> select counterDateTime from CounterData (NOLOCK)
> where convert(smalldatetime,counterdatetime)
> Line 2: Incorrect syntax near ')'.
> Please help me resolve this problem.
> Thank You,
>
>
> "Uri Dimant" wrote:
>|||I'm trying to convert the DateTime column format from char(24) to
smalldatetime in a sql query. When it's in smalldatetime format then use the
average function to calculate average full_scan and CPU values.
The sql statements listed below.
Thank You,
"Tibor Karaszi" wrote:

> Abive is not a valid WHERE clause. It is similar to saying:
> WHERE colname
> A WHERE clause need some predicate, like:
> WHERE colname = 23
> What do you want to achieve? Return only the rows in where you have a stri
ng in the column that can
> be converted to datetime? If so, try:
> WHERE ISDATE(counterdatetime) = 1
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
> news:84F6AF1A-E00B-416D-95CB-F5A3C05054BD@.microsoft.com...
>
>|||I'm not sure exactly what your problem is. You posted a query in the origina
l post, but you didn't
say what happens when you run the query. Are you saying that below part fail
s? If so, what error
message do you get? Or incorrect results?
dateadd(hour,datediff(hour,'20040802',[d
ate_Time]),'20050202')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:4D4CBFAA-70AB-498A-BBE4-3F9497793250@.microsoft.com...
> I'm trying to convert the DateTime column format from char(24) to
> smalldatetime in a sql query. When it's in smalldatetime format then use t
he
> average function to calculate average full_scan and CPU values.
> The sql statements listed below.
> Thank You,
>
> "Tibor Karaszi" wrote:
>

Tuesday, March 27, 2012

dayly table update

Thursday, March 22, 2012

datetime vs varchar

datetime vs varchar

DateTime Values in SQL Express ASPNETDB.MDF

datetime to integer

Wednesday, March 21, 2012

datetime to date

DATETIME question

Datetime problem

Sunday, March 11, 2012

datetime format problem

DateTime format during INSERT using MS JDBC driver

Thursday, March 8, 2012

datetime datatype conversion

Wednesday, March 7, 2012

datetime compare - eastern and pacific

Sunday, February 19, 2012

Dateime valid ranges

dateformat is ignored

dateformat

datediff() alters other values in nested iif

Friday, February 17, 2012

Datediff

Tuesday, February 14, 2012

Date_Time Convert(24) to DateTime Format

db error

Blog Archive

About Me