Getting Wrong Date Format - 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 2005 Forums

Transact-SQL (2005)

Getting Wrong Date Format

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2008-04-08 : 11:48:50

Hi Guys, I am trying to convert a date to yyyymmdd format

This is the input date: 1/10/2008 2:59:58 PM
This is what I am getting: Jan 10 2

Field definition: MaxDate (nvarchar (8) , not null)

Code:
INSERT INTO tblCurStatus (ResultsPKey, Status, MaxDate)
SELECT ResultsPKey, Status,
MaxDate = (REPLACE(REPLACE(CONVERT(varchar(8), tblStatusHistory.Date, 112), '-',''), ' ', ''))
FROM tblStDate INNER JOIN tblStHistory
ON (tblMaxStDate.CurStatDate = tblStHistory.Date)
AND (tblMaxStDate.ResultsPKey = tblStHistory.ResultsPKey)

I cannot understand why I’m not getting the format I want. What do I need to do to get yyyymmdd?

Thanx much,
Trudye

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-08 : 11:51:39

I think its a display issue and you should try to do it at your front end.and if you are particular to do it in sql use CONVERT() function.Have a look at BOL for available style values.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-04-08 : 11:59:16

Is [Date] column in the tblStatusHistory table a datetime column?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 12:01:17

[code]INSERT tblCurStatus
(
ResultsPKey,
Status,
MaxDate
)
SELECT ResultsPKey,
Status,
CONVERT(CHAR(8), CAST(tblStatusHistory.Date AS DATETIME), 112)
FROM tblStDate
INNER JOIN tblStHistory ON tblStHistory.ResultsPKey = tblMaxStDate.ResultsPKey
WHERE tblMaxStDate.CurStatDate = tblStHistory.Date[/code]You might to use CONVERT instead of CAST if tblStatusHistory.Date column has some specific format.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 03:13:29

<<
What do I need to do to get yyyymmdd?
>>

It depends on where you want to show the formatted dates

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources