|
I am trying to insert string date to datetime column with the following way.(Sql2000)
CONVERT(DATETIME, @To, 103)
but this is not saving the DD/MM/YYYY format in the webserver, but it is working in my local machine.In the webserver sql it is saving as MM/DD/YYYY format.
|
|
|
|
|
Use a parameterized query, that will do the magic around the date format.
|
|
|
|
|
It is based on how the systems are setup. Cannot remember the SET command and have to go to work. Sorry.
|
|
|
|
|
SET DATEFORMAT DMY
that the one you were thinking of?
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
|
Your string-representation is a culture-dependent format, a way of displaying a date in a specific culture. Date (and time) itself is not a string, but a number.
Make sure it's a DATE column in the database, and pass a Date (not a string) to the query. Use parameters as suggested.
using (var con = new SqlConnection(connectionString))
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM SomeTable WHERE MyDate = @MyDate";
cmd.Parameters.AddWithValue("@MyDate", DateTime.Now);
}
Bastard Programmer from Hell 
|
|
|
|
|
the purpose of a database is to store information, not to format it. Store dates and datetimes in fields with the appropriate type, and let your applications take care of formatting when presenting results to the user. Do not try and have the database format stuff, you will get lots of trouble and never get satisfactory results.
And as others have said, use SQL parameters rather than SQL string concatenation, to feed dates and datetimes to the database; thus avoiding all conflicts with regional settings and the like.
|
|
|
|
|
If the column is a DATETIME, then the statement "it is saving as MM/DD/YYYY format" is untrue.
If the output of SELECT thedate FROM table shows different formats when executed on different systems, it is because of the "Region and Language" settings of the systems -- see the control panel -- and this is correct behaviour. If you want to override this behaviour (you shouldn't) then format it via the query.
And I strongly reccommend ISO 8601 format YYYY-MM-DD.
|
|
|
|
|
Put it like,
CONVERT( VARCHAR, @To AS DATETIME, 103 ) )
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
How to make a function in sql server-2008?
|
|
|
|
|
Have a read of this MSDN: Create Function[^]
there are some examples at the bottom of the page
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
CREATE FUNCTION 'functionname'
AS
--
--
--
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
Table-Valued functions are awesome, like a view with input parameters. Like this:
CREATE FUNCTION [dbo].[Function_Name] (@inputParameter int) RETURNS table AS Return (<select clause> <from clause> WHERE <filterFieldName> = @inputParameter [<group by clause>])
Works in sql 2000+.
"Go forth into the source" - Neal Morse
|
|
|
|
|
Hi,
I want to send mails to the attendee of a particular event. So please tell me how can i send emails in c#.
Thanks..In advance
|
|
|
|
|
There's plenty of examples on the 'net showing how to send emails from C#.
How do I send mail using C#?[^]
Hope this helps getting you started
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
|
You can create a Window Service for same.
- Happy Coding -
Vishal Vashishta
|
|
|
|
|
A quick question about the mapping between CLR types and their TSQL ones.
From what I can see within the CLR you have SQLString and SQLChars which are nvarchar(4000) and nvarchar(max)...what if the callee expects to pass you a varchar(5)?
Can you tell the CLR the length it should expect? Or would you have to specifically code this check?
|
|
|
|
|
I wouldn't bother -- the engine will figure it out. And deal with an Exception if one is thrown.
|
|
|
|
|
I'm having some trouble with a JOIN that involves an OR (in SQL Server). For example:
SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
OR A.Field2=B.Field2
Quite correctly this produces two output rows for each row in TableA that matches TableB via both Field1 and Field2* -- but for this JOIN I want to output only one row when both match (preferably the result of the Field1 match, and only report the Field2 match if Field1 doesn't match).
* Clarification -- when it matches two rows in TableB; one via Field1 and the other via Field2.
I haven't done much searching for pointers because I don't think it's possible, however I'm posting here just in case someone here knows of a way or a simple (SQL only) work-around.
Edit -- Here's an example:
SELECT * FROM TableA
ID Field1 Field2
-- ------ ------
10 A E
11 E D
12 A D
SELECT * FROM TableB
ID Field1 Field2
-- ------ ------
20 A B
21 C D
10 will match only 20 ; 11 will match only 21 ; 12 will match both 20 and 21 -- so I want 20.
ID Field1 Field2 ID Field1 Field2
10 A E 20 A B <-- I want this row
11 E D 21 C D <-- I want this row
12 A D 20 A B <-- I want this row
12 A D 21 C D <-- I don't want this row
Luc's and pmpdesign's suggestions yield the same output.
Here's a variation of Bernhard's suggestion, which seems to work:
WITH cte1 AS
(
SELECT A.ID aID
, A.Field1 aField1
, A.Field2 aField2
, B.ID bID
, B.Field1 bField1
, B.Field2 bField2
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
)
, cte2 AS
(
SELECT A.ID aID
, A.Field1 aField1
, A.Field2 aField2
, B.ID bID
, B.Field1 bField1
, B.Field2 bField2
FROM TableA A
INNER JOIN TableB B
ON A.Field2=B.Field2
)
SELECT *
FROM cte1
UNION ALL
SELECT C2.*
FROM cte2 C2
LEFT OUTER JOIN cte1 C1
ON C2.aID=C1.aID
WHERE C1.aID IS NULL
modified 6-Jun-12 16:38pm.
|
|
|
|
|
Is it possible to try this through some stored procedure?
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I don't know whether or not such a stored procedure could be devised, but if so, it could be executed.
|
|
|
|
|
Ah, I didn't know logic operators were allowed in the ON clause, but as they are, why not just exploit them to the fullest:
SELECT *
FROM TableA A
INNER JOIN TableB B
ON A.Field1=B.Field1
OR (A.Field1<>B.Field1 AND A.Field2=B.Field2)
Maybe now is the time for a ![Java | [Coffee]](https://codeproject.freetls.fastly.net/script/Forums/Images/coffee.gif) ?
|
|
|
|
|
Checked it out, and it seems to work 
""Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Thanks.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
