Posts Tagged ‘SQL DateTime’

h1

SQL SERVER: Get Result ORDER BY Time regardless Date on SQL DateTime column

03/24/2009


Usually we save Time with Dates in DATETIME column.
Today, I came across situation, where I need to sort my result set by Time, regardless the Date.


Let’s see Example

I have some sample data like this:

DECLARE @Data TABLE(dt DATETIME)

INSERT INTO @Data(dt)
SELECT ‘2008-12-05 04:00:00.000’
UNION ALL
SELECT ‘2008-12-10 10:00:00.000’
UNION ALL
SELECT ‘2009-03-01 08:00:00.000’
UNION ALL
SELECT ‘2009-03-02 07:15:00.000’
UNION ALL
SELECT ‘2009-03-10 08:50:00.000’
UNION ALL
SELECT ‘2008-12-31 23:00:00.000’
UNION ALL
SELECT ‘2009-05-01 21:10:00.000’

SELECT *
FROM @Data
Now we need output like this:

Get Result Order BY Time regardless Date on DateTime column

Get Result Order BY Time regardless Date on DateTime column


I found very quick solution for this. You can create query as follows:

SELECT *
FROM @Data
ORDER BY Convert(VARCHAR, dt,108)


Let me know if it helps you in any way.

Advertisements
h1

SQL SERVER: Display Minutes in Hour Format like HH:MM

03/23/2009


Usually, we are saving Hours in Minute Format. So if user has entered
Hour: 2 Minutes: 15. We will save 135 Minutes in Database, so we can manipulate it easily.


Now we need a report in which we need to display this minutes in HH:MM format as user has entered.

This is very simple, lets see:

DECLARE @t TABLE(Minutes INT)
INSERT INTO @t
SELECT 120
UNION ALL
SELECT 135
UNION ALL
SELECT 135
UNION ALL
SELECT 1440
UNION ALL
SELECT 640
UNION ALL
SELECT 720

SELECT Minutes / 60 as Hours,
Minutes % 60 AS Minutes
FROM @t

Let me know if it helps you in any way.

h1

Get Weekday of Date – SQL Server

02/08/2009

Today i come to the situation where I need to display Dayname like Sunday, Monday,Tuesday etc.. fot the dates from the SQL Server. I found the syntax for that:

SELECT DATENAME(dw,GETDATE())

We can use this too: SELECT DATENAME(weekday,GETDATE())

h1

SQL SERVER – Get Time in AM/PM Format

12/22/2008

Today i come with one situation where I need to get Time in AM/PM format.

I tried with different way and come up with the simple solution, that i am going to share with all of you.


SELECT
GETDATE() AS CurrentDate,
RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentTime,
CONVERT(VARCHAR(10), GETDATE(), 101) + + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentDateTime

SQL DateTime

Syntax to Get SQL DateTime With Am/Pm Format

SQL Date time in AM/PM Format

Get SQL Date time in AM/PM Format

I have read very interesting article on SQL Authority.Com . You can find the many ways to get date in different formats.