Convert dat time to EN US format

isaackhazi
isaackhazi used Ask the Experts™
on
I have a VB.NET application with SQL server 2008 as the backend DB.

I live in Thailand so the current date here is :

2553-04-07 14:09:25.000

Usually all data entry PC have the application installed and the PC's regional setting is set to EN US.
Unfortunately last week the data entry team were doin their job from home and their setting athome was TH instead of EN US.

Now I have around 1000 records with a time stamp with the Thai date instead of the standard date.

How do I update it either in SQl directly oir by writing a small script in VB so that I can change these current date stamps to the current date seeting ENUS

What i need is to convert  2553-04-07 14:09:25.000 -> 2010-04-07 14:09:25.000

subtract  543 years from the year.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Hi,

  In sql server...

UPDATE mytable set mydate = dateadd(yy,-543,mydatecolumn)

Author

Commented:
thats great!!...

Now the only problem is Ive got to get it from

2010-07-06 ......
2010-06-07......
basically switch between month and day....
i dont remember what format mm/dd or dd/mm is

Author

Commented:
The dates are in this format now  :


2010-05-07 15:30:09.000

How to convert it to this : switch with month in the middle

2010-07-05 15:30:09.000
Commented:
Hi

If it is a datetime column, it will be according to regional settings.
You can do this way

Update mytable set mydatecolumn=year(mydatecolumn) + '-' + month(mydatecolumn) + '-' + day(mydatecolumn)

you update the above satement with the format you want.

Commented:

select convert(varchar,YEAR(getdate())) + '-' + right('00' + convert(varchar,MONTH(getdate())),2) + '-' + right('00' + convert(varchar,DAY(getdate())),2)

Top Expert 2010
Commented:
isaackhazi,If the data type being used is [small]datetime, then there is no format: SQL Server stores the data as paired int values (one for the date, one for the time) and the "format" is just an artifact of how the values are displayed to you.So, what data type are you using?Patrick
you can use

set dateformat mdy
you can use it
SET DATEFORMAT dmy;
SET DATEFIRST to U.S. English default value of 7.
SET DATEFIRST 7;

SELECT CAST('1999-1-1' AS datetime2) AS SelectDate
    ,DATEPART(dw, '1999-1-1') AS DayOfWeek;
-- January 1, 1999 is a Friday. Because the U.S. English default
-- specifies Sunday as the first day of the week, DATEPART of 1999-1-1
-- (Friday) yields a value of 6, because Friday is the sixth day of the
-- week when you start with Sunday as day 1.

SET DATEFIRST 3;
-- Because Wednesday is now considered the first day of the week,
-- DATEPART now shows that 1999-1-1 (a Friday) is the third day of the
-- week. The following DATEPART function should return a value of 3.
SELECT CAST('1999-1-1' AS datetime2) AS SelectDate
    ,DATEPART(dw, '1999-1-1') AS DayOfWeek;
GO

Author

Commented:
Good

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial