DATETIME STORED - 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

General SQL Server Forums

New to SQL Server Programming

DATETIME STORED

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-29 : 22:21:49

I read an article about how datetime is stored and I'm a bit confused. The way I understand it, it's the number of days after 19000101.

It seems I should be able to convert that into a readable format when running a query in the following manner:

CAST(convert(char(8),CAST(20090824 AS int),110) AS DATETIME)
or
CAST(convert(char(8),20090824110) AS DATETIME)

My expected results would be: 08-24-2009

However, both are returning: 2053777408 which I can only assume is the number of days after 19000101?

Does anyone have any idea why it's not returning results in the mm-dd-yyyy format?

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-29 : 22:26:11

refer to
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-30 : 00:16:09

why are you casting date to int? the integer value 20090824 is not equivalent to date 20009-08-24

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-30 : 00:28:50

Hi Vis,

Just a code I've seen in some forums for converting integer to datetime. The datatype of the number column above does start off as INT.
Either way, both the first expression as well as the second expression ( CAST(convert(char(8),20090824110) AS DATETIME) )

give me the same value of 2053777408.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-30 : 00:30:44

what does this give you?
CAST('20090824' AS DATETIME)

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-30 : 01:40:52

Returns: 2053777408

When I try to plug in the actual column: CAST(FROMDATE as DATETIME) FROM TABLE WHERE FROMDATE>0 it returns: SQL State=22003 Native Error=8115 SQL Error=0 Arithmetic overflow error converting expression to datatype datetime

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 02:09:16

SELECT CAST(CAST(FROMDATE AS CHAR(8)) as DATETIME) FROM TABLE WHERE FROMDATE>0

Madhivanan

Failing to plan is Planning to fail

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-30 : 11:02:02

M,

Thanks...returns: 2053777408 as well when I run that

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 11:04:16

quote:
Originally posted by flamblaster

M,

Thanks...returns: 2053777408 as well when I run that

Which version of SQL Server are you using?

Madhivanan

Failing to plan is Planning to fail

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-09-30 : 11:05:55

SQL SERVER 2005; however, the query writer is a tool that comes with the software. So the software is stored on SQL SERVER 2005, but there is a read-only (select statements only) GUI for query writing if that makes sense.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-10-01 : 01:46:48

Interestingly enough, this returns a value of Aug 24, 2009 12:00AM

SELECT CAST(CAST(CONVERT(CHAR(8), FROMDATE) AS DATETIME) AS VARCHAR)

Unfortunately, no matter what style I write in, it always returns the same format.

I've tried: SELECT CAST(CAST(CONVERT(CHAR(8), FROMDATE,110) AS DATETIME) AS VARCHAR) but it doesn't
change the style.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 01:55:11

what's GUI you're using? have you tried same using sql mgmt studio?

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-10-01 : 02:27:23

Hi Vis,

Yes, I can do it fine in SQL management studio. The interface I'm using is a Transit Software Application.
Basically, it gives read only access for query writing. While I can definitely write some of the queries
I need in SQL Server mgmnt studio, there's no way for me to distribute them to the end users. (Fairly long
and boring story :) ).

So I just need to keep finding work-arounds where I can.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 02:31:23

So, you need to find out a way how to do formation in Transit Software Application.

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:32:10

oh..ok..so its a ui specific thingy. is there any date format option tabs in ui?

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-10-01 : 12:50:26

Mad and Vis,

Yes, I understand it's probably the interface...I've been dealing with this stuff for a while. I always write
queries on SQL Server first then try to re-write them on the Transit Software so I can distribute to the user's workstations.

Unfortunately, there are no settings I can change for date format. There are some "styles" built into columns if
they're in integer format, but those are not always practical.

Thanks again for your help...let me know if you have any other ideas :)

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-01 : 13:46:25

quote:
Originally posted by flamblaster

The way I understand it, it's the number of days after 19000101.

DECLARE @d datetime; SET @d = 0; SELECT @d

And it is store as 2 int...one for date, one for time

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-10-01 : 13:58:01

Brett, thanks...Can not declare cursor in this interface. Returns an error: Invalid Cursor State

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-01 : 14:39:45

so....you are not using stored procedures?

And are using dynamic sql calls from the app?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2009-10-01 : 15:05:38

Can you create a view and use the view in your Transit Software? If so, create a view with the date formatted the way you want and have the users query the view.

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2009-10-01 : 18:35:55

Jeff,

I can't create views, but I can write common table expressions. I've tried, but I haven't been able to format it to read anything different than the results I've already returned.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources