Extracting Hours from a Datetime (SQL Server 2005)

Extracting hours from a DateTime (SQL Server 2005)

SELECT DATEPART(HOUR, GETDATE());

DATEPART documentation

How to get Time from DateTime format in SQL?

SQL Server 2008:

SELECT cast(AttDate as time) [time]
FROM yourtable

Earlier versions:

SELECT convert(char(5), AttDate, 108) [time]
FROM yourtable

How to get time part from SQL Server 2005 datetime in 'HH:mm tt' format

One way is:

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100), 7))

If you have a look at Books Online here, format 100 is the one that has the time element in the format you want it in, it's just a case of stripping off the date from the front.

How do you extract just date from datetime in T-Sql?

Best way is:

   SELECT DATEADD(day, DATEDIFF(Day, 0, @ADate), 0) 

This is because internally, SQL Server stores all dates as two integers, of which the first one is the ****number of days*** since 1 Jan 1900. (the second one is the time portion, stored as the number of seconds since Midnight. (seconds for SmallDateTimes, or milleseconds for DateTimes)

Using the above expression is better because it avoids all conversions, directly reading and accessing that first integer in a dates internal representation without having to perform any processing... the two zeroes in the above expression (which represent 1 Jan 1900), are also directly utilized w/o processing or conversion, because they match the SQL server internal representation of the date 1 jan 1900 exactly as presented (as an integer)..

*NOTE. Actually, the number of date boundaries (midnights) you have to cross to get from the one date to the other.

Retrieving the timezone of a recorded datetime in SQL Server (2005)

The timezone is not stored with the date value. You can read this article: Solving the Datetime Mystery:

So how does SQL Server internally store the dates? It uses 8 bytes to
store a datetime value—the first 4 for the date and the second 4 for
the time. SQL Server can interpret both sets of 4 bytes as integers.
For the date portion, the value SQL Server stores is the number of
days before or after a base date of January 1, 1900. Because of this
storage protocol, SQL Server assumed the date of January 1, 1900, when
I didn't supply the date in my first example. SQL Server internally
stored a value of 0. A negative number represents a date earlier than
January 1, 1900.

SQL Server stores the second integer for the time as the number of
clock ticks after midnight. A second contains 300 ticks, so a tick
equals 3.3 milliseconds (ms). You can see the values for days and
clock ticks by converting a datetime value to a binary(8) value and
using the substring function to extract each set of 4 bytes. The code
in Figure 3 then converts each set of 4 bytes into an integer.

You can use the datetime datatype and store the dates in UTC timezone and then you can format the date while retrieving it from the database in the format in which you want.

Time part of a DateTime Field in SQL

In SQL Server if you need only the hh:mi, you can use:

DECLARE @datetime datetime

SELECT @datetime = GETDATE()

SELECT RIGHT('0'+CAST(DATEPART(hour, @datetime) as varchar(2)),2) + ':' +
       RIGHT('0'+CAST(DATEPART(minute, @datetime)as varchar(2)),2)

How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

• No varchar<->datetime conversions required
• No need to think about locale

SQL convert DateTime to Date (SQL server 2005)

There is no Date datatype in SQL Server 2005, it was added in SQL Server 2008.

To go with your second alternative, you can truncate the datetime to midnight on the same day:

SELECT DATEADD(DAY, DATEDIFF(DAY,0, YourDateColumn), 0)
FROM   YourTable

Extracting and aggregating Hour from DateTime and Formatting the hour with AM/PM

Try this:

SELECT  CustomerCount = COUNT(*)
        , ArrivalHour = STUFF(RIGHT(CONVERT(VARCHAR(20), Arrival, 100), 7), 4, 2, '00 ')
FROM    MySchema.MyTable
GROUP BY STUFF(RIGHT(CONVERT(VARCHAR(20), Arrival, 100), 7), 4, 2, '00 ');

---

Related Topics