datetime to dd/MM/yyyy for inserting into DB

hi there, i have a calendar that i put into a string ilke this

string str = Calendar1.SelectedDate.ToShortDateString();

the result is dd/mm/yyyy date which is great, but for inserting into my DB (MSSQL) it needs to be a datetime field, however when i convert it

Datetime dtDate = Convert.ToDateTime(str);

it takes my date and adds 00:00:00 onto the end and this is not what i want! i just want the dd/mm/yyy how do i do this, it has to be simple but i have been searching for hours and cant find anything, i am using ASP.NET 2 and C#

Thanks

The datetime object MUST contain the time. You don't have any problem, if you display only the date, you are OK.

|||

ah rite ok, but when i try to get the date back from the db where its stored as e.g 15/06/2007 the datetime string passes it as 15/06/2007 00:00:00 and so it wont reconise it, is there any other way to pass it so that it can compare?

|||

Hi,

Actually, when recieve the datetime as string from the database, you may convert it to DateTime type and use ToShortDataString method, in this way,you can get the datetime string without time part. See the sample below:

string dt_s = ds.Tables[0].Rows[0][0].ToString();// Get the datetime from db. DateTime dt = Convert.ToDateTime(dt_s);// Convert it to DateTime type.this.Label1.Text = dt.ToShortDateString();// Get the short date

Thanks.

DateTime issue with some SQL

Hi, I'm having some terrible troubles with inserting DateTime into an MSSQL database and being able to search for data by date. It's all confusing me because of the damn US-style date formatting (no offence).

I have a form that users fill out, and one piece of data happens to be the date (dd/mm/yyyy). I'm using a regex to make sure it can only be entered in the correct format. Now, I know that SQL doesn't have a 'Date only' datatype so I'm using the 'datetime' data type. When the user clicks Process, I use DateTime date = Format.ToDateTime(txtDate.Text) and then insert 'date' into the DB.

When the date (say 11/10/2007) is inserted into the database, it shows up (in MS SQL Server Management Studio Express) as '11/10/2007 12:00:00 AM' which appears to be correct. I think it's correct because if I enter 31/01/2007 it shows up in SQL as '31/01/2007 12:00:00 AM', otherwise wouldn't it crash because there is no 01/31/2007?

Anyway, the insertion *seems* to be working correctly, but it's the search function that's driving me crazy. If a user wants to search for data by date, they enter the date into the textbox and again I format it by doing: DateTime searchDate = Format.ToDateTime(txtSearch.Text)
I then query the database with "SELECT * FROM tablename WHERE Date = '" + searchDate + "' but it doesn't return the correct results!

If I want to see the results for data on 11/10/2007, I have to search for 10/11/2007 otherwise there will be no results shown. Also, if I search for 31/01/2007, it crashes with an error saying something about converting a char to the datetime datatype out of range.
I have edited my Web.Config file so that it contains the globalisation tabs with my culture etc set to en-AU etc...

I'd post my code here, but I'm at home now and the code is at work. I'll post it here soon as I'm sure you'll need it.

Any help is *greatly* appreciated. This is really annoying...DateTime in .NET and SQL just don't mix for me.

When you insert dates into a database (and also whenever you deal with database dates), they should always be sent in the format yyyymmdd. All databases will deal with this format in the correct manner and it will alleviate any formatting issues.

|||

The date searches in SQL can sometimes be tricky because SQL keeps time also with the date. Now, I've observed that when matching a date for = it can be useful to use convert function, but mind well this function actually converts the date to a varchar value and then compares 2 values, but sometimes it can be useful. The sample query you can use is as below.

SELECT *FROM tablenameWHEREconvert (varchar(20) , Date , 103 ) =convert (varchar(20) ,'<textbos date>' , 103 )

If you can post some sample data, code and expected results then we'll be able to solve your problem better.

Hope this will help.

|||

hmm, so to do that i'd take the value from the text box (11/10/2007), convert it to DateTime (DateTime time = Convert.ToDateTime(txtDate.Text), and then what?
How do I make it yyyy/mm/dd?

|||

schuminator:

How do I make it yyyy/mm/dd?

If you have a date in a TextBox, you can just use:

TextBox1.Text.ToString("yyyyMMdd")

Make sure you validate it first though to make sure it is a valid date

|||TextBox1.Text.ToString("yyyyMMdd") and then convert to datetime, or just insert into db as a string? (with datetime as the format in sql)?|||

Just add it to the Parameter (which presumably you will have defined as a date) in your database call or add it as a String if it's part of a SQL Statement. If you are not using stored procedures and parameters, then you should as this will also help alleviate date problems.

|||

Ok, txtDate.Text.ToString("yyyy/MM/dd") didn't work because it wasn't a valid IFormatProvider, but I fixed that by:

IFormatProvider format =new System.Globalization.CultureInfo("en-GB",true);
DateTime searchDate =DateTime.ParseExact(txtDate.Text,"dd/MM/yyyy", format);

I had to do it in dd/mm/yyyy format because if I used yyyy/MM/dd I got this:

String was not recognized as a valid DateTime.
DateTime uploadDate = DateTime.ParseExact(txtDate.Text, "yyyy/MM/dd", format);

Anyway, the uploading of the date still appears to be working in that it appears correct in SQL Server, but retrieving data by the date is still a problem.
I am using SQL Statements and not Stored Procedures (SP). Although, while we're on the topic of SP, maybe you could help me out a bit? I've used them before, but I'm not very proficient so I have problems making ones that will actually be more advantageous than SQL Statements.

Here is how I'm adding data to the DB:
================
DateTime uploadDate =DateTime.ParseExact(txtDate.Text,"dd/MM/yyyy", format);
Mail newMail =newMail(uploadDate, txtStrataPlan.Text.ToUpper(), txtRecipient.Text.ToUpper(), txtSuburb.Text.ToUpper(), txtDetails.Text.ToUpper(), envelope, stamp);
DataBaseModifier.uploadMailToDatabase(newMail);

staticSqlConnection conn;
publicstaticvoid uploadMailToDatabase(Mail mail)
{
conn =SqlLogin.SqlConnect;
SqlDataAdapter adapter =newSqlDataAdapter("SELECT * FROM tblMail", conn);
SqlCommandBuilder builder =newSqlCommandBuilder(adapter);
conn.Open();
DataSet ds =newDataSet();
adapter.Fill(ds,"tblMail");
DataTable table = ds.Tables["tblMail"];
DataRow row = table.NewRow();
int fileID = getFileID(table);
row["MailID"] = fileID;
row["Date"] = mail.getTime();
row["StrataPlanID"] = mail.getStrataPlan();
row["Recipient"] = mail.getRecipient();
row["EnvelopeCount"] = mail.getEnvelopeCount();
row["StampCount"] = mail.getStampCount();
row["Details"] = mail.getDetails();
row["Suburb"] = mail.getSuburb();
table.Rows.Add(row);
adapter.Update(ds,"tblMail");
conn.Close();
}
=========

And here is how I'm retrieving data from the database and putting it into a gridview:
=========
DataBaseModifier.retrieveMailFromDatabase("SELECT * FROM tblMail WHERE Date='" + uploadDate +"' ORDER BY 'StrataPlanID'", mailView);

publicstaticvoid retrieveMailFromDatabase(string selection,GridView grid)
{
conn =SqlLogin.SqlConnect;
SqlCommand command =newSqlCommand(selection, conn);
conn.Open();
SqlDataReader reader = command.ExecuteReader();
grid.DataSource = reader;
grid.DataBind();
conn.Close();
}

As I said, the uploading seems to be working, it's just the retrieval that seems to be causing me problems in that to find data processed on 12/10/2007 I need to enter 10/12/2007.
If a stored procedure would alleviate these problems, any assistance or guidance in creating said stored procedure would be greatly appreciated.

|||

Ok, I think I misunderstood the formatting command you mentioned. I managed to do it like this:

DateTime uploadDate =DateTime.ParseExact(txtDate.Text,"dd/MM/yyyy", format);
string date = uploadDate.ToString("yyyyMMdd");

However, when I try to upload that string to SQL (using the methods posted above), I get this:

String was not recognized as a valid DateTime.
DataBaseModifier.uploadMailToDatabase(newMail);

I have also read elsewhere that 'paramaterised commands with a DateTime structure parameter' is the best way to go...any ideas on how I can accomplish that?
I'm a complete newbie when it comes to stored procedures etc

|||

Ok I got the search function working by changing string searchDate = date.ToString("yyyyMMdd") to:
string searchDate = date.ToString("yyyy-MM-dd");

I'd still like to be using stored procedures if it's going to be 'better', so any help would be awesome.
Thanks

|||

Yes I also face this problem.

I think, your point is like this.

User always in put the date format [dd/mm/yyyy] Right ?

So, you need to try to convert to date format.

--

Dim sDate as Date

sDate = CDate(Me.txtSendDate.Text)

--

and then you can you in user query string.

" WHERE docdte ='" & sDate & "'"

and normally SQL Server datetime format is yyyymmdd.

so, you just put this statement before execute your statement

SET DATEFORMAT dmy

so, your select statement happen like this

SQLSTR = " SET DATEFORMAT dmy " & _

" SELECT * FROM yourtable WHERE yourdatefield ='" & sDate & "'"

This is just for simple SELECT statement, if you try to send the parameter to s procedure you can use like this

-------

ALTER PROCEDURE sp_xxxxx

@.sDate nvarchar(20)

AS

SET DATEFORMAT dmy

SELECT * FROM yourtablename WHERE yourdatefield = @.sDate

GO

-------

and from your client side, you can execucte like this.

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();

string sqlstr = "EXEC your_procedurename '" & sDate & "'";
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlstr;
cmd.Connection = sql.Conn;
cmd.ExecuteNonQuery();

No need to use any parameter object.

arr... so my code is change to C# :) anyway I hope you can change to your code.

do you aware ? in my simple s-procedure, for date parameter, i just use the nvarchar.

as my experience datetime datatype is give alot of problem to me. So, if possible i never you this datatype in parameter.

and then you need to set the culture setting in your IIS.

Goto ASP.Net Configuration Setting and the select the Application Tab

you will see Culture setting set like this

Culture = en-GB

UI culture = en

That's all. I hope you can solve this problem.

If my solution is not complete or not in timely, I am sorry for it.

Soe Thiha.

datetime format problem

Is there any standard function for inserting datetime values to an sql table. I'm having a problem because some operating systems are in english and some operating systemes are in spanish.. When I insert a value '2005-02-15 12:00:00' it works on the english operating system, but it doesn't in the spanish one... any ideas?try GETDATE ( )|||I am getting the date/time from a date time picker in visual basic.. it's not the current date.|||Define date time picker. Sounds like you need one or more of the following:

A smarter date time picker that converts all datetimes to a standard format.
To write a routine that adds intelligence to your picker.
Some VB function that does b. for you. The lack of strong-typing in VB may make this very difficult.

SQL Server has this interesting trait of trying to do exactly what you ask it to do. You may also want to read up on Cast/Convert in SQL BOL.|||???

If it is a VB control I would think it would be returning a VB datetime value, which is just a number with no formatting applied.

