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)

USEFUL?
We benefit hugely from resources on the web so we decided we should try and give back some of our knowledge and resources to the community by opening up many of our company’s internal notes and libraries through mini sites like this. We hope you find the site helpful.
Please feel free to comment if you can add help to this page or point out issues and solutions you have found, but please note that we do not provide support on this site. If you need help with a problem please use one of the many online forums.

Comments

Your email address will not be published.