Equivalent to MySQL Now()

Use GETDATE():

UPDATE table SET date = GETDATE();

This returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.

Using Fields Stored Using GETDATE() In SELET queries

24 Hours ago:

getdate()-1

Specifying The Date & Time

For DateTime put single quotes around the value:

 sTemp = String::Format("{0:D4}", Convert::ToInt32(SelectedStartDate->SelectedDate->Year)) + "-";
 sTemp += String::Format("{0:D2}", Convert::ToInt32(SelectedStartDate->SelectedDate->Month)) + "-";
 sTemp += String::Format("{0:D2}", Convert::ToInt32(SelectedStartDate->SelectedDate->Day)) + " ";
 sTemp += "00:00:00";
 SqlWhereStatement += " AND StartDateTime>='" + sTemp + "'";

Datetime format can be a problem due to SQL storing milliseconds but outputting select strings with a datatime value not having the milliseconds. So you then manually add it but there are then problems of the SQL milliseconds being 1 out from the variable value you stored from and also where to tag the milliseconds onto due to different internatational conventions.

Good examples of breaking down data time fields in searches:
http://www.databasejournal.com/features/mssql/article.php/2209321/Working-with-SQL-Server-DateTime-Variables-Part-Three—Searching-for-Particular-Date-Values-and-Ranges.htm

Working Examples

Write Date And Time From VC++ DateTime Variable

DateTime ^TimeNow = gcnew DateTime();
TimeNow = DateTime::Now;
"UPDATE SomeTableName SET LastViewed=\'" + TimeNow->ToString("s") + "\' WHERE ...

"s" outputs the string in the ISO international format, e.g. '2006-04-17T14:38:09'

Compare day

query = "SELECT * FROM your_table WHERE DATEDIFF(dd, your_date_field, GETDATE()) = 0"

Gets all entries from your_table where your_date_field and the current date (today's date) are not different. The 'dd' means day, so it is comparing the day.

Exclude Entries On Specific Days

WHERE DATENAME(dw, StartDateTimeColumnName) NOT IN ('Monday','Tuesday') ";

Include Entries At Specific Times

WHERE DATEPART(HOUR, StartDateTimeColumnName)>=12 AND DATEPART(HOUR, EndDateTimeColumnName)<15

Get all entries from yesterday

query = "SELECT * FROM users WHERE DATEDIFF(dd, datecreated, GETDATE()) = 1"

Get from last week

query = "SELECT * FROM users WHERE DATEDIFF(ww, datecreated, GETDATE()) = 1"

Full list of what DATEDIFF does

http://msdn.microsoft.com/en-us/library/ms189794.aspx

International Date Format Issues

Give it to the server in ISO format (yyyy-MM-dd HH:mm:ss)