Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Custom Date Key??? Expand / Collapse
Author
Message
Posted Friday, March 11, 2011 8:12 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 10, 2011 10:23 AM
Points: 16, Visits: 60
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!
Post #1076902
Posted Friday, March 11, 2011 9:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 656, Visits: 3,960
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.
Post #1076994
Posted Friday, March 11, 2011 11:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
DMS11X (3/11/2011)
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-#


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.
Post #1077091
Posted Friday, March 11, 2011 11:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 11:01 AM
Points: 656, Visits: 3,960
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.
Post #1077110
Posted Friday, March 11, 2011 7:26 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 10, 2011 10:23 AM
Points: 16, Visits: 60
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
Post #1077250
Posted Friday, March 11, 2011 7:58 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, August 8, 2014 10:51 AM
Points: 292, Visits: 439
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.
Post #1077256
Posted Friday, March 11, 2011 8:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:24 PM
Points: 5,370, Visits: 9,009
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 TABLE ([FileName] varchar(500), Depth tinyint, IsFile bit);
INSERT into @test
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
WHERE [FileName] LIKE 'AL-' + @Date + '%';

SELECT @Counter;




Wayne
Microsoft Certified Master: SQL Server 2008
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1077259
Posted Friday, March 11, 2011 9:34 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:32 PM
Points: 3,110, Visits: 11,529
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



Post #1077264
Posted Saturday, March 12, 2011 6:00 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, April 10, 2011 10:23 AM
Points: 16, Visits: 60
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
Post #1077308
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse