Hello All,

I'm trying to write a sql statement and i'm having trouble dealing with a date field that is stored as text. An example of how it is stored is this, '20110916161946'. It is YYYYMMDDHHMMSS




How would I go about converting that field into a date, and pulling dates that are >=Date - 30 based on that field?

Thanks,
UtterAccess

You could create an expression column that returns a date typed result with this ...

CDate(Format([yourTextFieldThatLooksLikeADate],"0000-00-00 00:00:00"))

Then use the criteria of ...

>= DateAdd("d",-30,Date())


This will result in a WHERE clause that looks like this:

WHERE CDate(Format([yourTextFieldThatLooksLikeADate],"0000-00-00 00:00:00")) >= DateAdd("d",-30,Date())

You've posted this in the SQL Server forum, so I assume you are looking for a T-SQL solution.

I think that would be

Cast(left('20110916161946',4) + '/' +SUBSTRING('20110916161946',5,2)+ '/'+ SUBSTRING('20110916161946',7,2)+ ' ' + SUBSTRING('20110916161946',9,2)+':' + SUBSTRING('20110916161946',11,2) +':' + SUBSTRING('20110916161946',13,2) as DATEtime)

Oh ... sorry SQL Server ...

Something like this should do the trick (at least with SQL Server 2008 or higher):

... WHERE CAST(LEFT([yourTextDateField],8) as SMALLDATETIME) >= DATEADD(dd,CAST(GETDATE() AS INT) - 30,0)

2005 too. (Can't say about earlier.)

I'm not sure if i'm doing something wrong but i'm trying to apply your solutions with no luck. Here is my current sql statement, can someone help me apply the solutions>? PSPEC.PSRCVDT being the date field i'm using.



SELECT PDEM.PID2, PSPEC.PSID2, PSPEC.PSPECNO, PSPEC.PSRCVDT
FROM PDEM INNER JOIN PSPEC ON (PDEM.PUID = PSPEC.PUID) AND (PDEM.PEFDT = PSPEC.PEFDT)
GROUP BY PDEM.PID2, PSPEC.PSID2, PSPEC.PSPECNO

You could either put a WHERE clause prior to your GROUP BY clause or you can put a HAVING clause after your GROUP BY clause. In this case, I like the use of a WHERE clause.

SELECT <your field list>
FROM <your joined tables>
WHERE <an expression in the form I showed above>
GROUP BY <your group by list>

{note that the SELECT field list should ideally match the GROUP BY list -- at least for non-aggregated columns}

I do this but i still get dates that are older than 30 days ago???

SELECT PDEM.PID2, PSPEC.PSID2, PSPEC.PSPECNO, PSPEC.PSRCVDT, CAST(LEFT(PSPEC.PSRCVDT,8) as SMALLDATETIME)
FROM PDEM INNER JOIN PSPEC ON (PDEM.PUID = PSPEC.PUID) AND (PDEM.PEFDT = PSPEC.PEFDT)
WHERE CAST(LEFT(PSPEC.PSRCVDT,8) as SMALLDATETIME) >= DATEADD(dd,CAST(GETDATE() AS INT) - 30,0)
GROUP BY PDEM.PID2, PSPEC.PSID2, PSPEC.PSPECNO

Can you give us a sample dump of the data ... in Excel/Access/CSV or something, so we can try to duplicate your experience?

i got it to work with this:

SELECT PDEM.PID2, SUBSTRING(PDEM.PID2, 3) as MRNumber, PSPEC.PSID2, PSPEC.PSPECNO, PSPEC.PSRCVDT, CAST(LEFT(PSPEC.PSRCVDT,8) as SMALLDATETIME)
FROM PDEM INNER JOIN PSPEC ON (PDEM.PUID = PSPEC.PUID) AND (PDEM.PEFDT = PSPEC.PEFDT)
WHERE CAST(LEFT(PSPEC.PSRCVDT,8) as SMALLDATETIME) >= DATEADD(d, -30, getdate())
GROUP BY PDEM.PID2, PSPEC.PSID2, PSPEC.PSPECNO

Awesome! Glad to here of your success!

Whoa!! ...

<<< CAUTION >>>
This ...
WHERE ... >= DATEADD(d, -30, getdate())

will yeild a time component, where as this ...
CAST(LEFT(PSPEC.PSRCVDT,8) as SMALLDATETIME)

Will not.

-----

So, I would suggest that you work with the expression I provided ... but I did notice, the casting to INT did not TRUNCATE as I would have expected, so use FLOAT instead ..

DATEADD(d, CAST(getdate() AS FLOAT) - 30,0)

or ...

DATEADD(d, CAST(getdate() AS FLOAT), -30)