One of the things I really find frustrating sometimes is working with dates.  I came along a problem recently where I was receiving a nicely formatted UK date (dd/mm/yyyy etc) and needed to pass it as a string over to another SP which may or may not accept that string formatting.  Son instead of 19/04/2007, I wanted to use something closer to April 19, 2007.

Every time I tried I was getting a DateTime conversion error.  When I've seen this problem before people have always talked about parsing the string and cutting it in to it's component parts.  Now, I'm no SQL guru but I was pretty sure that SQL server should be able to handle this in a better way.

Well, I think I may have found it.  SQL nicely give us a convert function.  A simple example of how to use it might be:

Declare  @myNumber   int
Declare  @myString   varchar(4)
Select   @myNumber   = 101

Select   @myString   = Convert(varchar, @myNumber)

Print    @myString

This would return a varchar with the value "101".  Simple enough.

Dates of course are much more complex as a string due to the (possibly insane) possible combinations of values.

I came across this page which explains some more setting you can use. So eventually, I managed to solve my problem thanks to this using the following (I've expanded this just for the sake of the example):

Declare  @in      varchar(50)
Select   @in      = '19/04/2007 14:04:54'
Print    'In > ' + @in

Declare  @date    datetime
Select   @date    = Convert(datetime, @in, 103) -- Deals with the dd/mm/yyyy and time
Print    'Out > ' + Cast(@date as varchar)

Print    'Final > ' + Convert(varchar, @date, 109) -- Basically a long date time format

Print    'Combined > ' + Convert(varchar, Convert(datetime, @in, 103), 109)

The output being:

In > 19/04/2007 14:04:54
Out > Apr 19 2007  2:04PM
Final > Apr 19 2007  2:04:54:000PM
Combined > Apr 19 2007  2:04:54:000PM

Hurray, problem solved (for me at least)