Dateformat in SQL SERVER 2005

HI,

How do i get date in different format as output.
User gives a string 11/21/2005
and also gives dateformat MM/DD/YYYY
I have to insert this value in SQL SERVER 2005


anusdesaiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
select convert(datetime, '11/21/2005', 101)

alternatively:
SET DATEFORMAT MDY
select convert(datetime, '11/21/2005' )

0
anusdesaiAuthor Commented:
Hi,
i get an error when i type SET DATEFORMAT MDY
select convert(datetime, '11/21/2005' )
Let Set statements are no longer supported...


0
anusdesaiAuthor Commented:
I am using vb.net 2.0
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

anusdesaiAuthor Commented:
Hi,
I ned a function in my application to convert the string in the date format given by the user and then insert the value in sql.

Regards.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if the user gives the date in the format you told in the question, then you simply put that value into the sql as I showed above.
0
anusdesaiAuthor Commented:
Hi,
How can i put this in query?


0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please show the code that you use in vb.net to run a query.
0
anusdesaiAuthor Commented:
Besides I dont want to change the date format of sql server ...
Example...user has given
05/02/2005
he mentions
MM/DD/YYYY....
WHEN I CONVERT the string  IT TO A DATE
IT shows 05/02/2005 which according to system date is DD/MM/YYYY AND WHEN I SAVE IN SQL

YYYY/MM/DD IT gets saved as 2005/02/05 which is wrong as i expect 2005/05/02
How can I do it?
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please, show the code that you use in vb.net to run a query.
0
anusdesaiAuthor Commented:
Hi,
Sorry for the delay..
I have a column in datagridview for entering  date format
 Me.clmDTF.DataPropertyName = "DATA_TYPE_FORMAT"
        Me.clmDTF.HeaderText = "SPECIAL TYPE"
        Me.clmDTF.Name = "clmDTF"
I am loading a file in database depending on the  columns given.
In file the data is:
23/12/2004   xys  fgfg  

Query
   strsql = "Insert into " & frmmain.txttablename.Text & "( refno," & frmmain.txtdateText & ")VALUES(@refno,@" & frmmain.txtdate.Text & ")"

                        Dim command As New SqlCommand(strsql, cndb)

                        If fieldname <> "" Then
                            command.Parameters.AddWithValue("@refno", fieldname)
                            command.Parameters.AddWithValue("@" & frmmain.txtdate.Text & "", frmmain.txtfielddate.Text)


                            command.ExecuteNonQuery()
                        End If



       '''''''''''''''''''
 & frmmain.txtprmt.Text & is for the formated date.


Regards.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just try to change the strsql contents like this:

strsql = "SET DATEFORMAT DMY   Insert into " & frmmain.txttablename.Text & "( refno," & frmmain.txtdateText & ")VALUES(@refno,@" & frmmain.txtdate.Text & ")"

0
r1937Commented:
Hi,
To answer your original question:

Dim strMyDate As String
strMyDate = Format(DateTime.ParseExact("11-21-2005", "MM-dd-yyyy", _
Globalization.CultureInfo.CurrentCulture), "yyyy-MM-dd")

This will convert from MM-dd-yyyy to yyyy-MM-dd

To apply to your code, just replace the example date "11-21-2005" with frmmain.txtdate.Text and SQL to be like:

   strsql = "Insert into " & frmmain.txttablename.Text & "( refno," & frmmain.txtdate.Text & ")VALUES(@refno, '" & strMyDate & "')"

I am not sure about your SQL, but make sure date goes like '2005-11-21' - (within apostrophes - as I can remember).

Hope this will help, Good luck.

r1937
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anusdesaiAuthor Commented:
Hi Experts,

Thanks a lotz..will try and get back..

Regards.
0
anusdesaiAuthor Commented:
Hi r1937,

It works great only if MM is in Uppercase but, the user can enter it in lower case in that case it takes the minutes...is there any method in either case it will work correct for foramttting the date.
0
r1937Commented:
What about taking the entered date format into a string and then do a validation check or write code to convert mm to MM.
0
anusdesaiAuthor Commented:
Yes works gr8

Cheers mate...
0
anusdesaiAuthor Commented:
HI,

Can you suggest what it will be in oracle 9i.

Regards.
0
r1937Commented:
I am not familiar with Oracle, but most simple SQL will be the same.
As I think Oracle dates goes like "dd-MMM-yyyy". Again I do not know Oracle.
0
anusdesaiAuthor Commented:
I found it...
Cheers Mate
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.