Confuse while Datepart - 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 2008 Forums

Transact-SQL (2008)

Confuse while Datepart

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2011-08-19 : 03:52:29

Hi accidentally I stored data in Wrong format. Means if I have to store 11 Apr 2011 and I stored 4 nov 2011. There are lots of rows and I'm going to change it for that I tried this


Update CustMast1 Set RecoveryDate= datepart(yyyy,RecoveryDate) + '-' + datepart(DD,RecoveryDate) + '-' + DATEPART(MM,RecoveryDate)

But it doesn't works even I added time part in above query. Can anyone suggest me the best way. Thanking you.

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 03:53:54

the best way is to use proper datatype for your column ie make it datetime and to pass the date values to it in iso format ie yyyy-mm-dd to avoid confusion caused due to language and regional settings.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ravininave
Posting Yak Master

111 Posts

Posted - 2011-08-19 : 03:56:27

quote:
Originally posted by visakh16

the best way is to use proper datatype for your column ie make it datetime and to pass the date values to it in iso format ie yyyy-mm-dd to avoid confusion caused due to language and regional settings.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yeh. bt what is the solution nw

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-19 : 04:01:45

you mean the data is stored in string ?

All dates are stored wrongly ?

KH
[spoiler]Time is always against us[/spoiler]

ravininave
Posting Yak Master

111 Posts

Posted - 2011-08-19 : 04:02:51

quote:
Originally posted by khtan

you mean the data is stored in string ?

All dates are stored wrongly ?

KH
[spoiler]Time is always against us[/spoiler]

Data is stored in DateTime format. Yeh it stored wrongly

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-19 : 04:05:41

if you have already stored them in wrong format then you need to update like below


update  CustMast1 
Set RecoveryDate=cast(datename(yyyy,RecoveryDate) + right('00' +datename(dd,RecoveryDate),2)+right('00' + convert(varchar(2),datepart(mm,RecoveryDate)),2) as datetime)
WHERE datename(dd,RecoveryDate)<=12

Make sure you first do a select and ensure you're getting correct date values before you proceed with actual update
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-19 : 04:10:44

quote:
Originally posted by ravininave

quote:
Originally posted by khtan

you mean the data is stored in string ?

All dates are stored wrongly ?

KH
[spoiler]Time is always against us[/spoiler]

Data is stored in DateTime format. Yeh it stored wrongly

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

what is the format ?

KH
[spoiler]Time is always against us[/spoiler]

ravininave
Posting Yak Master

111 Posts

Posted - 2011-08-19 : 04:12:53

quote:
Originally posted by visakh16

if you have already stored them in wrong format then you need to update like below

update CustMast1
Set RecoveryDate=cast(datename(yyyy,RecoveryDate) + right('00' +datename(dd,RecoveryDate),2)+right('00' + convert(varchar(2),datepart(mm,RecoveryDate)),2) as datetime)
WHERE datename(dd,RecoveryDate)<=12

Make sure you first do a select and ensure you're getting correct date values before you proceed with actual update
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Yes it works fine. Thanx

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 04:24:22

quote:

Originally posted by ravininave

I tried this


Update CustMast1 Set RecoveryDate= datepart(yyyy,RecoveryDate) + '-' + datepart(DD,RecoveryDate) + '-' + DATEPART(MM,RecoveryDate)

In case it helps in future: the solution is to present text/string dates in "yyyymmdd" format. NO hyphens. Then SQL will interpret them unamibiguously.

Otherwise:

01-02-03

might be 1st Feb 2003, Feb 2nd, 2003, or 2001 Feb 3rd (or, indeed, other combinations

)

The way that SQL Server interprets 01-02-03 can change - depending on user and server settings. Apart from server-locale settings things like just using SET LANGUAGE can change the conversion method

If you have to present a date in xx-yy-zz format you can provide hints to tell SQL Server how to handle it - either using CONVERT (3rd parameter) or SET DATEFORMAT

Be unambiguous with your dates !

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-19 : 05:47:09

And the other universal format with time is
"YYYY-MM-DDTHH:MM:SS.{MS}{MS}{MS}"

'2011-11-01T23:59:59.999' is unambiguous and it is : 1st of November 2011 at 11PM and 59 minutes, 59 seconds, 9 hundred and 99 milliseconds.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 08:04:18

FWIW you can also use

"YYYYMMDD HH:MM:SS.{MS}{MS}{MS}"

for unambiguous date including time - note that there are no hyphens and no "T" but the space is required

I find the "T" version, which has no spaces, is handy to put in URLs as a space often gets mauled to either "+" or "%20"

(I know you know this TC

... just posting for "completeness")

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-08-19 : 08:08:43

quote:

(I know you know this TC ... just posting for "completeness")

I find your confidence confusing. I had no idea there was a 3rd universal format.

Cheers!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2011-08-19 : 09:11:34

Oh well! No charge then ...

The "T" format is ISO, probably added to SQL Server after the "YYYYMMYY" and "YYYYMMYY HH;MM;SS.ms" ones ... but I can't remember whether we had that in SQL 6.1 ... or not ... and I no longer care!

Pity they added the auto-detect and often-mis-interpreted formats at all ... intended to be helpful, no doubt, and the source of many newbie misunderstandings, and Forum questions, ever since ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources