Converting Time in DATETIME to Zero

The biggest question I get in regards to date functions is a quick way to convert the time portion of datetime to zero (or 00:00:00), regardless of where you get the date.
There are several options out there, but the easiest to implement and remember for me is using, what I call a flip-flop.
First you date the date and convert into an float. This turns your date into a Julian date which does not have any time factors.
EX: Convert(float,getdate())
returns 40239 (Where the date is 03/04/2010).
Now to get the time back we simply convert back to a DATETIME. Using FLOOR will force it to the current day instead of potentially rounding to the next day.
Here is the code. You are welcome to use, just replace GETDATE() with your date attribute.


CONVERT(DATETIME,(FLOOR(CONVERT(float,getdate()))))

---

** Updated 5.13.2010 **
As I was pondering the mysteries of the SQLverse I was looking at other ways to manipulate the dates and thought of another quick way to reset the time variant of DATETIME to 00:00:00. Of course we all know ways and some are very long. But here is another quick and dirty converion that would eliminate errors in rounding.


CONVERT(DATETIME,CONVERT(VARCHAR,date variable,101));

This simply removes the time variable with the VARCHAR,..,101 to YYYMMDD format. Since there is no longer a time variable the DATETIME conversion can only add 00:00:00 to the time aspect.

---

SQL 2012 and up

Since the release of SQL 2102 and up this conversion has become quite simple. There are now at least two ways to convert a date and time variable to show no time variable. First is a type conversion. If you want to see the time but in a 00:00.0000 way I convert the DATETIME attribute to a DATE and then back, like so: CONVERT(DATETIME,CONVERT(DATE,GETDATE())) Second is using FORMAT, like so: FORMAT(GETDATE, "MM/dd/yyyy") ** In SQL, if the answer seems too complicated, it probably is.

How to Get the First or Last Day of a Given Month

   This seems to be a frequently asked question that people give multiple answers to. Even I struggled with something so simple. Why?? I think we all too often try to think out of the box when often times the box holds the answer.
GETTING THE FIRST DAY OF THE MONTH
   Subtract the day on the month +1 from the date. Simple huh?

     DATEADD(d,-DAY(getdate())+1,getdate())

---

GETTING THE LAST DAY OF THE MONTH    To get the last day of the month you simply add month and subtract day from the above code. ** If you are using time then you would subtract some derivative of time such as second(s) or minute(s). **
   We would do something like the following:
     DATEADD(d,-1,DATEADD(m,1,DATEADD(d,-DAY(getdate())+1,getdate())))

Simple sweet and not complicated

UPDATE - 10.4.2012

  Since the release of SQL 2012 (Denali) finding the last/first day of the month becomes easier.  With 2012 there is a new call to find the end of the month EOMonth(date).  This will return the end of the month for the given date.  With a little imagination I imagine you can have a lot of fun with this new function.

How to Check if File Exists (VB)

How to check to see if a file exists before allowing an upload to your server.
In your Upload code-behind add the following lines to your btn_click sub:
...
If System.IO.File.Exists(path & Upload1.Filename) THEN
code on what to do if it exists
End If
...
Here is an example of my code with some error handling and reporting lines. You will need to change the generic objects to your id names:

Protected Sub btn_Upload1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_Upload1.Click
If Upload1.HasFile Then
Dim ext As String
Dim savefile As String
ext = System.IO.Path.GetExtension(Upload1.FileName)

If (ext = ".jpg" Or ext = ".gif" Or ext = ".jpeg" Or ext = ".png" Or ext = ".bmp") Then
savefile = "(Filepath & Upload1.FileName
FileLabel2.Visible = False
Try
If System.IO.File.Exists( Filepath & Upload1.FileName) Then
FileLabel1.Visible = True
FileLabel1.Text = "File already exists. Upload cancelled."
Else
Upload1.SaveAs(Server.MapPath(savefile))
FileLabel1.Visible = True
FileLabel1.Text = "File Name: " & Upload1.PostedFile.FileName & "
" & "File size: " & Upload1.PostedFile.ContentLength & " kb
" & "Type: " & Upload1.PostedFile.ContentType & "
<img src ='" & savefile & "'>"
End If
Catch ex As Exception
FileLabel1.Text = "Error: " & ex.Message.ToString()
End Try
Else
FileLabel2.Visible = True
FileLabel2.Text = " Please specify only Image files to upload."
End If
Else
FileLabel2.Visible = True
FileLabel2.Text = "Your Upload did not upload."
End If
End Sub

Hope this is helpful.

ASP.Net Login Errors

"CREATE DATABASE permission denied in database 'master'."

This was the bane of my existence for several days. I scoured the web to see what other people were doing to resolve the issue and I just got the same stuff, the stuff I was doing.
BACKGROUND: I am using SQL SERVER 2005 to host my data and aspnetdb from the ASP.net framework. I already have the ASPNETDB installed. This DB install defaults it DB connections to .\sqlexpress. This I resolved by changing the .\sqlexpress to (local)\. However this only gave me the message above. Again no help from the web. So how did I get it to work?
If you read in the error, the major issue is that the DB exists and can't be overridden. Think about that for a second and then look at your ASP.net configuration connections for LocalSQLServer...
The string will always try to attach the DB. SO I simply gave the LocalSQLServer connection a normal connection.
Open IIS and right click on the website. Select Propeerties. Click on the ASP.net tab and select either Global or Local configuration. Select the LocalSQLServer connection and edit the string to something like this:
Data Source= <servername>;Initial Catalog=Aspnetdb;Persist Security Info=True;User ID=<uname>;Password=<pword>; The key is to remove the section for AttachDatabase and replace with Initial Catalog.
Once I changed this connection it integrated into ASP.net security just fine. I hope this helps someone else from beating their head on the table for days onend wondering why it isn't working.