Friday, September 21, 2012

Stripping the Time Off of a DateTime Field in SQL Server

I find myself occasionally having to return just the date portion of a DateTime.  Every time I have to remember what I did the last time.

Here are a few solutions I have tried.  You can apply them to whatever your need is.

Let's say you have a field in your table called DateOfExecution and that date in one record is 2012-09-01 15:14:55.540

Method 1 - Convert to a NVarChar and Format It.
CONVERT(nvarchar(20),  DateOfExecution  , 101)

This method returns 09/01/2012.  This lets you just compare the date by itself, but you have to compare it as a string.

Method 2 - Cast It to a Float, Take Off the Decimal Portion With Floor, and Cast It Back to a DateTime
CAST( FLOOR( CAST(  DateOfExecution   AS FLOAT ) ) AS DATETIME )

This method returns 2012-09-01 00:00:00.000, leaving a time portion there, but the time portion is all zeros.  This can be useful if you want to get two dates into the same time and compare the entire DateTime.


Method 3 - Pull the Year, Month, and Day Out of the Date as Strings, Concatenate Them Together, and Cast Them Back to a DateTime
CAST(
(
STR( YEAR(  DateOfExecution   ) ) + '/' +
STR( MONTH(  DateOfExecution   ) ) + '/' +
STR( DAY(  DateOfExecution   ) )
)
AS DateTime
)

This method is more complicated and also returns 2012-09-01 00:00:00.000.

Method 4 - Pull the Year, Month, and Day Out of the Date as Strings, Trim the Individual Parts, and Concatenate Them Together

rtrim(ltrim(STR( YEAR(  DateOfExecution   ) ))) + '/' +
rtrim(ltrim(STR( MONTH(  DateOfExecution    ) ))) + '/' +
rtrim(ltrim(STR( DAY(  DateOfExecution   ) )))

This method returns a string that looks like this 2012/9/1.  You could apply some formatting and/or concatenate the values in a different order to make it look the way you want it to.

Monday, September 3, 2012

Sharing SQL Server (TSQL) Temp Tables Across Stored Procedures

I had a query that I created for a report.  Another report needed to display that report and use the data from it to display some other reports on the same page.

In order to pull in data from different sources and aggregate it at the end and to remove duplicates I dumped all the records I was accumulating into a temp table.  The problem I had was getting the information from that temple table to the other stored procedure that was creating the other reports.

At first I was using a local temp table using the #TableName syntax.  This was only available to the current stored procedure, so I had to change that to a global temp table using the ##TableName syntax.  After that the second stored procedure could use the temp table information.  Unfortunately, since it was global, even one who ran it was using the same information and were stepping all over each other.

I next decided that I would not drop the table at the top of the first store procedure, but create the records storing SPID in field in the temp table, so that I would only see my records and not some else's.  As I thought about it more I thought it might be best to use the UserId of the currently logged in user.  This would allow me to delete all of my records from previous runs without disturbing others' records.

From what I have seen the global temp table drops itself after some period of inactivity, so that is good too, but as a precaution I am considering also storing a datetime field on the records I am creating as well as the user name, so that I can delete records older that a certain number of minutes, since the report is run and looked at and the temp data is no longer needed.

So that I can dynamically create the temple table from a query I put in code to check for the temp table existing.

IF OBJECT_ID('tempdb..##NewSales') IS NOT NULL

If it does not exist then I do


INSERT INTO tempdb..##NewSales
SELECT {FieldList}

If it does exist then I do


SELECT  {FieldList} INTO tempdb..##NewSales