Date Formatting from VB.NET to SQL Server query

[BrianBurgit]

I have an app that accepts a date as input, in the format of mm/dd/yyyy. I now need to use that date in a query against a SQL Server Table where the date format is yyyy-mm-dd 00:00:00.000. My SQL query converts that date like this - CONVERT(CHAR(10),InventoryDate,120). My question is how do I format the date in VB.NET so that I can plug it into my query?

 

[Glowworm27]

try this

MyStr = Format(MyDateTime, "yyyy MM dd hh:mm:ss")

Visual Basic Language Reference

User-Defined Date/Time Formats (Format Function)See Also
Format Function | Predefined Date/Time Formats (Format Function) | User-Defined Numeric Formats (Format Function)
Requirements
Namespace: Microsoft.VisualBasic

Module: Strings

Assembly: Microsoft Visual Basic .NET Runtime (in Microsoft.VisualBasic.dll)
The following table shows characters you can use to create user-defined date/time formats. Unlike in previous versions of Visual Basic, these format characters are case-sensitive

Character Description
) Time separator. In some locales, other characters may be used to represent the time separator. The time separator separates hours, minutes, and seconds when time values are formatted. The actual character used as the time separator in formatted output is determined by your system's LocaleID value.
(/) Date separator. In some locales, other characters may be used to represent the date separator. The date separator separates the day, month, and year when date values are formatted. The actual character used as the date separator in formatted output is determined by your locale.
(%) Used to indicate that the following character should be read as a single-letter format without regard to any trailing letters. Also used to indicate that a single-letter format is read as a user-defined format. See below for further details
d Displays the day as a number without a leading zero (for example, 1). Use %d if this is the only character in your user-defined numeric format.
dd Displays the day as a number with a leading zero (for example, 01).
ddd Displays the day as an abbreviation (for example, Sun).
dddd Displays the day as a full name (for example, Sunday).
M Displays the month as a number without a leading zero (for example, January is represented as 1). Use %M if this is the only character in your user-defined numeric format.
MM Displays the month as a number with a leading zero (for example, 01/12/01).
MMM Displays the month as an abbreviation (for example, Jan).
MMMM Displays the month as a full month name (for example, January).
gg Displays the period/era string (for example, A.D.)
h Displays the hour as a number without leading zeros using the 12-hour clock (for example, 1:15:15 PM). Use %h if this is the only character in your user-defined numeric format.
hh Displays the hour as a number with leading zeros using the 12-hour clock (for example, 01:15:15 PM).
H Displays the hour as a number without leading zeros using the 24-hour clock (for example, 1:15:15). Use %H if this is the only character in your user-defined numeric format.
HH Displays the hour as a number with leading zeros using the 24-hour clock (for example, 01:15:15).
m Displays the minute as a number without leading zeros (for example, 12:1:15). Use %m if this is the only character in your user-defined numeric format.
mm Displays the minute as a number with leading zeros (for example, 12:01:15).
s Displays the second as a number without leading zeros (for example, 12:15:5). Use %s if this is the only character in your user-defined numeric format.
ss Displays the second as a number with leading zeros (for example, 12:15:05).
F Displays fractions of seconds. For example ff will display hundredths of seconds, whereas ffff will display ten-thousandths of seconds. You may use up to seven f symbols in your user-defined format. Use %f if this is the only character in your user-defined numeric format.
T Uses the 12-hour clock and displays an uppercase A for any hour before noon; displays an uppercase P for any hour between noon and 11:59 P.M. Use %t if this is the only character in your user-defined numeric format.
tt Uses the 12-hour clock and displays an uppercase AM with any hour before noon; displays an uppercase PM with any hour between noon and 11:59 P.M.
y Displays the year number (0-9) without leading zeros. Use %y if this is the only character in your user-defined numeric format.
yy Displays the year in two-digit numeric format with a leading zero, if applicable.
yyy Displays the year in four digit numeric format.
yyyy Displays the year in four digit numeric format.
z Displays the timezone offset without a leading zero (for example, -8). Use %z if this is the only character in your user-defined numeric format.
zz Displays the timezone offset with a leading zero (for example, -08)
zzz Displays the full timezone offset (for example, -08:00)

Example
The following are examples of user-defined date and time formats for December 7, 1958, 8:50 PM, 35 seconds:

Format Displays
M/d/yy 12/7/58
d-MMM 7-Dec
d-MMMM-yy 7-December-58
d MMMM 7 December
MMMM yy December 58
hh:mm tt 08:50 PM
h:mm:ss t 8:50:35 P
H:mm 20:50
H:mm:ss 20:50:35
M/d/yyyy H:mm 12/7/1958 20:50

Requirements
Namespace: Microsoft.VisualBasic

Module: Strings

Assembly: Microsoft Visual Basic .NET Runtime (in Microsoft.VisualBasic.dll)

See Also
Format Function | Predefined Date/Time Formats (Format Function) | User-Defined Numeric Formats (Format Function)



--------------------------------------------------------------------------------

Send feedback on this topic to Microsoft

© Microsoft Corporation. All rights reserved.


George Oakes
Check out this awsome .Net Resource!

http://www.learnvisualstudio.net?referedby=54275&cam=Tek-Tips