But if you are converting this to a string and submitting it to SQL Server as a datetime value (which you shouldn't be doing), then this format should be universally recognized by SQL Server:

yyyy-mm-dd hh:mi:ss

...where hh uses a 24 hour clock.|||If it is a VB control I would think it would be returning a VB datetime value, which is just a number with no formatting applied.Excellent point. So, diegocro, why is the value being sent a text string?|||Am I missing something (very possible)? How else can you send a date value from VB to SQL?|||Unlikely. You don't miss much.

But the VB code could be converting or storing the data in any number of odd ways before it is submitted to SQL Server. A lot can happen to data at point B while it is traveling from point A to point C...|||Or you could try CAST or CONVERT to change the text string to a DATE format.

e.g. CONVERT(datetime,'2005-02-15 12:00:00' ,120) (see BOL for more detail)|||you can do something like this

convert(datetime,datepickervalue,101)|||[sniped]
[sniped]
[sniped]
[sniped]
[sniped]|||ok
CONVERT(datetime,'2005-02-15 12:00:00' ,120) works, thanks a lot.

DATETIME conversion problem in stored procedure

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

hi,

dazfl wrote:

Hi,

I'm having a problem with inserting a datetime value into a database using VB.net and a Stored Procedure. Below is my stored procedure code and VB.net code. Could somebody please tell me what I am doing wrong ... I am almost frustrated to tears .

Stored procedure:

ALTER PROCEDURE dbo.SPTest
@.testvalue DATETIME
AS
INSERT INTO tbl_Rates VALUES (1.2, 1.3, @.testvalue, 'EUR/USD')
RETURN 1

usually return values other than 0 (zero) indicate a procedure error.. so, 1 is usually read as error and not "success"..

VB.NET code:

Dim RatesTA As New RatesDataSetTableAdapters.RatesTableAdapter
Dim ReturnVal As Object
ReturnVal = RatesTA.SPTest(Now)
Console.WriteLine(CType(ReturnVal, Integer))

When I run this the ReturnVal is 0.

I should also mention that my system uses the dd/mm/yyyy date format (Australian) and I am using VB.NET Express and SQL Server Express.

try directly consuming a command and relative parameters, like

Dim cmd As New SqlClient.SqlCommand

With cmd

.CommandText = "schema.procedureName"

.CommandType = CommandType.StoredProcedure

.CommandTimeout = n

.Connection = connection

Dim p As New SqlClient.SqlParameter

With p

.ParameterName = "@.testvalue"

.SqlDbType = SqlDbType.DateTime

.Value = DateTime.Now

.Direction = ParameterDirection.Input

End With

.Parameters.Add(p)

End With

cmd.ExecuteNonQuery()

cmd.Dispose()

cmd = Nothing

so that you can check (1st important addition of the command and parameters behaviour) and validate parameters initialization... more.. the parameter automatically handles this kind of conversions..

regards

Dates when inserting a record

Is it possible to have sql server automatically record date and time (in a designated field)when a record is created in the db? This may seam basic but it has caused me a lot of grief.You can use database trigger for automatic firing.|||yes it can
just use when creating the table for that field a default

something like this:

create table xxx (
id int not null,
dateadded datetime not null default(getdate())
)|||so if I used get date () in the field and queried the table. Will it show me the date and time as of that momnet or will it have embedded the date and time the record was added...|||getdate() is a function that returns the current date when the record is actually added. it's not a column name. so if u want to query the date when the record was added, u need to query that column.|||getdate() is not working. It works in the sense that it displays system date and time. The problem is that it updates every column whenever I view the table or query it...

I need an option which will ensure that each record added inserts into the designated Table/Field the actual system time at which the record was inserted.|||Foefie's solution should work for you. Specifying the Default value of a column to be GETDATE() will insert the SQL Server's current system date/time into the column when the record is inserted into the database. It willl not fluctuate or vary with queries -- it is concretely written into the record. We use this approach all of the time without incident.

Terri|||If you use GETDATE() as default value in the column, you can control what happens.

What I mean by that is, if you want the date to reflect the datetime of last change, then you can include the field in any update, but specify DEFAULT as the value to be inserted. This will then cause the date to be update.

Alternatively (if you don't want it to change on every update, and always reflect the original datetime when the record was created), you should omit the field from any update statements and it will remain as originally inserted (you don't need to specify the field on any insert statements either, since on the first insert any fields for which you don't explicitly specify a value should get the default)

HTH

Anton|||Thank you. This explains it much better. The confusion has been due to the fact that while using Enterprise Manager, rightclicking on the table in question and selecting return all rows, I have noticed that it always updates the date and time. I just assumed that the same would apply anytime I queried the data in the table.

Thanks!

Date/Time overlaps - urgent.

Hi,

What I have is a booking table, and when updating/inserting I need to
ensure that there are no date/time overlaps. The problem I'm having is
that while the following script works for events on the same day, it
fails miserably when a booking starts on a previous day.

I've just spent the last hour going through previous posts and just
can't seem to it right.

My DB structure (Sql Server 2000):

Table: CollateralBooking

-- CBID - int, identity(1, 1)

-- CBcPartNumber - varchar(50) (foreign key)

-- CBdDateTimeFrom - smalldatetime

-- CBdDateTimeTo - smalldatetime

-- CBcAlias - varchar(50) (foreign key)

My current script (in a stored proc):

IF (SELECT COUNT(*) FROM CollateralBooking
WHERE (((@.CBdDateTimeFrom > CBdDateTimeFrom) AND (@.CBdDateTimeFrom < CBdDateTimeTo))
OR ((@.CBdDateTimeTo > CBdDateTimeFrom) AND (@.CBdDateTimeTo < CBdDateTimeTo)))
AND (CBcPartNumber = @.CBcPartNumber)) <> 0
BEGIN
-- Return an error.
END

-- ... Other checks & finally, the insert/update.

--
Posted via http://dbforums.comFirst, add a constraint, if you haven't already, to ensure that the "from"
datetime is less than the "to" datetime (I've guessed your primary key and
only included the essential columns)

CREATE TABLE CollateralBooking (cbid INTEGER UNIQUE, cbcpartnumber INTEGER,
cbddatetimefrom DATETIME NOT NULL, cbddatetimeto DATETIME NOT NULL, CHECK
(cbddatetimefrom<cbddatetimeto), PRIMARY KEY (cbcpartnumber,
cbddatetimefrom))

IF EXISTS
(SELECT *
FROM CollateralBooking AS A
JOIN CollateralBooking AS B
ON A.cbcpartnumber=B.cbcpartnumber AND A.cbid<>B.cbid
AND NOT (A.cbddatetimefrom > B.cbddatetimeto
OR A.cbddatetimeto < B.cbddatetimefrom))
/* Raise an error */

--
David Portas
----
Please reply only to the newsgroup
--|||Thanks David :) It's working well now.

On a side note, CBID was the primary key.

--
Posted via http://dbforums.com