March 11, 2011 at 8:12 am
I am a complete SQL NooB:crazy:, 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!
March 11, 2011 at 9:30 am
Hi, You probably don't want to go there as far as creating a custom key. Anyway you haven't convinced me that this is really necessary so I would try to use the standard functionality so you don't get off on the wrong foot. 😉
DROP TABLE Test
CREATE TABLE [dbo].[Test](
[MyDate] [datetime] NULL
) ON [PRIMARY]
INSERT INTO Test
SELECT '1/1/2011 3:00:00 PM' UNION
SELECT '1/1/2011 4:00:00 PM' UNION
SELECT '1/1/2011 5:00:00 PM' UNION
SELECT '1/2/2011 5:00:00 PM' UNION
SELECT '1/2/2011 5:10:00 PM'
SELECT * FROM Test
You should be able to use a CTE to generate a sequential number
SELECT
'AL ' + REPLACE(CONVERT(VARCHAR(10), MyDate, 101), '/', '') + '-' +
--here you would add the code that would resequence for each new date
--since I am not too good with this I am hoping someone smarter than me will fill in the blanks here
FROM Test
This assumes you don't need to store this pseudo-key, just present it.
March 11, 2011 at 11:21 am
DMS11X (3/11/2011)
I am a complete SQL NooB:crazy:, 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-#
mmhhh... lets see, you need an increasing number that recycles itself when the day changes; isn't that the time of the day?
If a custom key is needed - not sure this is the case but I can't tell because I do not have details about business specifications - I would go with something like:
YYYYMMDDHHMISS
where
YYYY Year
MM Month
DD Day
HH Hours (24 hours time)
MI Minutes
SS Seconds
Do you need less or more granularity than that?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 11, 2011 at 11:47 am
Yes, the granularity question is key. Even if you are stamping your datetime field with getdate() then its possible though unlikely you could get duplicates.
It sound like you have two questions:
1) What is the proper key for the table? If you show us your table structure and describe its use we could help identify what the key should be or you could use the identity feature to create an artificial key.
2) When presenting the data from my table how can I generate a daily sequential number corresponding to the time the record was inserted.
March 11, 2011 at 7:26 pm
Here is the rundown:
I run a security team, and well I am designing a dispatch log of sorts and a report system, Using Access 2010 as the front and SQL server 2008 as the brains for obvious reasons.
I cant have the keys defined by MMDDYYYY-HHMMSS, Simply because we function 24hrs, and since we are on PST, every year in the fall the clock cycles back 1hr, essentially this would create the potential for duplicate keys as the clock has reset one hour every year... Trust me I was going to do this....
Additionally, for the officers reports, I wanted to be able to provide them with a report number upon request. For example:
IR-031111-1 would be = Incident Report 03/11/11 #1
IR-031111-5 would be = Incident Report 03/11/11 #5
Just for the day, then the next day the IR # would recycle back to 1
---------------------------------------------------------------------
No for the activity log, since my security team operates 24hrs a day, 365 days a year, all activity must be logged.
which is why I wanted the key to be:
AL-031111-1
AL-031111-2
AL-031111-3
Then recycle the ending # upon the next day.
If i needed someone to fix a specific log entry #, all I would have to do is tell them to fix log# 23 on todays date.
I hope this makes sense
March 11, 2011 at 7:58 pm
Hi
Correct me if I am wrong but you want a key which is composed by three datatypes:
char(2) + date + smallint
If I was in your place I would just create those three columns in my table and define them as my primary key. If you want to have the hole code together you can just concatenate those three values.
create table dbo.mytable
(type_log char(2) not null,
date_log date not null,
num_log smallint not null,
description varchar(100) not null);
go
alter table dbo.mytable
add constraint PK_mytable primary key (type_log,date_log,num_log);
go
Now you just have to create a stored procedure to insert data into your table, inside this SP you can make your logic to recycle you num_log column.
I hope this helps.
March 11, 2011 at 8:31 pm
DMS11X (3/11/2011)
I cant have the keys defined by MMDDYYYY-HHMMSS, Simply because we function 24hrs, and since we are on PST, every year in the fall the clock cycles back 1hr, essentially this would create the potential for duplicate keys as the clock has reset one hour every year... Trust me I was going to do this....
Did you check out the GetUTCDate() function? It's not affected by DST. 🙂
BTW, for sorting purposes, I'd suggest using YYYYMMDD-HHMMSS format. (Makes it a wee bit easier to find.) 😀
Now, to answer your question: use the undocumented system extended procedure xp_dirtree to load the file names into a table. Then count the number with that as the filename, and add one to it:
DECLARE @test-2 TABLE ([FileName] varchar(500), Depth tinyint, IsFile bit);
INSERT into @test-2
EXECUTE xp_dirtree 'D:\Temp\Videos\MCM Videos',0,1;
DECLARE @Counter smallint,
@Date char(8);
-- put the date in MMDDYYYY format
-- YYYYMMDD would be easier: convert(char(8), GetDate(), 112)
SET @Date = RIGHT('00' + CONVERT(VARCHAR(2), MONTH(GetDate())),2) +
RIGHT('00' + CONVERT(VARCHAR(2), DAY(GetDate())),2) +
CONVERT(CHAR(4), YEAR(GetDate()));
SELECT @Counter = COUNT(*) + 1
FROM @test-2
WHERE [FileName] LIKE 'AL-' + @Date + '%';
SELECT @Counter;
😎
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 11, 2011 at 9:34 pm
The "AL-" prefix seems like a complete waste. It's the same on every row, so it contains no infomation. If you need it for display purposes, just add it on your output.
If you need an incident number, why does it have to recycle each day? Use an identity column to generate a unique number that is never reused, and use that for the primary key.
Use a high precision UTC datetime so you don't have to worry about duplicated data due to DST, and capture the high precision system datetime at the same time to have the local time for reporting.
select
-- Converted to varchar only for display purposes.
-- Should be stored in a datetime2(7) datatype.
DT_UTC= convert(varchar(27),SYSUTCDATETIME(),121),
DT_LOCAL= convert(varchar(27),SYSDATETIME(),121)
DT_UTC DT_LOCAL
--------------------------- ---------------------------
2011-03-12 04:24:46.9012247 2011-03-11 23:24:46.9012247
March 12, 2011 at 6:00 am
You guys rock, thank you all for your valued suggestions, seriously!
i will try the assorted suggestions when i get home, hope my newb brain can figure out this SQL stuff... I will post back with any updates.
Again, thank you all!
~D
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply