format time datatype in stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

format time datatype in stored procedure

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2009-07-04 : 13:15:18

I have a time(0) datatype in sql 2008. In a stored procedure how would I format it to display it without the seconds and as non-military time?

I am using it in a Crystal Report in .Net. It does not show in crystal as a datetime field so I cannot use the embedded formatting of crystal so I want format it within the sp.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-04 : 14:28:53

RIGHT(CONVERT(VARCHAR(30), Col1, 101), 7)

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

smh
Yak Posting Veteran

94 Posts

Posted - 2009-07-04 : 20:20:15

Hi, thanks for responding. I receive the error:

Error converting data type time to varchar in the stored procedure.

smh
Yak Posting Veteran

94 Posts

Posted - 2009-07-04 : 22:00:43

Well, the only thing near to solving the problem that I have found is to use:

select cast(tblReceivingManifestHeader.TimeReceived as smalldatetime) as TimeReceived from tblReceivingManifestHeader

which returns 1900-01-01 14:35:00 but now it is a datetime type that perhaps crystal can handle.

This seems like going backwards and it is now depending upon Crystal to do the formatting.

Is there another way?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-05 : 03:11:13

Look for an update for Crystal Reports to handle the new datatypes available in SQL Server 2008?

Microsoft SQL Server MVP

N 56°04'39.26"
E 12°55'05.63"

smh
Yak Posting Veteran

94 Posts

Posted - 2009-07-05 : 12:25:57

Since I have the most recent vb.net 2008 in which the crystal report is bundled (It is not the stand alone version of CR), I tend to doubt if that is the problem, but I will look into it.

Butwhat I would like to know is whether it is possible to format the Time(0) column within the stored procedure itself so that it is not military time and does not include seconds.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-05 : 20:01:33

[code]
select
New_Time = convert(varchar(5),a.MyTime,108),
a.MyTime
from
( --Test Data
select MyTime= convert(time,getdate()) union all
select '23:23:59.59999'
) a

Results:
New_Time MyTime
-------- ----------------
19:59 19:59:52.4830000
23:23 23:23:59.5999900

(2 row(s) affected)

[/code]

CODO ERGO SUM

smh
Yak Posting Veteran

94 Posts

Posted - 2009-07-05 : 21:32:24

Thanks, this removes the seconds. Is there any way to change it to non-military, except by a case statement?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-06 : 09:40:44

It might be helpful if you provided an example of exactly the format you want to see.

CODO ERGO SUM

NeilG
Aged Yak Warrior

530 Posts

Posted - 2009-07-06 : 10:16:29

yes what is the format that you are requiring presume your not after the select convert(varchar(23),getdate(),103) type being from america

smh
Yak Posting Veteran

94 Posts

Posted - 2009-07-06 : 10:18:00

16:30 = 4:30 PM

1:23 = 1:23 AM

I am thinking that there is probably no format function to do that, so it probably has to be a string and done with a case statement. It would be nice if there were a way to automatically do this in sql however other that reverting to a case statement.

I have put a post to the crystal reports forum re: this because that is the logical place for this to happen. Otherwise I have to convert this to a datetime format in the sp and then use crystal to format it.

Thanks

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-07-06 : 11:06:19

quote:
Originally posted by smh
...I am thinking that there is probably no format function to do that...

I guess that means that you didn't bother looking in SQL Server 2008 Books Online.


select
	New_Time = convert(varchar(7),a.MyTime,100),
	a.MyTime
from
    ( --Test Data
    select MyTime= convert(time,getdate())  union all
    select '23:23:59.59999'
    ) a


Results:
New_Time MyTime           
-------- ---------------- 
11:03AM  11:03:24.3970000
11:23PM  23:23:59.5999900

(2 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources