• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

SQL Query - parse string

I need help parsing a date so that it is in the format I need it to be for sql query.

Example:
2011-04-26 05:53:05.000   -->  20110426055305
2011-04-26 13:12:09.000   -->  20110426131209


0
holemania
Asked:
holemania
  • 2
2 Solutions
 
sdstuberCommented:
if you'll always have .000 then simple replace will work

replace(replace(replace(yourstring,'-',''),':',''),'.000','')
0
 
SharathData EngineerCommented:
try this.
select REPLACE(REPLACE(REPLACE(convert(varchar,date_col,120),'-',''),':',''),' ','')

Open in new window

declare @date datetime
select @date = '2011-04-26 05:53:05.000'
select REPLACE(REPLACE(REPLACE(convert(varchar,@date,120),'-',''),':',''),' ','')
-- 20110426055305
select @date = '2011-04-26 13:12:09.000'
select REPLACE(REPLACE(REPLACE(convert(varchar,@date,120),'-',''),':',''),' ','')
-- 20110426131209
select @date = GETDATE()
select REPLACE(REPLACE(REPLACE(convert(varchar,@date,120),'-',''),':',''),' ','')
-- 20110427113730

Open in new window

0
 
holemaniaAuthor Commented:
When I use the replace my date isn't number any more, but it's looking like the following:

Apr 26 2011 553AM

I need for it to look like 201104260553.
0
 
holemaniaAuthor Commented:
Okay got it working now.  Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now