converting decimal to time

how do you convert a numeric to time format if it shows hours but a decimal figure for Minutes. For example if I have hours in decimal format like this

28.5000

but I want to show it in this format:

28:30:00

where 28 = hours, 30 = minutes, 00 = seconds

Thanks.This is a presentation issue. It should be handled at the client, not the server. Dealing with formatting inside the database is just a recipe for problems later.

-PatP|||What Pat said

DECLARE @.hours decimal(15,4)
SELECT @.hours = 28.5
SELECT RIGHT('00' + CONVERT(varchar(2),FLOOR(@.hours)),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60))),2)
+':'
+ RIGHT('00' + CONVERT(varchar(2),FLOOR(((@.hours-FLOOR(@.hours))*60)-FLOOR(((@.hours-FLOOR(@.hours))*60)))*60),2)|||Displaying it as 28:30:00 is a presentation issue, but converting it to a valid datetime format falls within the scope of the database server:declare @.Hours decimal (6, 4)
set @.Hours = 28.5
select dateadd(minute, @.Hours * 60, 0)|||I was gonna give them that, but I realized it wasn't what they asked for...

Hours of what BTW...sounds like derived data gotta be careful with that

Converting datetime to integer and back

Hi all,

I have a problem converting datetime to integer (and than back to
datetime).
Depending whether the time is AM or PM, same date is converted to two
different integer representations, which holds as true on reversal
back to datetime.

AM Example:

declare @.DI integer; declare @.DD datetime
set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
set @.DD = cast (@.DI as datetime)
print @.DI; print @.DD

Result:
37690
Mar 12 2003 12:00AM

PM Example:

declare @.DI integer; declare @.DD datetime
set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
set @.DD = cast (@.DI as datetime)
print @.DI; print @.DD

Result:
37691
Mar 13 2003 12:00AM

Now, this is not a big problem if I knew that this is how it is
supposed to work. Is this how SQL Server is supposed to work?Nikola (nigel35@.hotmail.com) writes:
> AM Example:
> declare @.DI integer; declare @.DD datetime
> set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
> set @.DD = cast (@.DI as datetime)
> print @.DI; print @.DD
> Result:
> 37690
> Mar 12 2003 12:00AM
> PM Example:
> declare @.DI integer; declare @.DD datetime
> set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
> set @.DD = cast (@.DI as datetime)
> print @.DI; print @.DD
> Result:
> 37691
> Mar 13 2003 12:00AM
> Now, this is not a big problem if I knew that this is how it is
> supposed to work. Is this how SQL Server is supposed to work?

Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
say this makes much sense to me.

Then again, I have to admit that I don't really see the point with
converting datetime values to integer.

In any case, the workaround should be simple, first chop of the
time portion.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote:
> Nikola (nigel35@.hotmail.com) writes:
> > AM Example:
> > declare @.DI integer; declare @.DD datetime
> > set @.DI = cast(cast('3/12/2003 11:34:02 AM' as datetime) as integer)
> > set @.DD = cast (@.DI as datetime)
> > print @.DI; print @.DD
> > Result:
> > 37690
> > Mar 12 2003 12:00AM
> > PM Example:
> > declare @.DI integer; declare @.DD datetime
> > set @.DI = cast(cast('3/12/2003 11:34:02 PM' as datetime) as integer)
> > set @.DD = cast (@.DI as datetime)
> > print @.DI; print @.DD
> > Result:
> > 37691
> > Mar 13 2003 12:00AM
> > Now, this is not a big problem if I knew that this is how it is
> > supposed to work. Is this how SQL Server is supposed to work?
> Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
> say this makes much sense to me.
> Then again, I have to admit that I don't really see the point with
> converting datetime values to integer.
> In any case, the workaround should be simple, first chop of the
> time portion.

VB6 will allow a similar translation and it has the same problem: a real
number is returned where the fractional (i.e. right of the decimal point)
part represents the time. So, converting from datetime to an int carries a
hidden conversion that rounds to get the integer. Check this out (I used
money, although I assume float or real would suffice).

declare @.d datetime
declare @.n money

set @.d = '3/12/2003'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/12/2003 11:34 AM'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/12/2003 11:34 PM'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

set @.d = '3/13/2003'
set @.n = convert(money, @.d)
print convert(varchar, @.n) + ' - ' + convert(varchar, @.d)

Craig|||Erland Sommarskog (sommar@.algonet.se) writes:
> Apparently, SQL Server rounds to the nearest wholest int. I wouldn't
> say this makes much sense to me.
> Then again, I have to admit that I don't really see the point with
> converting datetime values to integer.
> In any case, the workaround should be simple, first chop of the
> time portion.

Actually there is an even simpler workaround:

declare @.d datetime
declare @.i int

SELECT @.d = '20020202 11:59:00'
SELECT @.i = convert(float, @.d)
SELECT @.i

SELECT @.d = '20020202 12:01:00'
SELECT @.i = convert(float, @.d)
SELECT @.i

This works, because when convering from float to int, truncation occurs...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsqlsql

converting date/time to just date?

I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).

What I want is to do the following:

Say you have these records:

person | date-time
---+--------
jim | 06/02/2004 00:05:52
jim | 06/02/2004 05:06:21
jim | 06/02/2004 05:46:21
jim | 06/15/2004 11:26:21
jim | 06/15/2004 11:35:21
dave | 06/04/2004 09:35:21
dave | 06/04/2004 11:05:21
dave | 06/06/2004 10:34:21
dave | 06/08/2004 11:37:21

I'd like the results to count how many days and return

person | days
---+---
jim | 2
dave | 3

How would I do this?

--
[ Sugapablo ]
[ http://www.sugapablo.com <--music ]
[ http://www.sugapablo.net <--personal ]
[ sugapablo@.12jabber.com <--jabber IM ]On Tue, 22 Jun 2004 15:27:52 -0000, Sugapablo wrote:

>I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).
>What I want is to do the following:
>Say you have these records:
>person | date-time
>---+--------
>jim | 06/02/2004 00:05:52
>jim | 06/02/2004 05:06:21
>jim | 06/02/2004 05:46:21
>jim | 06/15/2004 11:26:21
>jim | 06/15/2004 11:35:21
>dave | 06/04/2004 09:35:21
>dave | 06/04/2004 11:05:21
>dave | 06/06/2004 10:34:21
>dave | 06/08/2004 11:37:21
>I'd like the results to count how many days and return
>person | days
>---+---
>jim | 2
>dave | 3
>How would I do this?

Hi Sugapablo,

SELECT person,
COUNT(DISTINCT CONVERT(CHAR(8), date-time, 114)) AS days
FROM YourTable
GROUP BY person
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)sqlsql

converting date time field

Hi,

I'm trying to report on the time records are created , but not to include the date. So for a month long period, I want to know how many records have been created between 8am & 9am. I can group the records and display by hour, but as the database is a time date field, it displays for each date as well.

I think I probably need to create a formula that will strip out the date information, then I can group by hour and that will return what I need, but I have no idea how create such a formula...

Any ideas?

Thanks,

Matt.What is your Database?
Write a Stored Procedure having the query group by Time and use that sp to design the report

Converting Data Types

I have a table with a field of Char(10) Data type

this field contains records of work time Attendance in a decimal format

Ex. I attend today for 8.30 this means eighthours and thirty mintutes

the main problem faced me to make some calculations on those records (sum, subtract, etc)

so I want to convert the data from char type to decimal or real using the next code but it doesn't work

select (cast (satreg, decimal) + cast (satot, decimal)) from timecard

can u please help me in the main issue how to sum char type records or at least how to convert them to decimal

Regards

create table test

(

time char(10)

)

insert into test (time) values ('8.30')

insert into test (time) values ('9.30')

insert into test (time) values ('10.30')

selectconvert(decimal(10,2), time )

fromtest|||

this can help:

it breaks your 8.30 to 8 and 30

