CODEWITHSUNDEEP
sqlvb.netsql-server-2005datetime
aphoria
aphoria

Reputation: 20209

Formatting a date/time in SQL Server 2005

I have a datetime field in a SQL Server 2005 table that has values like this:

2012-04-23 09:00:00.000
2012-04-23 14:00:00.000

The minutes, seconds, and microseconds are always zero.

I need to display a "time slot" (basically, the time plus one hour) like this:

2012/04/23  09:00 AM - 10:00 AM
2012/04/23  02:00 PM - 03:00 PM

I got what I needed using this:

SELECT SUBSTRING(CONVERT(VARCHAR, TimeSlot, 20), 1, 10) + '  ' +
       RIGHT('00000' + LTRIM(SUBSTRING(CONVERT(VARCHAR(24), TimeSlot, 109), 13, 5)), 5) + ' ' +
       SUBSTRING(CONVERT(VARCHAR(19), TimeSlot, 100),18,2) + ' - ' +
       RIGHT('00000' + LTRIM(SUBSTRING(CONVERT(VARCHAR(24), DATEADD(hh, 1, TimeSlot), 109), 13, 5)), 5) + ' ' +
       SUBSTRING(CONVERT(VARCHAR(19), DATEADD(hh, 1, TimeSlot), 100), 18, 2) AS TimeSlot
FROM MyTable

It works, but I feel so dirty.

I know I could do it easier in the code (VB .NET), but assume I have to do it in SQL.

Is there any cleaner way to do this?

Upvotes: 1

Views: 1385

Answers (3)

Steve Kass
Steve Kass

Reputation: 7184

FollowingConcernedOfTunbridgeW's suggestion, if you maintain a two-column table with a primary key int column [hr] that goes from 0 to 23 and a char(20) column [rng] containing the space-prefixed range string for each hour

' 12:00 AM - 01:00 AM'  (in the row where hr = 0)
...

' 11:00 PM - 12:00 AM'  (in the row where hr = 23)

this simply query should produce what you want:

  select
    convert(char(10),TimeSlot, 111) + rng as TimeRange
  from T join RangesByHr
  on datepart(hour,TimeSlot) = hr;

Upvotes: 0

ConcernedOfTunbridgeWells
ConcernedOfTunbridgeWells

Reputation: 66722

The built in date/time formats in SQL Server will only format to an actual date format, so you're up for custom formatting anyway. If you're feeding SSRS with this then you might be able to use an expression to format it within SSRS. Otherwise you're up for formatting it with the query using something like the technique you're describing.

If you're actually displaying it with a VB app then you could do that client-side.

If your date/time values are always on the hour or some consistent interval you could also make a reference table keyed on the time with the formatted interval stored as a varchar column and use that. That would save you in-lining the formatting expression into every query that needed to produce the interval description.

Upvotes: 1

balexandre
balexandre

Reputation: 75133

Taking the built-in conversions, I would use something like this:

SELECT 
    convert(varchar, [TimeSlot], 111) + ' ' +
    RIGHT(convert(varchar, [TimeSlot], 0), 7) + ' - ' +
    RIGHT(convert(varchar, dateadd(hour, 1, [TimeSlot]), 0), 7)
FROM
    [MyTable];

this gives you the line you want as

2012/04/23  9:00AM - 10:00AM

in detail:

  • convert(varchar, @dt, 111) converts the datetime to the Japanese format yy/mm/dd
  • convert(varchar, @dt, 0) converts the datetime to Apr 23 2012 9:00AM so we can use the time part
  • dateadd(hour, 1, @dt) adds one hour to the current datetime value

you can test it without tables with:

DECLARE @dt DATETIME
SET @dt = '2012-04-23 09:00:00'

PRINT convert(varchar, @dt, 111) + ' ' +
      RIGHT(convert(varchar, @dt, 0), 7) + ' - ' +
      RIGHT(convert(varchar, dateadd(hour, 1, @dt), 0), 7);

Upvotes: 1

Related Questions