Database - How do I use values (numbers, strings, dates) in SQL statements

This article explains how to put values directly into an SQL statement.

If you are using code to put the values in, you should really be using Parameters instead - because they do everything explained in this article for you, in addition to several other useful things not mentioned here (and can be used against different database systems with no changes). For more explanation and links to code examples, see the FAQ article Why should I use Parameters instead of putting values into my SQL string?



When you put numeric values into SQL statements you should simply put the number into your SQL, eg:

Code:

---

Select *
From myTable
Where numberField = 1

---

Note that the important thing is the data type of the field (in this case, numberField), it does not matter if the value (1) is coming from a variable in your program that has a different data type.

If a number has decimal places, you will almost certainly need to use the . character as the decimal separator (even if you would usually use the , character).



The method above does not work for string or date values, instead you need to indicate the start and end of the string/date, just as you would in VB code. The characters you need to use may vary by DBMS (Access/SQL Server/..), but in general:

Strings/chars/text: use the ' character around the value, eg:

Code:

---

Select *
From myTable
Where textField = 'text here'

---

If there is any chance that your value will contain the ' character (such as if it is coming from user input), you should also see How do I put the ' character into an SQL string?



Dates: for most database systems (DBMS's) you use the ' character around the value, but this varies by DBMS.

Note that apart from the exceptions listed below, date values always need to be in either US format (M/D/YYYY) or ISO format (YYYY-MM-DD).

Why those formats?

The way a date is displayed (whether that is in a database window, or VB controls, or the VB watch window, etc) is not the same way it is stored. They are typically stored as numbers like 43012.759 (which represents a certain number of days since a specific date), and are just formatted in a readable way (using either Regional Settings, or properties you have set within the database etc) when they are displayed somewhere.

When date values are inside inside SQL statements they (like the rest of the statement) are a String - they are no longer a Date, but a String based representation of one. In order for the database system to be able to work out what you meant, it needs to know which format was used (because the Regional Settings of the database server could be different to the Regional Settings of the client computer), and that has been standardised as the US and ISO formats.

If you are using Access (or another Jet/ACE based system), it will sometimes interpret other formats correctly, but not always - so if you use any other format you will end up with bad data and/or apparent bugs in your queries.

If you are using code to put the values in, do not use 'Short Date' format or 'Long Date' format, or simply append a Date to the statement (the formats they use vary based on things outside of your program). You need to explicitly ensure that one of the correct formats are used.

For MySQL, you should always use the format 'YYYY-MM-DD'

Code:

---

  For most DBMS's (including SQL Server)
Select *
From myTable
Where dateField = '01/01/2006'

  For most versions of Access [and other Jet databases] (others versions use ' as above)
Select *
From myTable
Where dateField = #01/01/2006#

  For Oracle (date only, no time)
Select *
From myTable
Where dateField = DATE '2006-01-01'

  For Oracle (any combination of date and time, see here for more details)
Select *
From myTable
Where dateField = TO_DATE('JAN-01-2006','MON-DD-YYYY')

  For Visual FoxPro
Select *
From myTable
Where dateField = {01/01/2006}

---

If you are using Classic VB (VB6 or earlier, or VBA) you can use the Format function to get a date into a string formatted as you specify, eg:

Code:

---

Dim strSQL as String
  strSQL = "Select * From myTable Where dateField = '" & Format(Date,"mm/dd/yyyy") & "'"

---

Note that you should never store the result of the Format function into a Date variable, as it will be prone to conversion errors - if you do want to store it, use a String variable instead.

For further information on using Dates in Classic VB, see the article How can I work with dates correctly?