Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

sql server 8.0 - Formatting time and numbers

Hi Experts,
In sql server, I need to format time part of the datetime field. Such as the datetime value in a column called checkintime is 2006-02-20 20:04:07. I want to format it as 20:04 (just the hour and minutes).
Also, I want to know what is the equivalent in sql server of Visual basic's "##"  format mask which, in VB6, always shows two-digit number by left-padding with 0 in case input value is a single-digit number. Thanks
0
uzairp
Asked:
uzairp
1 Solution
 
dttriCommented:
Hi,
You can use the CONVERT function to do that:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
data_type: the destination format
expression: the value you want to convert
style: apply to datetime datatype

List of style is:
Style ID                 Style Type
0 or 100            mon dd yyyy hh:miAM (or PM)
101                   mm/dd/yy
102                   yy.mm.dd
103                   dd/mm/yy
104                   dd.mm.yy
105                   dd-mm-yy
106                   dd mon yy
107                   Mon dd, yy
108                   hh:mm:ss
9 or 109            mon dd yyyy hh:mi:ss:mmmAM (or PM)
110                   mm-dd-yy
111                   yy/mm/dd
112                   yymmdd
13 or 113          dd mon yyyy hh:mm:ss:mmm(24h)
114                   h:mi:ss:mmm(24h)
20 or 120          yyyy-mm-dd hh:mi:ss(24h)
21 or 121          yyyy-mm-dd hh:mi:ss.mmm(24h)
126                  yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
130                  dd mon yyyy hh:mi:ss:mmmAM
131                  dd/mm/yy hh:mi:ss:mmmAM

Eg: convert(varchar(5),DateColumn,108) to display only the time part, vachar(5) to get only the first 5 characters (hour and minute)
0
 
imperial_p79Commented:
declare @test datetime
set @test = '2006-02-20 20:04:07'
select convert(varchar(5),@test,108)

Hope this helps
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now