SQL DateTime Data

Question

I have a SQL table like below.

SELECT * FROM [icom].[dbo].[Periods]

This table gives me periods that I specified. For example period one starts at 8:00 and ends at 8:45am.

When I run the query, it gives me date and time milliseconds format. So how could I get only time? Like 8:00am?

Try using `DATEPART` in SQL to get the time in the format you need. — user2989408, Apr 21 '14 at 16:42
I suggest that you use the value you get from the database as it is and then do all "formatting" in your application. You will be more flexible if you don't do the processing at database level. If you do so, you'll get back a string. Otherwise you'll have a `DateTime` which you can convert and use as you wish. — Thorsten Dittmar, Apr 21 '14 at 16:56

score 0 · Answer 1 · answered Apr 21 '14 at 16:45

0

You can add a CONVERT command in your query. Like so:

SELECT CONVERT(VARCHAR(8), GETDATE(), 108) * FROM [icom].[dbo].[Periods]

For more formats and examples you can check out: http://www.sql-server-helper.com/tips/date-formats.aspx I hope that helps!

answered Apr 21 '14 at 16:45

Jarod Kientz

131
1
9

Also there is a "Time" data type in SQL 2008 and beyond. Using that may be easier, but I'm not sure for your specific circumstances. – Jarod Kientz Apr 21 '14 at 16:49

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

You could also use SELECT DATEPART(hh, [YourDatetimeColumn]) FROM [Periods]. You might also find useful answers by taking a look at this stackoverflow question.

edited May 23 '17 at 10:26

Community

1
1

answered Apr 21 '14 at 16:52

Octavian Mărculescu

4,312
1
16
29

score 0 · Accepted Answer · answered Apr 21 '14 at 17:06

Assuming that you are using SQL Server (which I think you are), it sounds like you're using the datetime data type:

datetime represents an instant in time. Internally, datetime consists of a pair of integers:

The first measures the offset in days from the epoch. An epoch is the zero point of a calendar system, which for SQL Server 1 January 1900 00:00:00.000.
The second is the [positive] offset since start of day, in milliseconds.

How that internal representation is represented visually is entirely up to you, though SQL Server has a [configurable] default conversion pattern.

If you are accessing your SQL programmatically, say from C#, the API automatically maps the SQL Server datetime datatype to your languages equivalent type (System.Datetime for C#). The issue is present: it is your responsibility to format it visually or textually.

If you want to see only the date component. You can format it in T-SQL using the convert()/cast() function: convert(varchar(32),{some-datetime-expression},116) will convert the {some-datetime-expression} to the format dd mmm yyyy. Lots of other options.

Further assuming that you are using a recent-ish version of SQL Server, if you just want to deal with dates, change your schema to use the date datatype instead of datetime.

SQL DateTime Data

3 Answers3