Custom Date Key??? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Custom Date Key???

Author

Topic

DMS11X
Starting Member

3 Posts

Posted - 2011-03-11 : 09:20:26

I am a complete SQL NooB, I am looking to create a custom GETDATE() and use it as a key...

Format: AL= ACTIVITY LOG

AL-MMDDYYYY-1

AL-MMDDYYYY-2

AL-MMDDYYYY-3

and so on...

I need the number 1,2,3... to recycle every 24hrs,

so every new date/day (midnight server time) the number attached to the date would cycle back to 1, then 2, then 3 and so on...

Does that make sense?

Another Example:

AL-01012011-1 = January 1st 2011 entry 1

AL-01012011-2 = January 1st 2011 entry 2

AL-01012011-3 = January 1st 2011 entry 3
-----------------------------------------------
AL-01022011-1 = January 2nd 2011 entry 1

AL-01022011-2 = January 2nd 2011 entry 2

How do I get SQL server to format the date as: AL-MMDDYYYY-#

Thank you!

*I was nearly able to do this with access 2010, I have no clue how to config this for SQL

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-11 : 09:37:57

Just just datetime column to store actual dates and let your front end application do the formation. You can also derive it from the query

select 'AL-'+replace(convert(101,getdate(),101),'/','')

Madhivanan

Failing to plan is Planning to fail

DMS11X
Starting Member

3 Posts

Posted - 2011-03-11 : 09:48:03

I am actually using Access 2010 as the front end...

This helps with the formatting of the date, but how does this produce the sequence of 1,2,3 and reset every day at date change?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-11 : 10:18:32

How were you able to generate these in Access? Was it a Visual Basic function? Can you post the code?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2011-03-11 : 10:25:29

[code]
-- *** Test Data ***
CREATE TABLE #t
(
tDate datetime NOT NULL
)
INSERT INTO #t
SELECT '20110101 08:00'
UNION ALL SELECT '20110101 12:00'
UNION ALL SELECT '20110101 16:00'
UNION ALL SELECT '20110102 08:00'
UNION ALL SELECT '20110102 12:00'
-- *** End Test Data ***

SELECT DATEADD(d, DATEDIFF(d, 0, tDate), 0) As tDate
,ROW_NUMBER() OVER (PARTITION BY DATEADD(d, DATEDIFF(d, 0, tDate), 0) ORDER BY tDate) AS Row
FROM #t

-- Formatting is best done on front end
SELECT 'AL-' + REPLACE(CONVERT(varchar(50), DATEADD(d, DATEDIFF(d, 0, tDate), 0),101), '/', '')
+ '-' + CAST(ROW_NUMBER() OVER (PARTITION BY DATEADD(d, DATEDIFF(d, 0, tDate), 0) ORDER BY tDate) AS varchar(50))
FROM #t
[/code]

DMS11X
Starting Member

3 Posts

Posted - 2011-03-11 : 10:34:55

quote:
Originally posted by robvolk

How were you able to generate these in Access? Was it a Visual Basic function? Can you post the code?

I got: "AL"ddmmyyyy-hhnnss

-This was my initial key, however looking into the future I saw a possible duplication when we "fall back" the clock 1hr in fall time...

So i set out to create an activity log based on date and numaric, then i realized my DB will probably grow bigger than ACCESS, so i decided to switch to SQL Server 2008 -> linked tables ODBC -> ACCESS 2010 Front.

So now here I am, trying to figure out one last detail, which is critical THE KEY!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources