Sql Date time conversion

Question

Post reply

Sql Date time conversion

zubairm76

SSC Journeyman

Points: 90
More actions
February 3, 2009 at 12:41 am

#200879

hi all,
I have a column called WBsstartdate (storing dates), that has been created as a Varchar data type.
If I run a convert on the table, my date comes out in the right format.
(Eg: 2006-04-03 00:00:00). This is fine.
However when I run this date as an sql query in a programme in C#, and save to xml the result is 2006-04-03 00:00:00+02:00.
I need to get rid of the +02:00 part. If I check my windows date time settings, it is set to GMT+2:00. This could be the reason but changing my date time settings still returns the same value i.e: 2006-04-03 00:00:00+02:00.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 1

Use CONVERT (data_type , expression , style) function with style=20 or 120 or 21 or 121

Regards,
Nitin

zubairm76 SSC Journeyman Points: 90 More actions · Answer 2

I have tried that, but my XML result is still:

2006-05-25T00:00:00+02:00.

zubairm76 SSC Journeyman Points: 90 More actions · Answer 3

I have tried that, but my XML result is still:

2006-05-25T00:00:00+02:00.

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 4

Are you reading data in dataset and then converting to XML or generating XML from SQL (using FOR XML)?

Regards,
Nitin

zubairm76 SSC Journeyman Points: 90 More actions · Answer 5

I am reading the data in a dataset and then converting to XML or

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 6

Then I think you need to do something while converting to XML. It has nothing to do with SQL statement.

I think return data as string from SQL sql and then convert to XML.

Regards,
Nitin

zubairm76 SSC Journeyman Points: 90 More actions · Answer 7

Almost works.

When I convert using VarChar:

select CONVERT (varchar(11) ,WbsStartDate,126) from wbsdata

the result is May 25 2006 which is almost ok.

I need the format to be 2006 May 25. I have tried different styles(126,111) but there is no change on the data. It still reads May 25 2006.

Anam Verma SSCertifiable Points: 5365 More actions · Answer 8

zubairm76 (2/3/2009)
Almost works.
When I convert using VarChar:
select CONVERT (varchar(11) ,WbsStartDate,126) from wbsdata
the result is May 25 2006 which is almost ok.
I need the format to be 2006 May 25. I have tried different styles(126,111) but there is no change on the data. It still reads May 25 2006.

from management studio, try to output your results directly to text file and check the date format there.

i ran a small test and it all seems to be working fine.

declare @vdate varchar(20)

set @vdate = '31.12.2008'

select convert(varchar(21),convert(datetime,@vdate,104),126)

Output

---------------------

2008-12-31T00:00:00

(1 row(s) affected)

zubairm76 SSC Journeyman Points: 90 More actions · Answer 9

Problem has been sorted out.

The problem is an Xml problem. When converting to XMldata document, the date gets adjusted. I used the following code to sort out the problem.

After declaring your datadocument add the following:

foreach (DataRow r in Yourxmldocument.Tables[0].Rows)

{

if (r["Wbsstartdate"].ToString() ==System.DBNull.Value.ToString())

{

}

else

{

r["Wbsstartdate"]=string.Format(r["Wbsstartdate"].ToString)); r["Wbsstartdate"] = (DateTime.Parse(r["Wbsstartdate"].ToString()).ToString("yyyy-MM-dd"));

}