    Displaying the current date:

    select convert(char(3),datename(month,getdate()))+" "
           +convert(char(2),datename(day,getdate()))+","
           +convert(char(4),datename(year,getdate()))

    Displaying the current time:

    select convert(char(2),datename(hour,getdate()))+":"
           +convert(char(2),datename(minute,getdate()))+":"
           +convert(char(2),datename(second,getdate()))

    Selecting just the date part from the current timestamp
    (ie reseting the time fields to 12:00AM):

    select convert (datetime,
           (convert(char(3),datename(month,getdate()))+" "
           +convert(char(2),datename(day,getdate()))+","
           +convert(char(4),datename(year,getdate()))
           )
                   )

    This is an example of making all possible conversions back and forth from
    datetime to char:

    select convert (datetime,
           convert(char(4),datename(year,getdate()))
           +"-"
           +convert (char(3),datename(month,getdate()))
           +"-"
           +convert(char(2),datename(day,getdate()))
           +" "
           +convert(char(2),datename(hour,getdate()))
           +":"
           +convert(char(2),datename(minute,getdate()))
           +":"
           +convert(char(2),datename(second,getdate()))
        ,105)

    Converting a period to a char:

    select convert(char(10),p1)+"-"+convert(char(10),p2)
           from periods