Why display date different from source? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Why display date different from source?

Author

Topic

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-10 : 11:06:57

i've these following table,
tblSEL
Date_Taken |Time |Main_ID
------------------------------------------
4/3/2005 12:00:00 AM | 1148 | 233
4/3/2005 12:00:00 AM | 1248 | 231
4/3/2005 12:00:00 AM | 1348 | 235
4/3/2005 12:00:00 AM | 1448 | 232
4/3/2005 12:00:00 AM | 1548 | 223
4/3/2005 12:00:00 AM | 1648 | 245

tblMainID_Desc
Main_ID | Location
-------------------
233 | PRK
235 | WP
245 | PER
...
...

Let's say current date is 3 APRIL 2005
i run following query
SELECT
t1.Date_Taken, t1.Time,
t1.Main_ID
FROM dbo.SEL t1 INNER JOIN dbo.station_info t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.Location='PRK'
AND t1.Date_Taken=CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY t1.Date_Taken, t1.Time

then, result shown as follow
tblResult
Date_Taken |Time |Main_ID
---------------------------------------
2005-04-03 00:00:00 |0 |234
2005-04-03 00:00:00 |2 |236
...
...
...

Why Date_Taken different from tblSEL above? How to setting it to make sure nothing changes on Date_Taken when queries runned.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-05-10 : 11:24:52

Is the data type for Date_Taken is Datetime? How and where do you see the first output?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-10 : 11:56:25

correction, my query is

SELECT
t1.Date_Taken, t1.Time,
t1.Main_ID
FROM dbo.tblSEL t1 INNER JOIN dbo.tblMainID_Desc t2
ON t1.Main_ID=t2.Main_ID
WHERE t2.Location='PRK'
AND t1.Date_Taken=CONVERT(VARCHAR(10), GETDATE(), 101)
ORDER BY t1.Date_Taken, t1.Time

Date_Taken is smalldatetime. I'm just open tblSEL using right click.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-10 : 12:21:18

The date values are not changing. You seem be troubled by the presentation/formatting of the smalldatetime value. You should control the formatting of the datetime value in your presentation layer.

When you INSERT a value into a smalldatetime column, sql server will implicitly CONVERTs many different strings into a smalldatetime value. These STRINGs will all be stored as the same smalldatetime value
'2005-04-03 00:00:00'
'4/3/2005 12:00:00 AM'
'4/3/2005'
'2005-04-03'

Internally sql server stores these values as a pair of SMALLINTs (no formatting is associated with the stored value)

When you SELECT the values sql server uses the formatting defined by your server settings. You should be controlling the presentation of the values in whatever application presents the data. You can CONVERT the value to varchar and use any of the pre-defined formats available for converting datetime to varchar (see CONVERT). But if you do that you loose ability to manipulate, order, and calculate the values as dates so again, you should use the presentation layer to worry about the format.

Be One with the Optimizer
TG

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-10 : 19:59:38

DO you have any sample in SQL to display '4/3/2005 12:00:00 AM' in Date Taken column?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-11 : 13:00:54

There are plenty in Books Online. As mentioned in my previous post, "...(see CONVERT)..."
also check out the topics under the category "Dates" in this:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210

Be One with the Optimizer
TG

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-11 : 13:07:41

Read about the CONVERT function in books online:-

http://doc.ddart.net/mssql/sql70/ca-co_1.htm

wkm1925
Posting Yak Master

207 Posts

Posted - 2008-05-12 : 03:36:56

tq to mr. harsh_athalye, mr. TG and mr. visakh16.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources