date query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

date query help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-03 : 06:43:57

I had data that went in wrong

the date in the db went in as

2008-01-06 08:54:18.000

when it should have been 2008-06-01 08:54:18.000

is there an sql statement that i can run to just change all these records to the reverse - meaning to change the date from 01-06 to 06-01
and 02-06 to 06-02 leaving the time as is.

can someone help me with this?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-06-03 : 06:52:04

try this

SELECT Date, CAST(
CAST(YEAR(Date) AS VARCHAR(10)) + '-' +
CAST(DAY(Date) AS VARCHAR(10)) + '-' +
CAST(MONTH(Date) AS VARCHAR(10)) + ' ' +
CONVERT(VARCHAR(30), Date, 114)
AS DATETIME)
FROM tbl
WHERE DAY(Date) < 13

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 06:53:35

May be this:-

SET dateformat mdy

UPDATE YouTableSET date=CAST(DAY(datecol) AS varchar(2))+'/'+ CAST(MONTH(datecol) AS varchar(2))+ '/'+ CAST(YEAR(datecol) as varchar(4)) + ' ' +CONVERT(varchar(10),datecol,108)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-03 : 07:18:49

Which datatype is the date stored in?

E 12°55'05.25"
N 56°04'39.16"

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2008-06-03 : 07:24:30

thanks that worked visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-03 : 08:02:26