Select Datetime format changes

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)

Select Datetime format changes

Author

Topic

Fredforfree
Starting Member

49 Posts

Posted - 2008-04-09 : 11:41:10

Hello

I'm hoping someone can help explain why when i select timestamp field
2008-03-25 18:57:39.000 the view result changes to
04-04-2008 11:12:01 AM.

I need result of view to be in same format as table.

Thanks

Fred

Fred

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 12:29:48

which field? Can you post some sample data to make your post clear?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 12:35:00

Same question here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100597

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

Fredforfree
Starting Member

49 Posts

Posted - 2008-04-09 : 12:55:08

Same question, still without answere Peso.
If you can't help please just say so.

Fred

quote:
Originally posted by Peso

Same question here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100597

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

Fred

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 12:59:59

What does view definition look like? Have you applied any formatting on original field using CONVERT()?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-09 : 13:07:47

There is no format within T-SQL. It is just a date time value. The formatting is applied at clients. If you are using correct data types, formatting is never an issue within your database code. Your clients that are querying your database, such as a reporting tool, or web application, and so on, should handle all formatting.

Remember that CONVERT does not "apply formatting". It converts. So, what was once a sortable, comparable, calculatable datetime value now becomes a meaningless string. that is why we don't try to make things "look pretty" within the database, we focus on storing clean, raw, accurate date of the proper type, which any client tools can easily format any what that it wants.

more here:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspx

This is probably one the most important concepts for a programmer to understand when working with a database.

- Jeff
http://weblogs.sqlteam.com/JeffS

Fredforfree
Starting Member

49 Posts

Posted - 2008-04-09 : 13:22:21

data type is datetime in table.
2008-03-25 18:57:56.000

SELECT datimestamp AS timestamp, eventcode, subcode, worklist, username AS [user], telnum, retkey, timespan, oldstate, newstate
FROM eis_evntdb.dbo.eis_eventlog
WHERE (DAY(datimestamp) = 4) AND (MONTH(datimestamp) = 4)

result of dat04-04-2008 11:10:41 AM

can't explain why it is changed

quote:
Originally posted by jsmith8858

There is no format within T-SQL. It is just a date time value. The formatting is applied at clients. If you are using correct data types, formatting is never an issue within your database code. Your clients that are querying your database, such as a reporting tool, or web application, and so on, should handle all formatting.

Remember that CONVERT does not "apply formatting". It converts. So, what was once a sortable, comparable, calculatable datetime value now becomes a meaningless string. that is why we don't try to make things "look pretty" within the database, we focus on storing clean, raw, accurate date of the proper type, which any client tools can easily format any what that it wants.

more here:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspx

This is probably one the most important concepts for a programmer to understand when working with a database.

- Jeff
http://weblogs.sqlteam.com/JeffS

Fred

Fredforfree
Starting Member

49 Posts

Posted - 2008-04-09 : 14:17:51

I'm not understanding or not making myself clear.
I did not format anything but only selected timestamp field.

Select should not change format, how can i return datetime in same format as table?

Fred

quote:
Originally posted by Fredforfree

data type is datetime in table.
2008-03-25 18:57:56.000

SELECT datimestamp AS timestamp, eventcode, subcode, worklist, username AS [user], telnum, retkey, timespan, oldstate, newstate
FROM eis_evntdb.dbo.eis_eventlog
WHERE (DAY(datimestamp) = 4) AND (MONTH(datimestamp) = 4)

result of dat04-04-2008 11:10:41 AM

can't explain why it is changed

quote:
Originally posted by jsmith8858

There is no format within T-SQL. It is just a date time value. The formatting is applied at clients. If you are using correct data types, formatting is never an issue within your database code. Your clients that are querying your database, such as a reporting tool, or web application, and so on, should handle all formatting.

Remember that CONVERT does not "apply formatting". It converts. So, what was once a sortable, comparable, calculatable datetime value now becomes a meaningless string. that is why we don't try to make things "look pretty" within the database, we focus on storing clean, raw, accurate date of the proper type, which any client tools can easily format any what that it wants.

more here:

http://weblogs.sqlteam.com/jeffs/archive/2007/04/13/60175.aspx

This is probably one the most important concepts for a programmer to understand when working with a database.

- Jeff
http://weblogs.sqlteam.com/JeffS

Fred

Fred

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-09 : 14:32:06

quote:

Select should not change format, how can i return datetime in same format as table?

Again, I cannot make this clear enough: IF YOU ARE USING DATETIME TO STORE YOUR DATA, THERE IS NO "FORMAT" USED TO STORE YOUR DATA.

I apologize for "yelling", but, again, this is a crucial, fundamental concept to understand.

So -- where you outputting this data? THAT is where the formatting is applied, by the client that is selecting data from the database. And that client is where you need to alter this, if it allows you to, if you don't like the way things "look".

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 14:44:20

You server is displaying the data according to the servers regional settings.
A datetime value is nothing more than a "serial" number starting Jannuary 1, 1900.

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-04-09 : 14:47:52

He's not asking that question, he is saying that in his table he has value
2008-03-25 18:57:56.000
when he does a select on it (no convert, format or anything, just select)
he gets
2008-03-25 18:57:56PM

Sorry, Fred, I don't know the answer, but I think I understand the question now.

Jim

Fredforfree
Starting Member

49 Posts

Posted - 2008-04-09 : 14:53:41

Thanks

quote:
Originally posted by jimf

He's not asking that question, he is saying that in his table he has value
2008-03-25 18:57:56.000
when he does a select on it (no convert, format or anything, just select)
he gets
2008-03-25 18:57:56PM

Sorry, Fred, I don't know the answer, but I think I understand the question now.

Jim

Fred

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-09 : 14:58:02

Again, the tool he is using (SSMS) is dsplaying the data according to the regional settings at the computer where he is running the query (depending in the settings in SSMS).

There is no such "value" as 2008-03-25 18:57:56.000 in the database either!
The date is stored as a decimal (example 43186.43252222222). THIS IS THE VALUE ANY OTHER RDBMS sees and accepts.
Then that RDMS displays the data in a "human readable form".

And that's why you often get misplaced data when transferring from/to access because date "zero" in sql server is January 1, 1900 and in MS Access the zero date is "Dec 30, 1899".

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-04-09 : 15:02:46

Peso,

I was replying to jsmith, I should have quoted. So there's your answer Fred, Peso, as usual knows the scoop.

Jim

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-09 : 15:23:42

jimf -- What Peso wrote is almost exactly the same as what I wrote. Our points are exactly the same. The client is formatting the data, the value itself is stored without a format.

- Jeff
http://weblogs.sqlteam.com/JeffS

Fredforfree
Starting Member

49 Posts

Posted - 2008-04-09 : 19:27:45

Thank you i changes my computer setting and the sql view now desplays in correct format. Unfortunaly i can't leave date time regional setting set like that.

Can you help me write convert or cast to change to format i need?

Fred

quote:
Originally posted by jsmith8858

jimf -- What Peso wrote is almost exactly the same as what I wrote. Our points are exactly the same. The client is formatting the data, the value itself is stored without a format.

- Jeff
http://weblogs.sqlteam.com/JeffS

Fred

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-10 : 01:10:58

If you convert the date to a string, you will

1) Drop performance of query
2) Harder to compare and sort records

If you still persist in doing this, I suggest your read Books Online (the SQL Server help file) about CAST & CONVERT.
There is a complete section about how to convert datetime values to varchars.

Or see this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563

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

Fredforfree
Starting Member

49 Posts

Posted - 2008-04-10 : 12:50:09

Thanks Peso select convert(Varchar,datimestamp,121)was all i needed.
link helped me out.

Fred

quote:
Originally posted by Peso

If you convert the date to a string, you will

1) Drop performance of query
2) Harder to compare and sort records

If you still persist in doing this, I suggest your read Books Online (the SQL Server help file) about CAST & CONVERT.
There is a complete section about how to convert datetime values to varchars.

Or see this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80563

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

Fred

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources