DateTime format to SQL format using C

Question

I am trying to save the current date time format from C  and convert it to an SQL Server date format like so yyyy-MM-dd HH mm ss so I can use it for my UPDATE query   This was my first code   DateTime myDateTime   DateTime Now  string sqlFormattedDate   myDateTime Date ToString  yyyy-MM-dd HH mm ss      The output on date is okay  but time is always  12 00 00  so I changed my code to the following   string sqlFormattedDate   myDateTime Date ToString  yyyy-MM-dd            myDateTime TimeOfDay ToString  HH mm ss      It gave me this compile error      FormatException was unhandled   And suggested I need to parse  So I tried doing this to my code as per my research here in StackOverflow   string sqlFormattedDate   myDateTime Date ToString  yyyy-MM-dd            myDateTime Parse TimeOfDay ToString  HH mm ss      or  string sqlFormattedDate   myDateTime Date ToString  yyyy-MM-dd            myDateTime tryParse TimeOfDay ToString  HH mm ss      But it s telling me that it s a method which is not valid for given context  I tried searching for solutions to my problem and I have been stuck for two hours now  I m still a bit new in C   can you help me please

User · Answer

DateTime date1   new DateTime         date1   Convert ToDateTime TextBox1 Text       Label1 Text    date1 ToLongTimeString       11 00 AM     Label2 Text   date1 ToLongDateString      Friday  November 1  2019      Label3 Text   date1 ToString        Label4 Text   date1 ToShortDateString        Label5 Text   date1 ToShortTimeString

User · Answer

Your problem is in the  Date  property that truncates DateTime to date only  You could put the conversion like this   DateTime myDateTime   DateTime Now  string sqlFormattedDate   myDateTime ToString  yyyy-MM-dd HH mm ss        lt - No Date ToString

User · Answer

only you put  T  DateTime Now ToLongTimeString      2015-02-23

User · Answer

Your first code will work by doing this  DateTime myDateTime   DateTime Now  string sqlFormattedDate   myDateTime ToString  yyyy-MM-dd HH mm ss      Remove myDateTime Date part

User · Answer

if you want to store current date in table so you can use      GETDATE      or pass this function as a parameter   eg   update tblname set curdate GETDATE   where colname 123

User · Answer

I think the problem was the two single quotes missing   This is the sql I run to the MSSMS   WHERE checktime  gt    2019-01-24 15 01 36 000  AND checktime  lt    2019-01-25 16 01 36 000    As you can see there are two single quotes  so your codes must be   string sqlFormattedDate         myDateTime Date ToString  yyyy-MM-dd           myDateTime TimeOfDay ToString  HH mm ss            Use single quotes for every string in MSSQL or even in MySQL  I hope this helps

User · Answer

try this below   DateTime myDateTime   DateTime Now  string sqlFormattedDate   myDateTime ToString  yyyy-MM-dd HH mm ss fff

User · Answer

If you wanna update a table with that DateTime  you can use your SQL string like this example  int fieldId  DateTime myDateTime   DateTime Now string sql   string Format   quot UPDATE TableName SET DateFieldName   0   WHERE FieldID  1  quot   myDateTime ToString  quot yyyy-MM-dd HH mm ss quot    fieldId ToString

User · Answer

Your problem is in the Date property that truncates DateTime to date only  You could put the conversion like this   DateTime myDateTime   DateTime Now   string sqlFormattedDate   myDateTime ToString  yyyy-MM-dd HH mm ss

User · Answer

Why not skip the string altogether    SqlDateTime myDateTime   DateTime Now

User · Answer

The Answer i was looking for was   DateTime myDateTime   DateTime Now  string sqlFormattedDate   myDateTime ToString  yyyy-MM-dd HH mm ss      I ve also learned that you can do it this way   DateTime myDateTime   DateTime Now  string sqlFormattedDate   myDateTime ToString myCountryDateFormat     where myCountryDateFormat can be changed to meet change depending on requirement   Please note that the tagged  This question may already have an answer here   has not actually answered the question because as you can see it used a   Date  instead of omitting it  It s quite confusing for new programmers of  NET

User · Answer

Another solution to pass DateTime from C  to SQL Server  irrespective of SQL Server language settings supposedly that your Regional Settings show date as dd MM yyyy  German standard  104   then DateTime myDateTime   DateTime Now  string sqlServerDate    quot CONVERT date   quot  myDateTime  quot   104  quot     passes the C  datetime variable to SQL Server Date type variable  considering the mapping as per  quot 104 quot  rules   Sql Server date gets yyyy-MM-dd If your Regional Settings display DateTime differently  then use the appropriate matching from the SQL Server CONVERT Table see more about Rules  https   www techonthenet com sql server functions convert php

User · Answer

The correct answer was already given  use parameters   Formatting a date and passing it as a string to SQL-Server can lead to errors as it depends on the settings how the date is interpreted on the server side  In europe  we write  1 12 2012  to indicate december 1st 2012  whereas in other countries this might be treated as january 12th   When issuing statements directly in SSMS I use the format yyyymmdd which seem to be quite general  I did not encounter any problems on the various installations I worked on so far   There is another seldom used format  which is a bit weird but works for all versions   select   d  2013-10-01      will return the first of october 2013    select   ts  2013-10-01 13 45 01      will return october 1st  1 45 01 PM  I strongly advice to use parameters and never format your own SQL code by pasting together homegrown formatted statement fragments  It is an entry for SQL injection and strange errors  formatting a float value is another potential issue

User · Answer

Using the standard datetime format  s  will also ensure internationalization compatibility  MM dd versus dd MM    myDateTime ToString  s       gt  2013-12-31T00 00 00   Complete Options   code  sample result   d  6 15 2008  D  Sunday  June 15  2008  f  Sunday  June 15  2008 9 15 PM  F  Sunday  June 15  2008 9 15 07 PM  g  6 15 2008 9 15 PM  G  6 15 2008 9 15 07 PM  m  June 15  o  2008-06-15T21 15 07 0000000  R  Sun  15 Jun 2008 21 15 07 GMT  s  2008-06-15T21 15 07  t  9 15 PM  T  9 15 07 PM  u  2008-06-15 21 15 07Z  U  Monday  June 16  2008 4 15 07 AM  y  June  2008    h mm ss ff t   9 15 07 00 P   d MMM yyyy   15 Jun 2008   HH mm ss f   21 15 07 0   dd MMM HH mm ss   15 Jun 21 15 07    Mon t h   M   Month  6   HH mm ss ffffzzz   21 15 07 0000-07 00   Supported in  NET Framework  4 6  4 5  4  3 5  3 0  2 0  1 1  1 0 Reference  DateTime ToString Method

User · Answer

Let s use the built in SqlDateTime class new SqlDateTime DateTime Now  ToSqlString    But still need to check for null values  This will throw overflow exception new SqlDateTime DateTime MinValue  ToSqlString    SqlDateTime overflow  Must be between 1 1 1753 12 00 00 AM and 12 31 9999 11 59 59 PM

[c#] DateTime format to SQL format using C#

Examples related to c#

Examples related to sql

Examples related to datetime