Converting dates.

Kudos to y'all!!! I have this task of fixing a database table which contains dates but in a VARCHAR type column. Now I wanted to convert them to 103 format. But the problem is, some values were inserted into the database in either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR. Is there an easy way of doing such task?no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?|||no, there is no easy way

for example, is 04/05/2006 in dd/mm/yyyy format or in mm/dd/yyyy format?

It's in either format. Some dates are in dd/mm/yyyy and some are in mm/dd/yyyy. It's an old table and I don't know for sure which date format was used for it.|||i think you missed the intent of my question :)

i was trying to point out that the answer to your question "Is there an easy way of doing such task?" is no, because there will always be these types of values that you just cannot decide|||What does this give you?

SELECT * FROM Table WHERE ISDATE(DateCol)=0

??

SELECT ISDATE('10/24/1960'), ISDATE('24/10/1960')|||hey brett, i got one for you in return

what do you get for this query --SELECT ISDATE('04/05/2006') as is1
, ISDATE('05/04/2006') as is2

mwua ha ha ha hahahaha !!! :) :) :) :) :)|||Good point, bottom line, you are hosed

unless you have a column that identifies the format|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)|||eh, it's not so bad. worst case you'll convert wrong and be off by 9 months. no big deal right? :)sounds like the attitude of a certain large software company which shall remain nameless...

:)|||sounds like the attitude of a certain large software company which shall remain nameless...

yea, they drill it into you, it takes a while to feel clean again. :)

did I say 9? I meant 6. even better!|||either "dd/mm/yyyy hh:mm:ss AM/PM" or "mm/dd/yyyy hh:mm:ss AM/PM" formats since the column is VARCHAR.

How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.|||How much rows are you having in your table..?

Second thing, If you query your table, how you identify dates..? (05/04/2006 - dd/mm/yyyy or 04/05/2006 - mm/dd/yyyy )

Consider the points given below, remember you didn't provide enough information...

1. You can update all rows which is having 'day' more than 12. (i.e. 13/01/2006 or 01/13/2006).

2. If you can not identify date (05/04/2006 or 04/05/2006), than date data does not make any difference to you, because in this situation you can not get correct date.

3. Inform your higher authority & update your table, this way your new data will not be wrong.

I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format. This means that the SQL syntax used within the application follows this format. Therefore, the dates are inserted in dd/mm/yyyy format. As I said, this table is old and the old application that uses this table inserts date in mm/dd/yyyy format. The old application was stupid 'coz it formats date depending on th system setting and inserting it into the table as is. My only mistake is that I should've fixed the table before I started the application. For one year now, the old and the current application is inerting date values into the table as VARCHAR instead of DATETIME. Now that I'm updating the application ('coz I've managed to create it not to be dependent on the system settings), I want to start inserting date values as DATETIME so that it would work on BETWEEN statements properly as well as using SQL Server's built in functions such as DATEDIFF, DATEADD, etc. as I'll be using SQL Server Agent to execute T-SQL commands which involves dates.|||I have exactly 82,545 rows on this table and is expected to grow for a few more days since this table is still in use by one application. Currently, this application (which I made opf course) is following the dd/mm/yyyy format.
You have to take pain to replace the VARCHAR column to DATETIME column, choose the Server idle time and do it at single shot because you don't have any other option.

There are few ways to update your DATETIME columns...

1. You can create new table & copy all data from old table to new (using DTS).
2. Add new column in the existing table & update it (you can write query for it & after updating remove old column).
3. First update rows which is having 'day' more than 12, then update other rows.
4. Don't forget to check column references.

Note : You will get ambiguous / incorrect dates (which are below 12) because you will not identify dates between 1 to 12 (date or month).

By converting VARCHAR to DATETIME column you can eliminate future incorrect / ambiguous data. You have to take this risk, else I didn't find any other solution...|||Do you have a time stamp on your data that would indicate whether the date was entered under the old system or under the new system? If so, you can update the dates with two separate statements.

Convert varchar(12) to binary(12)

I need help converting a varchar field to binary.

I have a Data Control Task that has a OLE DB Source and corresponding OLE DB Destination Data Flow Task. In the referenced source table there exists a field defined as a varchar(12), in the corresponding destination table it is defined as a binary(12). How do I perform this conversion?

I tried inserting a Data Conversion Task and assigning the new data type as byte stream[DT_BYTES] and a Length = 12, but this was a bust. Output test is as follows:

Error: 0xC020901C at DFT - Windows, OLE_SRC - Windows [1]: There was an error with output column "Payload" (40) on output "OLE DB Source Output" (12). The column status returned was: "The value could not be converted because of a potential loss of data.".

Can anyone help me out here?

Regards,

What is the SSIS data type assigned to the source column? Right-click on the data flow error and slect Edit and then go to the Metadata tab of the Data Flow Path Editor dialog.

Is it DT_WSTR or DT_STR?

My guess is that SSIS is treating your input field as a Unicode string (DT_WSTR) so each of the 12 characters is taking up 2 bytes.

Convert SQL Account to Windows Accounts + EXEC on ALL SPs

I am no DBA, but this is my task.

I have an SQL Server 2000 Database that has an "SQL Account" that has
execute permission on all Stored procedures. it is what was used by
the company. This one account is used by "all workstations".

I want to fix this and use Windows Accounts, and get rid of that SQL
Account. How do I go about adding that Windows Account permission to
all the Stored Procedures?

What I want to do is to just add several windows account then go about
removing the permission where necessary on an account by account
basis.

Any suggestions would be greatly appreciated!DaBrain (Tapplication@.gmail.com) writes:

Quote:

Originally Posted by

I am no DBA, but this is my task.
>
I have an SQL Server 2000 Database that has an "SQL Account" that has
execute permission on all Stored procedures. it is what was used by
the company. This one account is used by "all workstations".
>
I want to fix this and use Windows Accounts, and get rid of that SQL
Account. How do I go about adding that Windows Account permission to
all the Stored Procedures?
>
What I want to do is to just add several windows account then go about
removing the permission where necessary on an account by account
basis.

First thing is of course to grant access to the Windows accounts. This
can be per account, or by granting access to Windows groups. The latter
is more convenient, since it will catch all new accounts - provided that
they should have access of course!

Whatever, I recommend that you create a role, and then add all Windows
logins to that role:

exec sp_addrole 'ourrole'
exec sp_addrolemember 'ourrole', 'DOMAIN\Group'

(I may have misremembered the order of the paramerers to sp_addrolemember.)

Then you can grant access to the procedures to the role:

SELECT 'GRANT EXEC ON ' + quotename(name) + ' TO ourrole'
FROM sysobjects
WHERE xtype = 'P'

As new procedures you would have to grant acess to these as well. There
is unfortunately no way in SQL 2000 to grant exec rights in advance,
so to speak. (It is possiuble in SQL 2005, where you can grant EXEC on
schema level.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi

DaBrain, will also have to configure all the client applications to use
Windows authentication instead of SQL Server Authentication. (or instruct
the users as to how to do it).

Depending on the number of users, the application they are using, this might
be a time-c0nsuming task.

If they connect through Access XP ADPs this is a relatively simple task.

If they connect using a DSN it will be slightly more difficult.

If they connect through some custom application where the connection info is
stored in a .INI file or the registry and the developers didn't plan on
connecting with Windows authentication this might be somewhere between
difficult and impossible.

--
-Dick Christoph

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns990D92FA5FA6Yazorman@.127.0.0.1...

Quote:

Originally Posted by

DaBrain (Tapplication@.gmail.com) writes:

Quote:

Originally Posted by

>I am no DBA, but this is my task.
>>
>I have an SQL Server 2000 Database that has an "SQL Account" that has
>execute permission on all Stored procedures. it is what was used by
>the company. This one account is used by "all workstations".
>>
>I want to fix this and use Windows Accounts, and get rid of that SQL
>Account. How do I go about adding that Windows Account permission to
>all the Stored Procedures?
>>
>What I want to do is to just add several windows account then go about
>removing the permission where necessary on an account by account
>basis.

>
First thing is of course to grant access to the Windows accounts. This
can be per account, or by granting access to Windows groups. The latter
is more convenient, since it will catch all new accounts - provided that
they should have access of course!
>
Whatever, I recommend that you create a role, and then add all Windows
logins to that role:
>
exec sp_addrole 'ourrole'
exec sp_addrolemember 'ourrole', 'DOMAIN\Group'
>
(I may have misremembered the order of the paramerers to
sp_addrolemember.)
>
Then you can grant access to the procedures to the role:
>
SELECT 'GRANT EXEC ON ' + quotename(name) + ' TO ourrole'
FROM sysobjects
WHERE xtype = 'P'
>
As new procedures you would have to grant acess to these as well. There
is unfortunately no way in SQL 2000 to grant exec rights in advance,
so to speak. (It is possiuble in SQL 2005, where you can grant EXEC on
schema level.)
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Convert Date to String

Hi all,
I have a bit of a task...
We are hitting an old VMS system with a sql statement through an
Intersystems Cache ODBC driver (very old - not the most recent driver - not
upgrade-able).
I'm firing the query through a DTS.
In leighmans (sp?) I seem to have to use very "simple" queries in order to
not create an error message - anything a bit "clever" seems to kill it.
I need to query the database using a date, however, the company that wrote
the database in the first place stored the date as a string in this format:
ccyymmdd (20060424 for example).
I need to get the current date - less 2 days - and then convert it to that
format in order to use it in the sql statement. My problem seems to be that
I dont get the ' ' around the value and therefore it never works - throws a
very unhelpful error message up (just a long number with a minus sign in
front of it).
Can anyone shed any light on this at all for me - I appreciate the above is
a bit vague...
I've tried using parameters but that didn't work either...
If I hard code in the date it works fine - ie... WHERE their_date_column =
'20060424'
Any help would be appreciated.
Regards
RobCan you use the following code without it 'killing' it?
Select ... WHERE their_date_column = convert(char(8),getdate() -2,112)
HTH
Adam
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> Hi all,
> I have a bit of a task...
> We are hitting an old VMS system with a sql statement through an
> Intersystems Cache ODBC driver (very old - not the most recent driver - no
t
> upgrade-able).
> I'm firing the query through a DTS.
> In leighmans (sp?) I seem to have to use very "simple" queries in order to
> not create an error message - anything a bit "clever" seems to kill it.
> I need to query the database using a date, however, the company that wrote
> the database in the first place stored the date as a string in this format
:
> ccyymmdd (20060424 for example).
> I need to get the current date - less 2 days - and then convert it to that
> format in order to use it in the sql statement. My problem seems to be th
at
> I dont get the ' ' around the value and therefore it never works - throws
a
> very unhelpful error message up (just a long number with a minus sign in
> front of it).
> Can anyone shed any light on this at all for me - I appreciate the above i
s
> a bit vague...
> I've tried using parameters but that didn't work either...
> If I hard code in the date it works fine - ie... WHERE their_date_column
=
> '20060424'
> Any help would be appreciated.
> Regards
> Rob
>
>|||"Adam Warne" wrote ...

> Can you use the following code without it 'killing' it?
> Select ... WHERE their_date_column = convert(char(8),getdate() -2,112)
Hi Adam,
Many thanks for your reply - unfortunately it would seem not...
The "standard" error message I seem to get whenever I try something that
doesn't work is as follows:
HResult of 0x80040e14 (-2147217900) returned
Unexpected error occurred. An error result was returned without an error
message.
I think it has problems using what I would call "sql server" functions via
the Cache stuff - but I dont know for sure...
My previous idea was to do the "clever" bit - ie the above and slap the
result into another table - then use a
WHERE their_date_column IN (SELECT myNewDate FROM myNewTable)
etc...problem then is that I cant use a linked server in the query, I guess
this is something to do with the DTS - when I click on the "black bar" that
joins the object in the designer its obvious that its connecting to the
remote database and therefore I think I'm limited as to what else I can
connect to.
Originally we wanted to do all of this in .net - but we had major problems
connecting to the source - after 3 months we managed it with a dts, and 2
months and we were able to query some tables, 1 more month and we're where
we are now!
I can get ALL data but its like 2 years worth and takes over 30 minutes to
import - the information being "got" is from a Pharmacy in a hospital and we
need the results to appear on a web based report in more of a "real-time"
fashion - ie, not running every 1 hours as I currently have it (sometimes it
takes longer than 30 minutes , some times less)..
Regards
Rob|||OK Rob, I'm clutching at straws now ;-) How about
declare @.sqlstring nvarchar(300)
select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
'''' + convert(char(8),getdate() -2,112) + ''''
exec (@.sqlstring)
This will build the query as
SELECT * FROM UserTable WHERE their_date_column = '20060422'
and then execute it.
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> "Adam Warne" wrote ...
>
> Hi Adam,
> Many thanks for your reply - unfortunately it would seem not...
> The "standard" error message I seem to get whenever I try something that
> doesn't work is as follows:
> HResult of 0x80040e14 (-2147217900) returned
> Unexpected error occurred. An error result was returned without an error
> message.
>
> I think it has problems using what I would call "sql server" functions via
> the Cache stuff - but I dont know for sure...
> My previous idea was to do the "clever" bit - ie the above and slap the
> result into another table - then use a
> WHERE their_date_column IN (SELECT myNewDate FROM myNewTable)
> etc...problem then is that I cant use a linked server in the query, I gues
s
> this is something to do with the DTS - when I click on the "black bar" tha
t
> joins the object in the designer its obvious that its connecting to the
> remote database and therefore I think I'm limited as to what else I can
> connect to.
> Originally we wanted to do all of this in .net - but we had major problems
> connecting to the source - after 3 months we managed it with a dts, and 2
> months and we were able to query some tables, 1 more month and we're where
> we are now!
> I can get ALL data but its like 2 years worth and takes over 30 minutes to
> import - the information being "got" is from a Pharmacy in a hospital and
we
> need the results to appear on a web based report in more of a "real-time"
> fashion - ie, not running every 1 hours as I currently have it (sometimes
it
> takes longer than 30 minutes , some times less)..
> Regards
> Rob
>
>|||"Adam Warne" wrote ...

> OK Rob, I'm clutching at straws now ;-) How about
> declare @.sqlstring nvarchar(300)
> select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
> '''' + convert(char(8),getdate() -2,112) + ''''
> exec (@.sqlstring)
> This will build the query as
> SELECT * FROM UserTable WHERE their_date_column = '20060422'
> and then execute it.
Hi Adam,
Thanks again for the reply.
We tried something like this on Friday - albeit that the date building bit
was different (about 5 times as long :oD) and both that one, and your
example above fail with the same error as earlier :o/
We also tried it without the date creation bit and simply put a hard coded
date in there (in the right format) - but it still didn't work - obviously
doesn't like the parameter stuff...
Any more thoughts? It's getting to the point where I'll be setting up a
reminder in Outlook to go and advance a hard coded date by 1 month each
month :o(
Rob|||No worries Rob ... problem shared and all that :-)
Did you say you were using a DTS package? If this is the case have you
looked at the possibility of using a Global variable? This means you can us
e
a '?' in place of the date and set the ? to a global variable that you can
set up though the 'Package Properties'.
The variable can be checked and changed every time it runs.
If this sounds feasible and you want more info, let me know. If this
doesn't sound like a possibility let me know anyway and I'll see if I can up
my brain a gear! ;-)
Cheers
Adam
--
Adam J Warne, MCDBA
"Rob Meade" wrote:

> "Adam Warne" wrote ...
>
> Hi Adam,
> Thanks again for the reply.
> We tried something like this on Friday - albeit that the date building bit
> was different (about 5 times as long :oD) and both that one, and your
> example above fail with the same error as earlier :o/
> We also tried it without the date creation bit and simply put a hard coded
> date in there (in the right format) - but it still didn't work - obviously
> doesn't like the parameter stuff...
> Any more thoughts? It's getting to the point where I'll be setting up a
> reminder in Outlook to go and advance a hard coded date by 1 month each
> month :o(
> Rob
>
>|||On Mon, 24 Apr 2006 09:35:16 +0100, Rob Meade wrote:
(snip)
>In leighmans (sp?) I seem to have to use very "simple" queries in order to
>not create an error message - anything a bit "clever" seems to kill it.
Hi Rob,
Can you call user-defined functions? Or execute stored procedures? Both
might be able to work around your problem.
Hugo Kornelis, SQL Server MVP|||"Adam Warne" wrote ...

> No worries Rob ... problem shared and all that :-)
> Did you say you were using a DTS package? If this is the case have you
> looked at the possibility of using a Global variable? This means you can
> use
> a '?' in place of the date and set the ? to a global variable that you can
> set up though the 'Package Properties'.
> The variable can be checked and changed every time it runs.
> If this sounds feasible and you want more info, let me know. If this
> doesn't sound like a possibility let me know anyway and I'll see if I can
> up
> my brain a gear! ;-)
Hi Adam,
Many thanks for the reply.
I dont know whether or not this will work but am more than happy to give it
a try.
Any help would be appreciated - and yes - its via a DTS.
Regards
Rob|||"Hugo Kornelis" wrote ...

> Can you call user-defined functions? Or execute stored procedures? Both
> might be able to work around your problem.
Hi Hugo,
Thanks for the reply.
I dont think so - in the "transformation" part of the DTS I have a SQL Query
box to enter the sql statement - the problems seems to be that because its
source is the remote machine, I cant use linked servers or anything like
that - so unless the tables exist on the remote database I cant use them -
the same is true of SP's...|||"Adam Warne" wrote:

> OK Rob, I'm clutching at straws now ;-) How about
> declare @.sqlstring nvarchar(300)
> select @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
> '''' + convert(char(8),getdate() -2,112) + ''''
> exec (@.sqlstring)
> This will build the query as
> SELECT * FROM UserTable WHERE their_date_column = '20060422'
> and then execute it.
> --
> Adam J Warne, MCDBA
Essentially the same thing, but maybe try one of the following variations of
the above solution:
SELECT @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
CHAR(39) + CONVERT(CHAR(8), GETDATE() -2, 112) + CHAR(39)
SELECT @.sqlstring = 'SELECT * FROM UserTable WHERE their_date_column = ' +
QUOTENAME( CONVERT(CHAR(8), GETDATE() -2, 112), '''' )
Cheers,
dave