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.

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

Putting Javascript in an XSLT Stylesheet

There was some code from a previous developer that I had to edit. He had used XML and an XSLT stylesheet to display a form to the user. I needed to add some javascript to hide and show a div when an existing button was clicked. There was an <xsl:template tag and I had to put the script tags and the javascript in there.

<xsl:template match="/*">

    <script language="JavaScript" xmlns:msxsl="urn:schemas-microsoft-com:xslt" >

      function toggle(ID)

      {

      var element = document.getElementById(ID);

      if(element.style.display == "block")

        {

          element.style.display = "none";

        }

      else

        {

          element.style.display = "block";

        }

      }

    </script>

Redisplay Dynamic Controls Every Time ASP.Net

I had some controls that I was dynamically creating.  When I would click my button to postback I was finding that the code had no idea about those controls, even though I could see them in the page I just posted.  The controls were also still there when the code returned to the web page.

It turns out that you have to recreate the dynamic controls every time.  I avoided this answer, since recreating the control I thought would reinitialize the values and I would lose what the user has chosen on the page.  It turns out the viewstate figured it out and just needed to the control to be created each time for it to have a place for it to save its values.

It looks like Page_Load and Page_Init both work for this purpose.

mm is not MM

Even though when you call a ToString() on a DateTime the day and year format characters are dd and yyyy, respectively, that does not mean that the month part is mm.  mm is already used to format for minute.  I spent a while today trying to figure out why I was getting a number that was beyond the range of 1-12 for the mm portion of my date when using ToString(mmddyyyy).  You have to use MM for your month portion.

Have You Ever Done Any Debugging?

A while ago I was contacted by a recruiter.  One of the screening questions was, "Have you ever done any debugging?"

Maybe there is some type of software development that I am not familiar with where you don't debug code.  In my definition of software development debugging is mostly the main job.  Not only are you debugging the code you write and the code others have written, but you also have to debug the users process and perceptions.

Even if you count out debugging the user, I can't imagine why someone would list debugging as a requirement for a software developer, since it is implied in the title.

I also find it somewhat ridiculous that one that is sent to find candidates for a programming job hasn't been trained enough to know that programmers debug by definition.  It makes the recruiter look less than competent.  It makes the recruiting company look bad.  It also makes the company that is looking for a programmer look like they don't have it together.  Quite likely the company looking to hire someone is perfectly fine, but the recruiter hasn't been trained properly to be able to find candidates.

What's In a Name

Over the last 15-20 years of my programming career my title has changed all over the place while doing essentially the same job.

In some jobs I have been titled a programmer.  In others an application developer.  Still others I was called a software developer.  One employer called me a software engineer.

The engineer title was a little much for what was going on.  Even the title architect seems a bit much.  Perhaps there are those that get degrees in software architecture or software engineering, but it kind of leave my paradigm of what engineers and architects are.