SELECT substring(test,0, (patindex('%.%',test))),substring(test,(patindex('%.%',test)+1),len(test)) from test

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?
What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER COLUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegro ups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER COLUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER CO
LUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Convert when executing query

When I am executing a simple select query in a database with correct index set it takes a long time (over 30 secunds) to execute. In another database with an identical table it runs on less then one second.
The differense I can find is that when I check execution plan on the slow database I get the arguments of the index scan to "convert([table].[column])=Convert([@.1]) and i don't get this in the other database. Does anyone have any idea on why?
Best Regards HalTry updating statistics and recreating indexes on your slow database.

blindman|||I have the update statistics automaticly set to true. Should I do that anyway?
//Hal

Originally posted by blindman
Try updating statistics and recreating indexes on your slow database.

blindman

Convert varchar to date time

How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
Exp. Convert 31/12/04 to 2004-12-31Arief
DECLARE @.dt AS VARCHAR(20)
SET @.dt='31/12/04'
SELECT CAST(RTRIM(y*10000+m*100+d)AS DATETIME)
FROM
(
SELECT '20'+SUBSTRING(@.dt,7,2)AS y,
SUBSTRING(@.dt,4,2)AS m
,SUBSTRING(@.dt,1,2)AS d
) AS D
"Arief" <a_nursetyawan@.yahoo.com> wrote in message
news:1121409145.447241.152520@.o13g2000cwo.googlegroups.com...
> How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
> Exp. Convert 31/12/04 to 2004-12-31
>|||Datetime data doesn't have any format, it is the client application that for
mats the data. If you
want to convert to datetime:
SELECT CONVERT(datetime, '23/07/98', 3)
And if you want above to be returned in some special format, you need to con
vert back to a string:
SELECT CONVERT(char(10), CONVERT(datetime, '23/07/98', 3), 120)
For more information, see:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arief" <a_nursetyawan@.yahoo.com> wrote in message
news:1121409145.447241.152520@.o13g2000cwo.googlegroups.com...
> How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
> Exp. Convert 31/12/04 to 2004-12-31
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234WWz6QiFHA.320@.TK2MSFTNGP09.phx.gbl...
> Datetime data doesn't have any format, it is the client application that
formats the data. If you
> want to convert to datetime:
> SELECT CONVERT(datetime, '23/07/98', 3)
> And if you want above to be returned in some special format, you need to
convert back to a string:[vbcol=seagreen]
> SELECT CONVERT(char(10), CONVERT(datetime, '23/07/98', 3), 120)
> For more information, see:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Arief" <a_nursetyawan@.yahoo.com> wrote in message
> news:1121409145.447241.152520@.o13g2000cwo.googlegroups.com...
@.todaysdate varchar (25)
AS
declare @.month varchar(10)
select @.month =datepart(mm,@.todaysdate)
declare @.day varchar(10)
select @.day =datepart(dd,@.todaysdate)
declare @.year varchar(10)
select @.year =datepart(yyyy,@.todaysdate)
select @.todaysdate = @.year +'/' + @.month + '/' + @.day
long winded i guess but itll work ?

Convert varchar to date time

How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
Exp. Convert 31/12/04 to 2004-12-31Arief
DECLARE @.dt AS VARCHAR(20)
SET @.dt='31/12/04'
SELECT CAST(RTRIM(y*10000+m*100+d)AS DATETIME)
FROM
(
SELECT '20'+SUBSTRING(@.dt,7,2)AS y,
SUBSTRING(@.dt,4,2)AS m
,SUBSTRING(@.dt,1,2)AS d
) AS D
"Arief" <a_nursetyawan@.yahoo.com> wrote in message
news:1121409145.447241.152520@.o13g2000cwo.googlegroups.com...
> How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
> Exp. Convert 31/12/04 to 2004-12-31
>|||Datetime data doesn't have any format, it is the client application that formats the data. If you
want to convert to datetime:
SELECT CONVERT(datetime, '23/07/98', 3)
And if you want above to be returned in some special format, you need to convert back to a string:
SELECT CONVERT(char(10), CONVERT(datetime, '23/07/98', 3), 120)
For more information, see:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arief" <a_nursetyawan@.yahoo.com> wrote in message
news:1121409145.447241.152520@.o13g2000cwo.googlegroups.com...
> How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
> Exp. Convert 31/12/04 to 2004-12-31
>

Convert varchar to date time

How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
Exp. Convert 31/12/04 to 2004-12-31
Arief
DECLARE @.dt AS VARCHAR(20)
SET @.dt='31/12/04'
SELECT CAST(RTRIM(y*10000+m*100+d)AS DATETIME)
FROM
(
SELECT '20'+SUBSTRING(@.dt,7,2)AS y,
SUBSTRING(@.dt,4,2)AS m
,SUBSTRING(@.dt,1,2)AS d
) AS D
"Arief" <a_nursetyawan@.yahoo.com> wrote in message
news:1121409145.447241.152520@.o13g2000cwo.googlegr oups.com...
> How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
> Exp. Convert 31/12/04 to 2004-12-31
>
|||Datetime data doesn't have any format, it is the client application that formats the data. If you
want to convert to datetime:
SELECT CONVERT(datetime, '23/07/98', 3)
And if you want above to be returned in some special format, you need to convert back to a string:
SELECT CONVERT(char(10), CONVERT(datetime, '23/07/98', 3), 120)
For more information, see:
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Arief" <a_nursetyawan@.yahoo.com> wrote in message
news:1121409145.447241.152520@.o13g2000cwo.googlegr oups.com...
> How to Convert VarChar 'dd/mm/yy' to DateTime yyyy-mm-dd
> Exp. Convert 31/12/04 to 2004-12-31
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234WWz6QiFHA.320@.TK2MSFTNGP09.phx.gbl...
> Datetime data doesn't have any format, it is the client application that
formats the data. If you
> want to convert to datetime:
> SELECT CONVERT(datetime, '23/07/98', 3)
> And if you want above to be returned in some special format, you need to
convert back to a string:[vbcol=seagreen]
> SELECT CONVERT(char(10), CONVERT(datetime, '23/07/98', 3), 120)
> For more information, see:
> http://www.karaszi.com/SQLServer/info_datetime.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Arief" <a_nursetyawan@.yahoo.com> wrote in message
> news:1121409145.447241.152520@.o13g2000cwo.googlegr oups.com...
@.todaysdate varchar (25)
AS
declare @.month varchar(10)
select @.month =datepart(mm,@.todaysdate)
declare @.day varchar(10)
select @.day =datepart(dd,@.todaysdate)
declare @.year varchar(10)
select @.year =datepart(yyyy,@.todaysdate)
select @.todaysdate = @.year +'/' + @.month + '/' + @.day
long winded i guess but itll work ?

Convert varchar to 12 hour time format

A column stores the time as varchar 24 hour format. Is there a way to
change the data using the convert function to 12 hour format from the
sql select statement? Thanks.>A column stores the time as varchar 24 hour format.
Why are you storing time as a varchar? Wouldn't datetime make more sense?
>A column stores the time as varchar 24 hour format. Is there a way to
> change the data using the convert function to 12 hour format from the
> sql select statement?
SELECT [12 hour format] = LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
CONVERT(DATETIME, varchar_column), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
CONVERT(DATETIME, varchar_column), 22), 3))
FROM table
WHERE ISDATE(varchar_column) = 1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Convert varchar to 12 hour time format

A column stores the time as varchar 24 hour format. Is there a way to
change the data using the convert function to 12 hour format from the
sql select statement? Thanks.>A column stores the time as varchar 24 hour format.
Why are you storing time as a varchar? Wouldn't datetime make more sense?

>A column stores the time as varchar 24 hour format. Is there a way to
> change the data using the convert function to 12 hour format from the
> sql select statement?
SELECT [12 hour format] = LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
CONVERT(DATETIME, varchar_column), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
CONVERT(DATETIME, varchar_column), 22), 3))
FROM table
WHERE ISDATE(varchar_column) = 1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Convert varchar to 12 hour time format

A column stores the time as varchar 24 hour format. Is there a way to
change the data using the convert function to 12 hour format from the
sql select statement? Thanks.
>A column stores the time as varchar 24 hour format.
Why are you storing time as a varchar? Wouldn't datetime make more sense?

>A column stores the time as varchar 24 hour format. Is there a way to
> change the data using the convert function to 12 hour format from the
> sql select statement?
SELECT [12 hour format] = LTRIM(SUBSTRING(CONVERT(VARCHAR(20),
CONVERT(DATETIME, varchar_column), 22), 10, 5) + RIGHT(CONVERT(VARCHAR(20),
CONVERT(DATETIME, varchar_column), 22), 3))
FROM table
WHERE ISDATE(varchar_column) = 1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

|||I have the same question as KoryS. Anyone have an answer?

Thanks.

Convert UTC time to local time

Hello,

I am new with the reporting services. I am creating a report and I need to display date/time on the report. But the servers stores those date/time in UTC. How can I convert them to the local time in my report.

Thanks for your help.

You need to use System.TimeZone.ToLocalTime(UTCTime).

See http://msdn2.microsoft.com/en-us/library/system.timezone.tolocaltime.aspx.

You should be able to use TimeZone.CurrentTimeZone if you want to convert using the server time zone.

|||

Hello,

i tried this tip with no luck.

I used the expression = System.TimeZone.ToLocalTime(!Fields.DateTime.Value) in one of my cells and got an BC30469 error.

|||

Try this instead:
=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!DateTime.Value)

-- Robert

|||

Robert,

thanks alot. Works like a charm.

|||

This works for conversion based on the time zone of the report server, but not the client. Is that correct? I tested this by using the function in a textbox on the report that I deployed to the report server. Then on my workstation PC, I changed my timezone and viewed the report. The time in the report still reflected the time on the report server (converted from the UTC time of course).

How do you change the dates in the reports dynamically based on the area of the country someone opens the report? Because the report renders as HTML first before being delivered to the client, does this mean it will always use the report server time zone?

Thanks

-Kory

Convert to different date time format

Hi,
I want to convert following datetime value '2001-09-15 00:00:00.000'
to
'2001-09-15'
and the time part to be converted to respective 'AM, PM' Value
How can I do that?Better to do the formatting in the client side.
select
convert(varchar(10), getdate(), 126) + ' ' + right(convert(varchar(35),
getdate(), 109), 14)
go
AMB
"dotnettester" wrote:

> Hi,
> I want to convert following datetime value '2001-09-15 00:00:00.000'
> to
> '2001-09-15'
> and the time part to be converted to respective 'AM, PM' Value
> How can I do that?
>|||On Tue, 23 Aug 2005 13:57:01 -0700, dotnettester wrote:

>Hi,
>I want to convert following datetime value '2001-09-15 00:00:00.000'
>to
>'2001-09-15'
>and the time part to be converted to respective 'AM, PM' Value
>How can I do that?
Hi dotnettester,
http://www.karaszi.com/SQLServer/info_datetime.asp
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Convert to DateTime

I have a date filed 12/26/2006 and a time field 7:00am. How can I combine them in my select statement and get a DateTime field.

declare @.v1 varchar(20)
select @.v1 ='12/26/2006'

declare @.v2 varchar(20)
select @.v2 ='7:00am'

select convert(datetime,@.v1 + ' ' + @.v2)

but is safer to use the YYYYMMDD format for dates

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Convert to DateTime

I have a date filed 12/26/2006 and a time field 7:00am. How can I combine them in my select statement and get a DateTime field.

Using SQL Server?

SELECT CONVERT(VARCHAR(10), MyDateField) + Convert(VARCHAR(12), MyTimeField) As MyDateTimeField
FROM MyTable

|||

I forgot to add a space between the date and time:

SELECT CONVERT(VARCHAR(10), MyDateField) + ' ' + CONVERT(VARCHAR(12), MyTimeField) As MyDateTimeField
FROM MyTable

|||

What are exact definitions of those fields in database?