Get the first portion of datetime

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)

Get the first portion of datetime

Author

Topic

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-03 : 10:07:13

Hello,

'7/7/2008 12:00:00 PM'

I want to get the first portion of this
'7/7/2008'
If you use substring, just be carefully, I don't want
'7/7/2008 1'

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 10:10:06

quote:
Originally posted by zhshqzyc

Hello,

'7/7/2008 12:00:00 PM'

I want to get the first portion of this
'7/7/2008'
If you use substring, just be carefully, I don't want
'7/7/2008 1'

Thanks

What is the datatype of the column?

Madhivanan

Failing to plan is Planning to fail

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2009-04-03 : 10:10:46

SELECT CONVERT(VARCHAR(8),'7/7/2008 12:00:00 PM',103)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 10:19:15

quote:
Originally posted by rcr69er

SELECT CONVERT(VARCHAR(8),'7/7/2008 12:00:00 PM',103)

Wont work in all cases

SELECT CONVERT(VARCHAR(8),'7/17/2008 12:00:00 PM',103)

Madhivanan

Failing to plan is Planning to fail

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-03 : 10:21:11

It's a datetime type,

SELECT CONVERT(VARCHAR(8),'12/12/2008 12:00:00 PM',103)

I will get '12/12/20' instead of '12/12/2008'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 10:23:58

quote:
Originally posted by zhshqzyc

It's a datetime type,

SELECT CONVERT(VARCHAR(8),'12/12/2008 12:00:00 PM',103)

I will get '12/12/20' instead of '12/12/2008'

Where do you want to show formatted dates?
If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-03 : 10:33:30

quote:
Originally posted by madhivanan

quote:
Originally posted by zhshqzyc

It's a datetime type,

SELECT CONVERT(VARCHAR(8),'12/12/2008 12:00:00 PM',103)

I will get '12/12/20' instead of '12/12/2008'

Where do you want to show formatted dates?
If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

But I need it here, any simple string split way?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 10:39:06

select convert(varchar(10),getdate(),101)

Madhivanan

Failing to plan is Planning to fail

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-03 : 10:45:56

It is not working
for '4/3/2008 12:00 AM'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-03 : 10:49:19

quote:
Originally posted by zhshqzyc

It is not working
for '4/3/2008 12:00 AM'

select convert(varchar(10),date_col,101) from your_table

Madhivanan

Failing to plan is Planning to fail

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-03 : 11:39:02

I am trying to find the first space of the string then to do something.

select convert(varchar(select charindex(' ','04/01/2009 12:00 AM')-1), '04/01/2009 12:00 AM', 101)

but what is wrong?

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-03 : 11:39:03

I am trying to find the first space of the string then to do something.

select convert(varchar(select charindex(' ','04/01/2009 12:00 AM')-1), '04/01/2009 12:00 AM', 101)

but what is wrong?

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-04-03 : 11:47:02

syntax is incorrect. I think ytou are trying for this,

select convert(varchar, left('04/01/2009 12:00 AM',charindex(' ','04/01/2009 12:00 AM')-1) , 101)

zhshqzyc
Posting Yak Master

240 Posts

Posted - 2009-04-03 : 12:07:48

[code]select @qry=
'update #TmpJnlData set TRAN_DATE =
(select select(convert(varchar, left(BUS_DT,charindex(' ',BUS_DT)-1) , 101))
FROM TRANS_HEADER A WHERE #TmpJnlData.TRANS_NUMBER = A.TRAN_ID and #TmpJnlData.TRANS_NUMBER = A.TRAN_ID
)'[/code]
Can you tell me what's wrong?

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-04-03 : 13:11:06

You are using dynamic sql?

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-03 : 13:24:34

cant understand use of dynamic sql in above case. also you cant use #tables inside dynamic sql.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources