Date in string 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 2000 Forums

SQL Server Development (2000)

Date in string format

Author

Topic

alpoor
Starting Member

29 Posts

Posted - 2005-06-15 : 15:16:13

I have date coming to one page as a string in the following format
"May 4 2005 12:00AM"

I need to query one of my tables using this date in combination of other nondate values. How can I convert this date into valid sql server datetime format before I query a database tables

Please help

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-15 : 15:21:42

It should accept just like that

SELECT * FROM Orders WHERE OrderDate = 'May 4 2005 12:00AM'

What do you mean "in combination of other nondate values"?

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

alpoor
Starting Member

29 Posts

Posted - 2005-06-15 : 15:48:20

Got your point but my actual problem is

I had to convert "2005-05-17 16:35:49.000" date to string using
CAST(a.ps_ActImplDate AS varchar(20)), 'Not Assigned'), since I had to assign "not assigned" to all null dates

Problem here is when I am displaying date in string format it is only displaying like "May 17 2005 4:35PM" missing seconds part. I need everything because I need to query using same date on other table. How to convert datetime to string without any truncation

Following a snapshot of how it each datetime looks before and after conversion

2005-05-17 16:35:49.000 May 17 2005 4:35PM
2005-05-17 16:27:03.000 May 17 2005 4:27PM
2005-05-17 16:27:03.000 May 17 2005 4:27PM
2005-05-17 16:35:48.000 May 17 2005 4:35PM

quote:
Originally posted by X002548

It should accept just like that

SELECT * FROM Orders WHERE OrderDate = 'May 4 2005 12:00AM'

What do you mean "in combination of other nondate values"?

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-15 : 16:22:54

quote:
since I had to assign "not assigned" to all null dates

^^^ says who??? Thats a display issue...

how about:

Select convert(varchar,getdate(),101) + ' ' + convert(varchar,getdate(),114)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

X002548
Not Just a Number

15586 Posts

Posted - 2005-06-15 : 16:28:31

I still don't get it

SELECT CONVERT(varchar(23),GetDate(),121)

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-15 : 16:32:29

yeah... that too

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources