SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Select Datetime format changes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Fredforfree
Starting Member

Canada
46 Posts

Posted - 04/09/2008 :  11:41:10  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 04/09/2008 :  12:29:48  Show Profile  Reply with Quote
which field? Can you post some sample data to make your post clear?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/09/2008 :  12:35:00  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Same question here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100597



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Fredforfree
Starting Member

Canada
46 Posts

Posted - 04/09/2008 :  12:55:08  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 04/09/2008 :  12:59:59  Show Profile  Reply with Quote
What does view definition look like? Have you applied any formatting on original field using CONVERT()?
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/09/2008 :  13:07:47  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 04/09/2008 13:17:55
Go to Top of Page

Fredforfree
Starting Member

Canada
46 Posts

Posted - 04/09/2008 :  13:22:21  Show Profile  Reply with Quote
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
Go to Top of Page

Fredforfree
Starting Member

Canada
46 Posts

Posted - 04/09/2008 :  14:17:51  Show Profile  Reply with Quote
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
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/09/2008 :  14:32:06  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 04/09/2008 14:33:15
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/09/2008 :  14:44:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/09/2008 :  14:47:52  Show Profile  Reply with Quote
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
Go to Top of Page

Fredforfree
Starting Member

Canada
46 Posts

Posted - 04/09/2008 :  14:53:41  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/09/2008 :  14:58:02  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 04/09/2008 15:00:19
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 04/09/2008 :  15:02:46  Show Profile  Reply with Quote
Peso,

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

Jim
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 04/09/2008 :  15:23:42  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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
Go to Top of Page

Fredforfree
Starting Member

Canada
46 Posts

Posted - 04/09/2008 :  19:27:45  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30111 Posts

Posted - 04/10/2008 :  01:10:58  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

Fredforfree
Starting Member

Canada
46 Posts

Posted - 04/10/2008 :  12:50:09  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000