How can I convert datetime to number of minutes

I have a column in a table that stores the number of hours a task took to do. The column TaskDuration is a datetime datatype. I need to convert the hours to something that can be summed. Does anyone how this can be done? I tried CONVERT(nvarchar(5), tblTasks.TaskDuration, 108) but of course the nvarchar(5) cannot be summed. Maybe there is a way to convert the time portion to minutes and divide it by 60, anyway if someone can offer some help I appreciate it.

Try something like this

(datepart(hh, tblTasks.TaskDuration) * 60) + datepart(mm, tblTasks.TaskDuration)

|||

I tried this and it will return the number of minutes for the hours; however, the Parenthesis will not stay around the (datepart(hh, tblTasks.TaskDuration) * 60) in the view. So the (mm) are not being added.

Well it is adding time for the minutes but 30 is calculating to 10, so 03:30 is returning 190 minutes and it should be 210.

Any ideas?

|||I gave you the wrong datepart signifier, try datepart(n, tblTasks.TaskDuration)|||

Ok, so now that I have the number of minutes, can I convert this to hours and minutes. What I mean is the reporting tool needs a numeric column to sum on, so 03:15 needs to be 3.25.

Is this possible?

|||

I tried and it looks to be returning the correct format. If you have any comments, I appreciate them.

CONVERT (FLOAT, DATEPART(hh, dbo.tblVolunteerTasks.VTaskDuration) * 60 + DATEPART(n, dbo.tblVolunteerTasks.VTaskDuration)) / 60

How can i change collation of userdefined datatype?

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query

varcharUzun = varchar(100) --> userdefined data type....

[code]ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL [/code]

when i run the query it says.
[code]
Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.
[/code]

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

Hi Cem,

I don't htink you can set collation for a column that is using USER DEFINED DATATYPE.

If you do need to use the collation, just use the system datatype varchar(100), instead of user-defined, on that column.

regards

Jag

|||

Thanks Jag

but i have to use user defined datatypes.

No way to change the COLLATION of TYPES..... i couldnt find any ALTER TYPE xxxx COLLATE = xxx :(

And also how can i change the collation of PrimaryKey (varchar(10))....

2 Main problems:(

cemuney

|||

Hi Cem,

I dont think you can change the collation for user defined data type.

To change collation for a particular column try the following:

ALTER TABLE <tableName>

ALTER COLUMN <columnName> VARCHAR(10) COLLATE <collationName>

Let me know how you got on.

regards

Jag

|||

Hi Jag

i understant that UDDType do not have collations itself.

it is coming from the DATABASE's default collation.

When i change the COLLATION of database, also UDDType's collation also changes.

thanks anyway.

How can i change collation of userdefined datatype?

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query

varcharUzun = varchar(100) --> userdefined data type....

[code]ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL [/code]

when i run the query it says.
[code]
Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.
[/code]

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

Hi Cem,

I don't htink you can set collation for a column that is using USER DEFINED DATATYPE.

If you do need to use the collation, just use the system datatype varchar(100), instead of user-defined, on that column.

regards

Jag

|||

Thanks Jag

but i have to use user defined datatypes.

No way to change the COLLATION of TYPES..... i couldnt find any ALTER TYPE xxxx COLLATE = xxx :(

And also how can i change the collation of PrimaryKey (varchar(10))....

2 Main problems:(

cemuney

|||

Hi Cem,

I dont think you can change the collation for user defined data type.

To change collation for a particular column try the following:

ALTER TABLE <tableName>

ALTER COLUMN <columnName> VARCHAR(10) COLLATE <collationName>

Let me know how you got on.

regards

Jag

|||

Hi Jag

i understant that UDDType do not have collations itself.

it is coming from the DATABASE's default collation.

When i change the COLLATION of database, also UDDType's collation also changes.

thanks anyway.

sql

How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:
> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
> >I made a mistake when I first created the column and just found out when I
> > tried to calculate. I tried using the design feature on Enterprise
> > Manager,
> > but got an error message that this couldn't be done. Any help is deeply
> > appreciate.
>
>

How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.
Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.
|||Thank you, Aaaron. I will use what you provided and make the change. Thanks
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:

> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>

How can I change a column with datatype "text" to "int"?

I made a mistake when I first created the column and just found out when I
tried to calculate. I tried using the design feature on Enterprise Manager,
but got an error message that this couldn't be done. Any help is deeply
appreciate.Don't use Enterprise Manager for this. Open Query Analyzer and connect to
the correct database.
-- add a new column to the table;
ALTER TABLE tablename ADD temp_column INT;
-- copy the data from the text column;
UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column)
= 1;
-- drop the text column;
ALTER TABLE tablename DROP COLUMN text_column;
-- rename the new column;
EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
Of course, you'll want to put in the correct names for tablename,
text_column, real_column, etc.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"Karolus" <Karolus@.discussions.microsoft.com> wrote in message
news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>I made a mistake when I first created the column and just found out when I
> tried to calculate. I tried using the design feature on Enterprise
> Manager,
> but got an error message that this couldn't be done. Any help is deeply
> appreciate.|||Thank you, Aaaron. I will use what you provided and make the change. Thank
s
mucho. karolus
"Aaron Bertrand [SQL Server MVP]" wrote:

> Don't use Enterprise Manager for this. Open Query Analyzer and connect to
> the correct database.
> -- add a new column to the table;
> ALTER TABLE tablename ADD temp_column INT;
> -- copy the data from the text column;
> UPDATE tablename SET temp_column = text_column WHERE ISNUMERIC(text_column
)
> = 1;
> -- drop the text column;
> ALTER TABLE tablename DROP COLUMN text_column;
> -- rename the new column;
> EXEC sp_rename 'tablename.temp_column', 'real_column', 'COLUMN';
> Of course, you'll want to put in the correct names for tablename,
> text_column, real_column, etc.
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
> "Karolus" <Karolus@.discussions.microsoft.com> wrote in message
> news:3B1473BD-7C92-4D18-80FF-F0DEE26C408F@.microsoft.com...
>
>

How can I cast a money field in a view to look like money

I have a special need in a view for a money column to look like money and still be a money datatype. So I need it to look like $100.00 (prefered) or 100.00(can make work).

If I convert like this '$' + CONVERT (NVARCHAR(12), dbo.tblpayments.Amount, 1) it is now a nvarchar and will not work for me.

How can I cast so it is still money? by default the entries look like 100.0000.

They must remain a money datatype.

Try this out.

select'$' +cast (convert (decimal ( 10 , 2 ) , <column name> )as varchar )from <table name>

Hope this will help.|||

This still ends up as a varchar, so it will not work for me.

|||

Is there a way to cast to money and have it only show as 100.00 or can I only cast to decimal(10,2) to do this?

|||

As I have mentioned in my previous, for a similar question from you, what are you doing with the values? If its just for display purpose, use front end formatting functions.

|||

I think I can use the decimal(10,2) to work with my money issue. All though I would prefer that it remain as its original datatype.

I would love to use front end formatting; however, in this case it is not possible.

|||

Then you most likely have a serious design issue.

|||

If you have something constructive to say, please do so. Until you understand someone's underlying requirements you are as ignorant as the in experienced.

|||

If I needed more information to qualify my above statement, I would have asked for it. Fortunately, I don't.

Pointing out that what you are asking for would result in a poor design and you should probably rethink your process rather than implementation is hardly unconstructive. I do however, take offense to your statement, so I will end this conversation here. Good luck on getting help when you insult those around you.

PS. inexperienced is a single word.

|||

Motley,

It was not my intent to insult. What I hope for in these cases is not to be told that I'm wrong, as much as to be offered a constructive suggestion.

hour separator from . to : ?!?!

In my SQLserver DATA datatype appear to me with hour separator "."
(12.00.00)
I want that they appear with separator ":" (12:00:00) (for to prevent ODBC
problems)
(I have changed the international setting of the server... the OS time is
changed, but in the DB I always have the ".")
Any suggestions?
Is this a dattime datatype? If so it is not stored with any type of
delimiter. Internally it is stored as two Integers and it is up to the
front end to display it however they wish. So if it's displaying with a .
you need to look at your GUI or the driver being used. You can also convert
the datetime to a string with CONVERT() to display it in many different
formats. But as a pure datetime it does not have a display format. See
CONVERT() in BOL for more details.
Andrew J. Kelly SQL MVP
"YURYSSG" <yuryssg@.yahoo.it> wrote in message
news:2qlj83F10pofdU1@.uni-berlin.de...
> In my SQLserver DATA datatype appear to me with hour separator "."
> (12.00.00)
> I want that they appear with separator ":" (12:00:00) (for to prevent
ODBC
> problems)
> (I have changed the international setting of the server... the OS time is
> changed, but in the DB I always have the ".")
> Any suggestions?
> --
>
>
|||If you are using the SQL Server ODBC driver, and you are using the driver to
convert from DATETIME to string (ie. binding the column as SQL_C_CHAR), then
you should be able to change the seperator characters using the Regional
settings on the client machine.
Brannon
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:#B$JQaZmEHA.2020@.TK2MSFTNGP09.phx.gbl...
> Is this a dattime datatype? If so it is not stored with any type of
> delimiter. Internally it is stored as two Integers and it is up to the
> front end to display it however they wish. So if it's displaying with a .
> you need to look at your GUI or the driver being used. You can also
convert[vbcol=seagreen]
> the datetime to a string with CONVERT() to display it in many different
> formats. But as a pure datetime it does not have a display format. See
> CONVERT() in BOL for more details.
> --
> Andrew J. Kelly SQL MVP
>
> "YURYSSG" <yuryssg@.yahoo.it> wrote in message
> news:2qlj83F10pofdU1@.uni-berlin.de...
> ODBC
is
>
|||In the worst case if the above do not work you can always use Replace after
you converted it to string and return it as a string.
"Brannon Jones" <brannonjNOSPAM@.gmail.com> wrote in message
news:OphvrcfmEHA.512@.TK2MSFTNGP10.phx.gbl...
> If you are using the SQL Server ODBC driver, and you are using the driver
> to
> convert from DATETIME to string (ie. binding the column as SQL_C_CHAR),
> then
> you should be able to change the seperator characters using the Regional
> settings on the client machine.
> Brannon
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:#B$JQaZmEHA.2020@.TK2MSFTNGP09.phx.gbl...
> convert
> is
>