October 9, 2007 at 12:12 pm
We are storing dates as dayPublished, monthPublished, yearPublished because sometimes not all the values are available.
What is the best way to put them back together again (ignoring the fact that sometimes the dayPublished or monthPublished could be null)
I thought something like
select dbo.Resources.monthPublished + '/' + dbo.Resources.dayPublished + '/' + dbo.Resources.yearPublished as fullDate
but it doesn't like my slashes, and if I leave them out it simply adds the three fields together. Boo!
I'd also like to use a between statement against this also to get specific date ranges from the selection set, so does that mean I'll have to convert to datetime anyway rather than stringing these varchars together?
Thanks for any help! I tried to search on this but wasn't able to find any similar q, but maybe my searching capabilities aren't so great yet!
Thanks,
Megan
October 10, 2007 at 9:46 am
Megan, two things to note here. First, I am pretty sure you will have to convert to date format to use between in the where clause, unless you wanted to assign some numerical values to each char month field, and pull it into the query. It will be much easier to convert to date format. Second, you will have to assign a default value to fill the null fields. Something like this should work for you.
IF OBJECT_ID('TempDB..#Parts','u') IS NOT NULL
DROP TABLE #Parts
CREATE TABLE #Parts
(
Dd VARCHAR(2),
Mm VARCHAR(2),
Yy CHAR(4)
)
INSERT INTO #Parts
SELECT '1',NULL,'2007' UNION ALL
SELECT '2','10','2007' UNION ALL
SELECT NULL,'12','2007'
DECLARE @Day VARCHAR(2),@Month VARCHAR(2)
SET @Day = '15'
SET @Month = '6'
SELECT
CONVERT(SMALLDATETIME,
CASE WHEN mm IS NULL THEN @Month ELSE mm END + '/' +
CASE WHEN dd IS NULL THEN @Day ELSE dd END + '/' +
YY) AS DateFormat
FROM #Parts
DROP TABLE #Parts
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 10, 2007 at 10:01 am
Assuming you handle the nulls correctly (meaning the fields actually HAVE a value and it's a valid value), you could also try this one on:
...
select dateadd(dd,daypublished-1,dateadd(mm,monthpublished-1,dateadd(yy,yearpublished-1900,0)) as fulldatetime
...
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2007 at 11:07 am
Matt, that is quite a simple and nifty trick, thank you. There needs to be one more ) at the end though.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 10, 2007 at 11:36 am
*&^%$#@. thanks! can't seem to count today:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 10, 2007 at 11:54 am
Thanks for all the help, I got it working pretty much by ignoring the date aspect of things... let me know if you think this will cause any problems. It works well even with user supplied "dates" (as strings) from a web form.
I didn't have to end up worrying about null for any of the dateparts because luckily for me all the records are news articles, which always have a complete date for when they're published.
This ended up being my query:
SELECT DISTINCT yadda, yadda, yadda, CAST(dbo.Resources.monthPublished AS varchar) + '/' + CAST(dbo.Resources.dayPublished AS varchar) + '/' + CAST(dbo.Resources.yearPublished AS varchar) AS fullDate
FROM yaddaYadda
WHERE (CAST(dbo.Resources.monthPublished AS varchar) + '/' + CAST(dbo.Resources.dayPublished AS varchar) + '/' + CAST(dbo.Resources.yearPublished AS varchar) >= 'startDate') AND (CAST(dbo.Resources.monthPublished AS varchar) + '/' + CAST(dbo.Resources.dayPublished AS varchar) + '/' + CAST(dbo.Resources.yearPublished AS varchar) <= 'endDate') ORDER BY dbo.Resources.systemDateAdded DESC"
October 10, 2007 at 1:03 pm
Just a thought...If that works for you, and assuming there is some kind of identity field on dbo.Resources, then using a derived table for building the date might make it a little easier to look at.
SELECT DISTINCT
r.yadda,r.yadda,r.yadda,
t1.fulldate
FROM dbo.Resources r,
(
SELECT
ID,
CAST(Month_Published AS varchar) + '/' +
CAST(Day_Published AS varchar) + '/' +
CAST(Year_Published AS varchar) AS fullDate
FROM dbo.Resources
) t1
WHERE r.id = t1.id
AND t1.fulldate >= 'Start date'
AND t1.fulldate <= 'End date'
ORDER BY r.systemDateAdded DESC
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply