September 26, 2008 at 2:02 pm
Hello,
I have one datetime column named Hours in SQL Server Table1, which gives the output as 09/26/2008 01:00:00, 09/26/2008 02:00:00, 09/26/2008 03:00:00 till 09/26/2008 12:00:00 format.
I wanted to get just the year in int datatype from this format and I got it using (SELECT CONVERT(int, YEAR (Hours)) AS Year FROM Table1) and I am getting the correct Year as I wanted 2008. Same way I am getting month and date also. But the issue is with hour.
I want the hour also in the integer format. I mean if there is 01:00:00, then the output should just be 1 and same way the next should be 2.
Can anyone please help me to achieve this output?
I want a proper syntax for the same.
Meanwhile I will carry on the research on my part.
Thank you.
September 26, 2008 at 2:41 pm
You are still going to use CONVERT - but, you need to tell it what style. Look up CONVERT in Books Online for the correct style to return just the time.
Once you have that - then you can CAST or CONVERT the formatted value to integer.
It will look like:
SELECT CONVERT(int, CONVERT(char(2), date_column, style));
SELECT CAST(CONVERT(char(2), date_column, style) AS int);
For just the hour - use style 108.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 26, 2008 at 3:58 pm
Try this:
SELECT Hours, CAST(SUBSTRING(CAST(Hours AS VARCHAR(40)),CHARINDEX(':',Hours)-2,2) AS INT)
AS 'Int Hours' FROM Table1
Table1 contents as displayed in SSMS open table
9/26/2008 4:46:20 PM
9/26/2008 4:46:22 PM
9/26/2008 4:46:24 PM
9/26/2008 5:01:01 AM
9/26/2008 12:05:10 PM
Results:
Using select hours Hour
2008-09-26 16:46:24.403 4
2008-09-26 05:01:01.000 5
2008-09-26 12:05:10.000 12
The above does not adjust for AM or PM but shows the hours as you would see by openning the table in SSMS
If you want AM or PM use:
SELECT Hours, CAST(SUBSTRING(CAST(Hours AS VARCHAR(40)),CHARINDEX(':',Hours)-2,2) AS INT)
AS 'Int Hours', CAST(SUBSTRING(CAST(Hours AS VARCHAR(40)),LEN(Hours)-1,2) AS VARCHAR(2)) FROM Table1
Note that Jeffry Williams code uses a 24 hour clock
SELECT CAST(CONVERT(char(2), Hours, 108) AS int)FROM Table1
Results:
Hour
2008-09-26 16:46:24.403 16
2008-09-26 05:01:01.000 5
2008-09-26 12:05:10.000 12
Hope this helps
September 27, 2008 at 6:33 pm
BWAAHAA! No need to do the Polka with Convert, folks! 😛 Read the original post!
I have one [font="Arial Black"]datetime [/font]column named Hours in SQL Server Table1...
Notes4we, I recommend that you study the article at the link in my signature line below so you can avoid this type of confusion and get better answers quicker ;)...
Here's the test data setup according to verbage in the original post...
--===== This is the start time used to build the test table with
DECLARE @StartTime DATETIME
SET @StartTime = '09/26/2008'
--===== This builds the test table according to the OP's specs
-- using Master.dbo.spt_Values as if it were a Tally table.
SELECT DATEADD(hh,Number,@StartTime) AS Hours
INTO dbo.Table1
FROM Master.dbo.spt_Values --Used as if a Tally table
WHERE Type = 'P'
AND Number BETWEEN 1 AND 12
--===== This just shows the contents of the table
SELECT * FROM dbo.Table1
... and here's the solution... without the Convert Polka... 😛
--===== This is the solution along with the original value from the table
SELECT Hours,
DATEPART(yy,Hours) AS Yr,
DATEPART(mm,Hours) AS Mo,
DATEPART(dd,Hours) AS Dy,
DATEPART(hh,Hours) AS Hr
FROM dbo.Table1
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply