asked on

Varchar Datetime to Varchar Date in SQL

Team,

I have a field (Varchar(255)) in a table which always have inputs like

12/29/2010 7:31:37 AM
12/30/2010 7:27:05 AM
1/3/2011 7:29:04 AM

Till Date, i was using the below formula to do a look up

select Convert(varchar, getdate(),101) as 'BacklogDate', Convert(varchar, getdate(),7) as 'Today'

however since the date i have in the table has 1/3/2011 7:29:04 AM, and the query i wrote populates, 01/03/2011, its not matching due to preceding zeros.

I need to have a query which does both with / without preceding zeros.

Help me.

Thanks,
David

Muhammad Ahmad Imran

use case statement to append 0

s_chilkury

Try the following :

SELECT Convert(varchar
RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(MM, GetDate())), 2) + '/' + RIGHT('0' + CONVERT(CHAR(2), DATEPART(dd, dateadd(dd, -1, GetDate()))), 2) + '/' + CONVERT(CHAR(4), DATEPART(YYYY, GetDate()))
, GETDATE(), 101) AS 'Today'

arilani

SQL Server convert function does not have a format for the dates without the preceding zero. You can concatenate the date parts like this:

select Convert(VarChar, Month(GetDate())) + '/' + Convert(VarChar, Day(GetDate())) + '/' + Convert(VarChar, Year(GetDate()))

Open in new window

sammySeltzer

You can also try

select right('0000000000' + convert(varchar,getdate(),101),10)

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

indianguru2

Try this

SELECT CONVERT(VARCHAR(20),CAST('1/3/2011 7:29:04 AM' AS DATETIME),101)

waltersnowslinarnold

Use the following as a sample to solve you requirement,

SELECT 1 WHERE CONVERT(VARCHAR,CONVERT(DATETIME,'1/3/2011 7:29:04 AM'),101) = Convert(varchar, getdate(),101)

Open in new window

arilani

I have a simple question.

Why you store a datetime in a varchar(255) field?

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

davidi1

ASKER

@ACPerkins: Works Great. Thanks. Graded A with 500 points

Erick37

If you compare the values as dates, then it does not matter if there are preceeding zeros or not.
Depending on your requirements something like this may work:

Select
something
From yourtable
Where CAST(BacklogDate AS DATETIME) < getdate()