Converting date to Varchar? and Varchar to Date?

I have a column of data in a table that has date formatted as '2006-03-26 00:00:00.000'

What T-SQL command that will alter the column so that it is now Varchar '03-26-2006'?

I also want to know how to do the opposite... if I have '03-26-2006' via command, how do I convert the column of the table to be datetime from varchar

This should give you an idea of how to handle these conversions:

DECLARE @.MyDateTimeValue datetime
SET @.MyDateTimeValue = '2006-03-26 00:00:00.000'

SELECT convert( varchar(10), @.MyDateTimeValue, 101 )

-
03/26/2006

SELECT cast( '03/26/2006' AS datetime )

2006-03-26 00:00:00.000

Converting a Oracle PL/SQL command into MS SQL

hi

I was wondering if anyone would be able to advise on converting oracle PL/SQL features into MSSQL.

For example i have the following sequence + trigger below but cant find any information on creating a sequence in MSSQL, is it possible?

create sequence a_SEQ
start with 001
increment by 1

create or replace trigger a_TG
before insert a
for each row
begin
select (concat('D',(cast(a_SEQ.nextval as varchar(4))))) into :new.a_id from dual;
end;

any links or tutorials would be great, i've got a couple of MSSQL 2005 books which do explain triggers but examples are really needed to understand the full functionality.

cheersAs it was for SQL 2000, the SQL 2005 Books Online (BOL) is for me the quickest way to research something.

The Oracle squence is one of the things I miss in SQL Server. The thing that comes close is an IDENTITY column. In your example you seem to generate an unique number for a table. So define a_id as an IDENTITY column (see CREATE TABLE in the BOL) and forget about the trigger.

Note, after rereading this, the IDENTITY column works better/simpler than a sequence :)|||hi there

yes that identity column works great and only takes a second through the GUI.

Just with oracle we were taught to insert a letter before the unique ID to help identify the tables more, (this was done using a sequence + trigger).

for example on a table called detective instead of:
ID fname sname
1 bil fish
2 fred frog
3 dave dog

It would display:
ID fname sname
D1 bil fish
D2 fred frog
D3 dave dog

This would help identify that the ID was coming from the detective table.

Do you know a way of doing something along these lines with MS SQL.

cheers|||I would say don't do it.

You know what table it's in and what column it's in

Personally I would avoid surrogate keys|||OK

cheers for the advise|||Personally I would avoid surrogate keys
Sputter...choke...cough...
...but anyway, PROPER use of surrogate keys would not require adding prefixes to indicate their location. That should be discouraged. A surrogate key should have no inherent relationship to the data it identifies.

Converting .dbf to sql server table...batch file? command line?

My client will be receiving a .dbf file which needs to be uploaded
into a sql server database table (as an append) every week. They are
NOT computer savvy and I would like to automate this process rather
than go into enterprise manager and run data transformation.

Is there any way to write a batch file or a set of command lines which
will do this?

Thanks.

Monicamonica@.datashark.net (Monica J. Braverman) wrote in message news:<d70cd025.0310081033.375fbc9b@.posting.google.com>...
> My client will be receiving a .dbf file which needs to be uploaded
> into a sql server database table (as an append) every week. They are
> NOT computer savvy and I would like to automate this process rather
> than go into enterprise manager and run data transformation.
> Is there any way to write a batch file or a set of command lines which
> will do this?
> Thanks.
> Monica
DTS package would probably be the easiest solution. Create the package
as an import from the .dbf into the approriate table and then run it
with each new .dbf received. If running DTS packages is a bit
'complex' for your users you could wrap it up in an applicaion/wizard
(eg in VB) and execute it that way.|||monica@.datashark.net (Monica J. Braverman) wrote:
>My client will be receiving a .dbf file which needs to be uploaded
>into a sql server database table (as an append) every week. They are
>NOT computer savvy and I would like to automate this process rather
>than go into enterprise manager and run data transformation.
>Is there any way to write a batch file or a set of command lines which
>will do this?

You might create a DTS package on the server, then create a batch file
to access it that calls the DTSRUN.exe program. I have a batch file
with this as the command line:
dtsrun /e /n"dtspackagename" /sSQLServerName and it works very well.
hth,
Myron|||Hi,
you can automate your process using a command prompt utility named
"bcp"; if you try to execute this command you get the syntax:

C:\>bcp
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide
character type]
[-N keep non-text native] [-V file format version] [-q quoted
identifier]
[-C code page specifier] [-t field terminator] [-r row
terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional
enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"]

Another way is to use the T-SQL command "BULK INSERT".
You can find explanations on your MS-SQL documentation.

Bye.

monica@.datashark.net (Monica J. Braverman) wrote in message news:<d70cd025.0310081033.375fbc9b@.posting.google.com>...
> My client will be receiving a .dbf file which needs to be uploaded
> into a sql server database table (as an append) every week. They are
> NOT computer savvy and I would like to automate this process rather
> than go into enterprise manager and run data transformation.
> Is there any way to write a batch file or a set of command lines which
> will do this?
> Thanks.
> Monicasqlsql

Convert string to datetime - Performance

Dear Friends,

I have a doubt relation when converting a field…

I ‘m converting a string to datetime inside a SQL command in an OLE DB Source using this:

convert(datetime,[Maturity],103) As MaturityDate

It’s better to use inside the OLE DB Source or is better to convert it with data conversion transformation?

Regards!!

Thanks

Probably just a matter of opinion. SSIS can do datatype conversions, but has to create a new column in the pipeline to hold the new type, which is a little bit inefficient. I generally don't like things "hidden" in the OLE DB Source, but in this case I think that's what I'd do.
|||

But witch of these options is more fast?!

Regards!

|||

PedroCGD wrote:

But witch of these options is more fast?!

Regards!

I think SQL would be faster. And because I hate being wrong, I tested it before I said that. I averaged the times for three runs of 20,000 rows and SQL appears 12% faster than SSIS.
|||

PedroCGD wrote:

But witch of these options is more fast?!

Regards!

Why don't you try it and tell us. You're in the best position to judge because its your data.

-Jamie

|||

I'm agree with you jamie!! :-)

What a question I made... eheheh... I can see the time in the logs of the cube!!!

Sorry...

Tomorrow I will give you the response!! thanks both!!

|||Someone help me?!|||

PedroCGD wrote:

Someone help me?!

Umm, with what? Call 911? |||

sorry was teh wrong post!!

And relating to this post, the better choice is converting the date in OLE DB SOurce.

Thanks!

|||

PedroCGD wrote:

sorry was teh wrong post!!

And relating to this post, the better choice is converting the date in OLE DB SOurce.

Thanks!

Good to knw (and as expected). Thanks Pedro.

Convert Sting to Date

Hi,
I am a Newbie to SQL and am therefore sorry if this is a stupid
question
I am trying to run the following command
SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
FROM dbo.IpPbxCDR
WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
'MMColParam' and StartTime Between '%MMColParam2%' AND
'%MMColParam3%'
I am passing the variables MMColParam from a Web page but I always get
the following error when I try to run the command
Error converting string Datetime from Charactor String.
I have checked the DB and the value is defined as DateTime entry
here is an example of Table entry 16/11/2005 18:27:24
Can anyone help me with this?
Many Thanks
IanSee if these help:
http://www.karaszi.com/SQLServer/info_datetime.asp
Guide to Datetimes
http://www.sqlservercentral.com/columnists/bsyverson/sqldatetime.asp
Datetimes
http://www.murach.com/books/sqls/article.htm
Datetime Searching
--
Andrew J. Kelly SQL MVP
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1141251396.268280.282650@.p10g2000cwp.googlegroups.com...
> Hi,
> I am a Newbie to SQL and am therefore sorry if this is a stupid
> question
>
> I am trying to run the following command
>
> SELECT OriginationNumber, DestinationNumber, StartTime, State, Duration
> FROM dbo.IpPbxCDR
> WHERE OriginationNumber Like 'MMColParam' or DestinationNumber Like
> 'MMColParam' and StartTime Between '%MMColParam2%' AND
> '%MMColParam3%'
>
> I am passing the variables MMColParam from a Web page but I always get
> the following error when I try to run the command
>
> Error converting string Datetime from Charactor String.
>
> I have checked the DB and the value is defined as DateTime entry
>
> here is an example of Table entry 16/11/2005 18:27:24
>
> Can anyone help me with this?
>
> Many Thanks
>
> Ian
>

CONVERT function use

I'll go right to the point:

I have a textbox in wich the user can seize a "money" value.

But when i'm doing the INSERT command, it fails and says I cannot implicitely convert nvarchar data (mytextbox.text) to money data.

I don't know how to handle this...

objCmd = New SqlCommand("INSERT INTO tbl_appel_service_pieces " & _
"(fld_nom_piece, fld_quantite, fld_prix, fld_description, fld_num_appel) " & _
"VALUES (@.fld_nom_piece, @.fld_quantite, @.fld_prix, @.fld_description, @.fld_num_appel)", objConn)
objCmd.Parameters.Add("@.fld_nom_piece", champsNomPiece.Text)
objCmd.Parameters.Add("@.fld_quantite", champsQuantite.Text)
objCmd.Parameters.Add("@.fld_prix", champsPrix.Text)
objCmd.Parameters.Add("@.fld_description", champsDescription.Text)
objCmd.Parameters.Add("@.fld_num_appel", champsNumAppel.Text)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
afficherPieces()

How and where to do the conversion ? On the VALUES variable ?objCmd.Parameters.Add("@.fld_nom_piece", SqlDbType.Money)
objCmd.Parameters("@.fld_nom_piece").Value = System.Convert.ToDecimal(champsNomPiece.Text)|||Thanks ! It worked well.|||I have a similar problem in that I have a text box which holds a numeric value and when I try to insert the value into the numeric field in the SQL database I get the same error message.

I have tried the suggestion above but then I get another error message saying incorrect input.

Any idea's ??

Thanks