Insert into datetime picker values with specified format?

Question

0.00/5 (No votes)

See more:

Datum sql format is date
Vrijeme poziva is sql format time(7)

 Working fine but in datagrid have values 
09.05.2018 00:00:00
09:51:32.25655565
 
In sql value is 2018-05-09
09:51:32.25655565
 
I need 
 
09.05.2018
09:51:32
 
Some help?

What I have tried:

"INSERT INTO dbo.registar (datum, vrijeme_poziva)" +  
" VALUES ('" + datumDateTimePicker.Value.Date.ToString("yyyyMMdd") + "', '" + vrijeme_pozivaDateTimePicker.Value.TimeOfDay.ToString("HHmmss") + "')";

Posted 8-May-18 22:07pm

Goran Bibic

Updated 8-May-18 22:29pm

Add a Solution

Comments

F-ES Sitecore 9-May-18 4:48am

Dates represent a moment in time, have a "format" when you want to represent them as a string to show. So you need to change how you think about your data, keep things a DateTime where possible, store the data as a date in a date or datetime field in your database, and the only time you worry about its "format" is when you show it on the screen. Most grids etc have a way of representing your date in the format of your choice, but in general always use date fields right up until you want to show them on screen and at that point show the date in your chosen format.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-05-08T22:31:00

THere are two things wrong here, and I suspect they are related.
The first is that you are converting a perfectly good DateTime value to a string and sending that to SQL via string concatenation - and that implies that the rest of your code uses concatenation as well. That's dangerous, very dangerous! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SQL

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

SQL

DROP TABLE MyTable;

A perfectly valid "delete the table" command

SQL

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

The second is that the conversion to a string implies that you are storing your dates in the DB as string values - and that's a bad idea as well. If you use DATE, DATETIME, or DATETIME2 columns instead, then you can apply a format to the DataGrid which will automatically display the DateTime value from the DB directly in any format you want. As a string, it gets a lot more complicated, because you need to parse the date to a DateTime first, and then format it back to a string for display and that gets very messy - because I guarantee you that somehow an invalid date will get into your DB - it always does - and you have to allow for that in your parsing.

Sort out your SQL Injection as a very high priority, and then get your DB right - then start thinking about display formats!