How to get the hour from a datetime string (T-SQL)

Often data is entered into the database as a string (varchar), when a more appropriate data type should have been applied.

Here’s a snippet of T-SQL for dealing with datetime data, stored as varchar, when what you really need is the hour. 

DECLARE @DateTime AS VARCHAR(25)

-- GET THE CURRENT DATETIME AND CONVERT INTO A STRING
SET @DateTime = (
		SELECT CONVERT(VARCHAR(25), GETDATE(), 120)
		)

--PRINT @DateTime
-- GET HOUR FROM DATETIME AS A STRING
SELECT DATEPART(HOUR, CONVERT(DATETIME, CONVERT(VARCHAR(13), @DateTime, 120) + ':00')) AS ConvertedHour

 

Why would data be stored as a varchar instead of using the appropriate data type in the first place?

The most common reason might be that XML files have been loaded without any data type conversions being applied, i.e. dates have been entered as strings along with everything else.

Why would you need this code?

Should you be confronted with data in this raw string form you would be unable to apply a clause regarding a specific hour. Ideally there should be some sort of staging table to convert the fields into the appropriate data types but if you simply wanted to apply some clauses to select specific data, which will be outputted to an excel file for example, that additional effort may be unnecessary.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s