Sql-server – Extract time portion of Datetime2(7) in hhmmssfff format

datetimesql serversql-server-2008-r2t-sql

I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this:

DECLARE @mdate AS DATETIME2(7) 

SET @mdate = '2012-03-15 10:13:27.5437431'

SELECT LEFT(REPLACE(REPLACE(CONVERT(VARCHAR ,@mdate ,114),':',''),'.',''),9)

Is there a better approach than doing this ugly replaces/convert/left?

I need this to join to a DimTime dimension whose key is in hhmmssfff format.

Best Answer

Please don't use varchar without length.
Please use semi-colons as statement terminators.

I don't think there is a more covert way to do this. Here is how I would do it (14 characters shorter, ignoring the semi-colon):

DECLARE @mdate AS DATETIME2(7);

SET @mdate = '2012-03-15 10:13:27.5437431';

SELECT REPLACE(REPLACE(CONVERT(TIME(3),@mdate),'.',''),':','');

Related Solutions

SQL Server Data Warehouse Design – Best Practices

Here are my answers:

Define relationships based on your surrogate keys and not your business keys. This is because this is what the SQL engine will use to optimize queries and this is the reason you have surrogate keys. Since surrogate keys are often integers, this simplifies the joins from using different data types such as strings which are less efficient or even using composite keys. It might be worth defining nonclustered indexes on your business keys for lookups during your ETL process, but queries against the data warehouse should utilize your surrogate keys.
Here's how I would approach your fact tables. If the different grades are the same granularity and have most if not all of the same dimensions in common, I would put them in the same fact table. Otherwise, I would put them in separate fact tables. You can always define cubes with multiple measure groups or put a view in place that combines the different fact tables into one that makes sense down the road.
You definitely can link one dimension to multiple fact tables. This is referred to as a conformed dimension and is usually more common that linking a dimension to a single fact table.
Typically, I create a date dimension that is at a single day granularity and link it to any fact table that has a date field in it. However, in an education context, you might have the need for a different granularity such as just assigning a fact to a quarter dimension instead. If that's the case, you could build out a separate dimension table at the desired granularity. However, for your example of an exam, you would just link that exam record to the date dimension based on that specific day. Furthermore, you should build out your date dimension to have fields such as quarter, school year, etc. One other thing to consider in your example is that it might be useful to calculate the duration of the exam based on the start and end dates if they are in a date time format and you want to know how long it took student to complete a specific exam and to see if duration correlates with results in some way.

Let me know if you have any follow up questions!

SQL Server – Alter Table vs Drop and Create

I recently did this in my organization wherein we wanted to handle a table with billion + rows.

All the credit for the idea goes to Aaron Bertrand and is from his blog post Trick Shots : Schema Switch-A-Roo

Test below process on a small table and get your self comfortable before doing it in PROD.

create 2 schemas fake and shadow with authorization dbo.
Create a table with the columns and data types you want in shadow schema e.g. create table shadow.Correct_Table ...
Insert the data and create all the indexes that the original table has in the shadow schema table.
This way you have identical copies of table with data and indexes but they are in different schemas (logically separated).
Once done update stats on the table with shadow schema.

Switch the schemas (This is a metadata operation and is extremely fast)

--- ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName; 

BEGIN TRANSACTION;

  ALTER SCHEMA fake TRANSFER     dbo.original_table;
  ALTER SCHEMA dbo  TRANSFER  shadow.Correct_Table;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER fake.Lookup;

Do a final check to see if everything went as planned. You should do a select count(1) from dbo.Correct_table
Once step 7 is confirmed and you are happy, drop the shadow.table, shadow schema and fake schema as clean up.

Best Answer

Related Solutions

SQL Server Data Warehouse Design – Best Practices

SQL Server – Alter Table vs Drop and Create

Related Question