How do I get the file name appended with the time stamp info like this:
Order_file__2002626_8_34_4.csv in an automated process which generates the file? Any help??
Thanks.
Reddy.
Hi,
I would like to know as to how can I put a Current Date & Time stamp on a FILE NAME automatically which is created by a DTS package.
E.g. I create a file named ABC.TXT daily. How do I get this file to have the current Date stamp so that the file name is ABC121201.TXT without any user intervention.
Thanks for the help!
I'm trying to create a trace file with the file as part of it's name.For example, I'd like to create a file called FailedLogins-20050428. Sofar I haven't been able to figure out how to get the name of the fileand the date together (I'm sill very new to SQL Server and tracing).What I've done is:declare @rc intdeclare @traceid intdeclare @maxfilesize bigintset @maxfilesize = 50exec @rc=sp_trace_create @traceid=@traceid output, @options=0,@tracefile=N'C: racefailedlogins', @maxfilesize=@maxfilesize,@stoptime=NULLif @rc > 0 print 'sp_trace_code failed with error code ' +rtrim(cast(@rc as char))else print 'traceid for the trace is ' + rtrim(cast(@traceid as char))I can create a trace file on C drive without difficulty. I've triedcreating a file like this:exec @rc=sp_trace_create @traceid=@traceid output, @options=0,@tracefile=N'C: racefailedlogins + convert (varchar,getdate(),112',@maxfilesize=@maxfilesize, @stoptime=NULLBut what I end up created is a file on C calledfailedlogins + convert(varchar,getdate(),112).trcI have no doubt what I want to do can be done. I just done know how todo it.If anyone could tell me where I'm going wrong, I'd really appreciateit.Thanks in advance.
View 2 Replies View RelatedHi ALL,
I have something that i have been wondering about for a while now and hope someone can help clear this up for me.
My mdf and ldf files on my server have a time stamp from 2 days however the database is in use 10 hours a day including backup time. Is there any reason for this the OS is XP SP2 and SQL 2000 MSDE.
Hope some one can help on this
TA
Stumblin
I am pretty new at this so please bare with me.I have a page working perfectly but I just want to ad a column that willshow the time it was entered into the database. I think all I need is thesyntax for writing the server time to the TimeStamp field in the record. Ihave hidden field in the form that I alocated. So how do I get it to writethe current time to the database. Here is the current code:<form name="form1" method="POST" action="<%=MM_editAction%>"><table width="500" cellpadding="2" cellspacing="0"><tr><td width="242" class="tdtl">Are you coming to the lake this weekend?</td><td width="244" class="tdtr"><input name="Answer" type="radio"value="Yes" checked>Yes <input name="Answer" type="radio"value="No">No</td></tr><tr><td class="tdleft">Your Name Please </td><td class="tdright"><input name="Name" type="text" id="Name"size="40"></td></tr><tr><td class="tdleft">I will be arriving on . . . </td><td class="tdright"><select name="Arriving" size="1" id="Arriving"><option value="Cant make it">Cant make it</option><option value="Friday">Friday</option><option value="Saturday">Saturday</option><option value="Sunday">Sunday</option><option value="Monday">Monday</option><option value="Tuesday">Tuesday</option><option value="Wednesday">Wednesday</option><option value="Thursday">Thursday</option></select></td></tr><tr><td class="tdleft">Boathouse</td><td class="tdright"><input name="BoatHouse" type="text" id="BoatHouse"size="6" maxlength="6"> </td></tr><tr align="center"><td colspan="2" class="tdleft"><input name="Submit" type="submit"onClick="MM_validateForm('Name','','R');return document.MM_returnValue"value="Submit"></td></tr></table><input type="hidden" name="MM_insert" value="form1"><span class="tdright"><input name="TimeStamp" type="hidden" id="TimeStamp"value="<%=(rsList.Fields.Item("TimeStamp").value)%>"></span></form>Is it this last "Input" statement that I need to change to get this to workright?ThanksHouston
View 2 Replies View RelatedHow do we put a time stamp on a report?
Thanks
Hi All,
How can I add the date/time stamp to the end of the text file? This file is being created as a result of query output of the bcp command:
'bcp "Select * from ##Test" queryout C:TestJobForDB.txt -S servername -U user -P password -c'
I need to add date/time stamp to the JobForDB.txt file before the period.
Thanks
Hi All,
I have a script that adds the date/time stamp to a file in the following format:
200701120149PM.
here is the script:
set dttm=%~t1
for /F "tokens=1-6 delims=/: " %%i in ("%dttm%") do (
set date=%%k%%i%%j%%l%%m
)
I need to display the time as military. How can I do that?
Thanks.
How can I know when a record on a table has been modified ?
I want to add a field and fill it with a date/time when the recors is modified
Thanks
Hello, first post here.
I am re-writing a web based application and trying to make improvements this go round.
Problem Statement:
I have two tables that need to work independently, but may be related at any given time.
tblAccount
accountID
accountName
accountNumber
lastUpdate
lastEng
etc...
tblEscalation
escID
fk_accountID
escNumber
escCustomer
description
notes
lastUpdate
lastEng
etc...
Escalations come in at any time and need to be tracked. If the escalation gets hot enough there is a need to track this as a "Hot Site" by the account name. Once tracked under the account name, the account may have numerous escalations. Additionally, it is possible to open an account and not yet have any escalations.
My problem is that I need to track on the account level when the last change was made, so I would need to find the MAX date of either the tblAccount.lastUpdate or any of the related tblEscalation.lastUpdate fields. Is there a preferred way to do this?
In the last iteration I just performed a getdate() on either set of records and have a grotesque query that pulls the max date, but it only works if both tables have related data. Maybe just my query is bad, but seems like I'm missing something obvious here.
I also need to track the engineer who made the last change.
Any ideas?
Thanks!
When a record is written to a table (via a asp form), I'd like the timeand date from the server to automatically populate a column in thattable. From what I can tell, timestamp isn't working. I rather nothave the time come from the client.Thanks for the help.
View 2 Replies View Related
I am moving the data from a flat file to a table in SQL Server 2005.
One of the field in the table is time stamp which tells me when the data is loaded.
Which is best way to populate this field (time stamp) with constant time stamp for the entire batch data?
Thank you,
SQL Server 2005 SP2.
Is there a property setting in SqlEX. That automatically inserts the date and time in to a field (timestamp) in the dB, when a record is created. If so can someone please show me how this is done.
Thanks in advance
I create back end table on SQL Server and front end in Access. On my sql table I select the datatype as date/time and on the default value on the field I put this one (dateadd(day,datediff(day,0,getdate()),0). When I input the data in to a SQL table and refresh its showing correct as 12/06/2006. But when I checked in access its showing the date data as 2006-12-06 00: but I want to show 12/06/2006. Can you tell what I have to do SQL table default field to come out like 12/06/2006 not 2006-12-06 00:. Thanks and appreciate your comments.
moetahsen
I have a shopping cart that will get full from time to time becausecustomers click out of the site before they confirm their purchase,therefore leaving a full cart behind. I'd like to have a timestamp onthis table, so that I can delete anything that I find is more than a dayold.How can I do this?Thanks,Bill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies View RelatedHi,I am having a problem with aquery. Firstly here is a script to createthe table and insert some sample data:CREATE TABLE [dbo].[tbltemp999] ([Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,[EventDate] [datetime] NOT NULL ,[EventID] [int] NOT NULL) ON [PRIMARY]GOINSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:05:14.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-08-16 12:08:32.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:24:45.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-09-22 12:28:25.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:53:38.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-03 17:57:02.000',6005)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:09:10.000',6006)INSERT INTO tbltemp999 VALUES ('MOBL','2004-11-04 12:12:48.000',6005)The EventIDs 6005s represents a system start up and an EventID 6006represents a system shut down/restart. I already have a query tocalculate the downtime of a particular date range. So when i want tofind the total uptime of a date range in the past i do the following:Date1 00:00:00.000 to Date2 23:59:59.999 then subtract the downtime.My problem arises when Date2 is todays date as the time of23:59:59.999 would not have reached so the calculations are alwayswrong.How can I make the Date2 also read the current time?If this is vague info feel free to ask me to explain better.ThanksSunny
View 1 Replies View RelatedHi all:
first of all, i must say that this website is just awesome...
my question is how do i truncate the time portion in a datetime stamp in a single sql statement.
thanks.
I have a table (tbl_entries) in my db that has a timestamp field (startDate). What I need to do is run a query that counts the number of records on a 15 min interval.
something like
start_date
2008-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-01 00:00:00.000
2008-01-01 00:01:00.000
2008-01-01 00:01:00.000
2008-01-01 00:01:00.000
2008-01-01 00:14:00.000
EVERY THING ABOVE HERE IS IN GROUP 1
2008-01-01 00:35:00.000
EVERY THING ABOVE HERE IS IN GROUP 2
2008-01-01 01:01:00.000
2008-01-01 01:03:00.000
2008-01-01 01:03:00.000
2008-01-01 01:04:00.000
EVERY THING ABOVE HERE IS IN GROUP 3
2008-01-01 01:29:00.000
EVERY THING ABOVE HERE IS IN GROUP 4
2008-01-01 01:41:00.000
EVERY THING ABOVE HERE IS IN GROUP 5
2008-01-01 02:25:00.000
2008-01-01 02:28:00.000
2008-01-01 02:31:00.000
2008-01-01 02:33:00.000
EVERY THING ABOVE HERE IS IN GROUP 6
Hope this is enough Info
Down below is my tables.
If I want to add time stamp in log_old_val table.
what value in log_old_val and cone in trigger have to be modified?
thanks
************************************************** *****
--main table
> create table test (manufacturer varchar(500), score int)
> insert into test values('Toyota', 1 )
> insert into test values('Toyota', 2)
>
> --logging table
>
> create table log_old_val(manufacturer varchar(500), score int, operation
> varchar(10))
>
> --trigger to log old value into log_old_val table.
> create trigger tr_man on test
> for update,delete
> as
> if @@rowcount = 0
> return
> if exists (select * from inserted)
> if exists (select * from deleted)
> insert into log_old_val
> select manufacturer,score, 'update' from deleted
>
> if exists (select * from deleted)
> if not exists (select * from inserted)
> insert into log_old_val
> select manufacturer,score, 'delete' from deleted
> go
Hi - I am a bit of an amatuer, but I am trying to use the EMS data Pump to get all of the data out of an Interbase 6 DB into an MSDE DB. I have managed to instal an Interbase ODBC driver, and I ca connect to it and then automatically create the tables in my MSDE DB, but I get an error stating that there cannot be multiple Timestamp columns in the MSDE databse. Is this true ?
View 3 Replies View RelatedHi,
I'm pretty new at this, writing SQL and reporting services. I created a report with a date parameter. I need the report to ignore the timestamp. My @Startdate is fine because the timestamps is at 12:00:00AM but my @EndDate also has this timestamp. I need to pull all the data up to the end date the user enters without taking the timestamp into consideration.
If someone can help me out with, I would greatly appreciate it.
Thanks,
I would like to make a function to convert a datetime to half-hour. E.g. If the timestamp is 1:23:05 then converts to 1:30:00, if 1:35:27 then converts to 2:00:00.
Anyone has any idea? Thanks.
I need to separate the date stamp (which looks like this 2006-10-05 09:08:41.000) into the date in this format 05OCT2006 and then the date stamp separate in this format 09:08. Thanks!
View 2 Replies View RelatedHey All,
I am new to programming in SQL developer and I was hoping one of you kind salmon of knowledge could help me
I am running an SQL script every 15 minutes to pull data. I would like to insert the results into a column in a table. I have two issues:
1.How can I give the result column the current time stamp as an alias?
2.How can I add this column to the results table (I know its Alter table but do I put this into the insert sql?)
I would appreciate any help possible
Thanks
The following question might sound a bit stupid but I'm not a database expert so hopefully nobody minds me asking it.
Here's what I did:
1. I created an SSIS package that is supposed to import new data into my data warehouse as it becomes available.
2. Since I need to maintain some of the history I use the Slow Moving Dimensions part (set the history flag on input fields) but run into an error condition while running the package. The message basically says that I'm about to create a duplicate record which is not allowed.
Original Table1:
PK1 field1
PK2 field2
PK3 field3
field4
field5
-----------
Now I enhanced it like this:
Extended Table1:
PK1 field1
PK2 field2
PK3 field3
field4
field5
CreateDate (new)
NewDate (new)
ActiveFlag (new)
_____________________
Now on some records the package is supposed to archive history by populating the (new) fields. In order to keep the record unique (primary key constraint) thought, do I need to make the (new) fields primary keys as well?
So I guess I'm struggling with a more basic concept;)
I would appreciate if somebody could shed some light on this.
Thanks in advance.
Dirk
SQL Express 2005 with one instance on a networked dedicated stand-alone window XP€¦
We use this for custom software written in VB.net. We are not SQL experts€¦
Question 1) I am getting a event viewer warning that says
The time stamp counter of CPU on scheduler id 1 is not synchronized with other CPUs.
This seems strange, since there is only one CPU on this machine. Any ideas?
Question 2) In SQL Server Management Studio Express, we do not have the actual data files attached to the instance, but the custom program does use the actual data files just fine, defined by the connection string. Is there any pro/con to having them attached in SQL Server Management Studio Express?
Thanks!
Bob
Hi,
Get some errors in my log, does anyone knows how I can turn off all logging?
Indeed error should be investigated, but for the moment I just need to turn off all loggings.
Cheers!
Rickard
64 bit sql 2005 - SP2 CTP is creating several of this messages in the log. Could somebody tell me what is the impact of these on the performance?
Also, could anybody from MS have any idea as to the release date of SP2 ( Non CTP)
Hi All,
I have a dataflow task that creates a text file and posts it in a FTP share.
In need to rename the file attaching datestamp(current date of package execution) at the end of filename.
For ex: My dataflowtask creates a file called 'Samplename.txt'
I need to rename it to : 'Samplename20080225.txt.
I think it can be done using the File task...I see an option to rename the file but not sure of how to configure the task to attach current datestamp.
I see some suggestions online, but complete steps on how to implement the above would be highly appreciated.
Thanks,
I would like to know if it is possible to date stamp the output file for the DBCC integrity check?
The following cmd works fine however I tried different ways to date stamp the output file with no luck.
sqlcmd -U backup -P Password -S Server -Q"DBCC CHECKDB('DatabaseName') WITH ALL_ERRORMSGS" -o"G:LOGSDBCCResults.txt"
This post is to provide information and Script Library links related to datetime. There are also links to other resources.
List of Subjects
Typical Date Query
Uses of the DATETIME data type
Finding the Start of Time Periods
Finding the End of Time Periods
Generating Date Tables
Getting Time Only from DateTime
Finding Age
Finding ISO Weeks
Converting Year, Month, and Day to DateTime
Converting to/from UNIX Time
Finding the midpoint between two datetimes
Generating Random Datetimes
Creating a Formatted Calendar
Links to other Date, Time, and Calendar Resources
Typical Date Query
How to query a table with a selection on a datetime column, for example, find all items for the date 2006-01-14. This isn’t really a script, but it is one of the most common questions about datetime.
Select
*
from
NyTable
Where
MyDateColumn >= '20060114' and
MyDateColumn < '20060115'
Notice that you are asking for greater than or equal to the beginning of the date, and less than the following date. You can apply the same general query for any range of days. This is almost always the best way to write a query of this type, because it allows SQL Server to use any index that exists on the datetime column, and it uses less resources than a query that applies a function to the datetime column.
Notice that the query dates are in format YYYYMMDD; you should always use this format for date strings. This is SQL Servers "universal" date format that works the same with all settings of DATEFIRST. Any other format may produce an error if the setting of DATEFIRST is not exactly what you expect.
For datetime strings use universal format
YYYYMMDD HH:MM:SS.MIL (20061231 23:59:59.997).
Uses of the DATETIME data type
The DATETIME data type can be used to hold four different types of date/time values:
1. Date and time – a date and time together
Example: 2006-07-15 12:06:15.333
2. Date – a date only stored as the time at midnight:
Example: 2006-07-15 00:00:00.000
3. Time – a time only stored as time on the DATETIME zero date, 1900-01-01.
Example: 1900-01-01 12:06:15.333
4. Elapsed time – a difference between two DATETIME values, stored as the time since the DATETIME zero point, 1900-01-01 00:00:00.000.
Example: 1900-01-03 14:12:34.443
The actual usage of the value is only defined in the context of the application. There is no way to specify that a DATETIME is to be used for a date and time, date only, time only, or elapsed time. It is possible to insure that a column in a table contains date only or time only by adding a constraint to a column, but is is necessary that the application format the DATETIME value properly.
The following script briefly demonstrates the four different ways to use DATETIME, and several conversions from one type to another: date and time to date only, date and time to time only, date only plus time only to date and time, two date and time values to elapsed time, and elapsed time to individual days, hours, minutes, seconds, and milliseconds.
-- Demo four uses of DATETIME datatype
declare @datetime1 datetime
declare @datetime2 datetime
declare @date_only datetime
declare @time_only datetime
declare @date_plus_time datetime
declare @elapsed_time datetime
declare @elapsed_days int
declare @elapsed_hours int
declare @elapsed_minutes int
declare @elapsed_seconds int
declare @elapsed_milliseconds int
-- Load 2 datetime values
select @datetime1 = '20060715 12:06:15.333'
select @datetime2 = '20060718 02:18:49.777'
-- Get date only from datetime using DATEAADD/DATEDIFF functions
select @date_only = dateadd(day,datediff(day,0,@datetime1),0)
-- Get time only from datetime by subtracting date only
select @time_only = @datetime2-dateadd(day,datediff(day,0,@datetime2),0)
-- Add date only and time only together
select @date_plus_time = @date_only+@time_only
-- Get elapsed time as the difference between 2 datetimes
select @elapsed_time = @datetime2-@datetime1
-- Get elapsed time parts as time since 1900-01-01 00:00:00.000
select @elapsed_days = datediff(day,0,@elapsed_time)
select @elapsed_hours = datepart(hour,@elapsed_time)
select @elapsed_minutes = datepart(minute,@elapsed_time)
select @elapsed_seconds = datepart(second,@elapsed_time)
select @elapsed_milliseconds = datepart(millisecond,@elapsed_time)
declare @cr varchar(4), @cr2 varchar(4)
select @cr = char(13)+Char(10)
select @cr2 = @cr+@cr
print'Results:'+@cr2
print'Datetime1 = '+convert(varchar(30),@datetime1,121)+@cr+
'Datetime2 = '+convert(varchar(30),@datetime2,121)+@cr2
print'Date Only = '+convert(varchar(30),@date_only,121)+
', from Datetime1 = '+convert(varchar(30),@datetime1,121)+@cr2
print'Time Only = '+convert(varchar(30),@time_only,121)+
', from Datetime2 = '+convert(varchar(30),@datetime2,121)+@cr2
print'Add date and time: '+convert(varchar(30),@date_plus_time,121)+' ='+@cr+
' '+convert(varchar(30),@date_only,121)+
' + '+convert(varchar(30),@time_only,121)+@cr2
print'Elapsed Time: '+convert(varchar(30),@elapsed_time,121)+' ='+@cr+
' '+convert(varchar(30),@datetime2,121)+
' - '+convert(varchar(30),@datetime1,121)+@cr2
print'Elapsed Time Parts:'+@cr+
' Days = '+convert(varchar(20),@elapsed_days)+@cr+
' Hours = '+convert(varchar(20),@elapsed_hours)+@cr+
' Minutess = '+convert(varchar(20),@elapsed_minutes)+@cr+
' Secondss = '+convert(varchar(20),@elapsed_seconds)+@cr+
' Milliseconds = '+convert(varchar(20), @elapsed_milliseconds)+@cr2+@cr2Results:
Datetime1 = 2006-07-15 12:06:15.333
Datetime2 = 2006-07-18 02:18:49.777
Date Only = 2006-07-15 00:00:00.000, from Datetime1 = 2006-07-15 12:06:15.333
Time Only = 1900-01-01 02:18:49.777, from Datetime2 = 2006-07-18 02:18:49.777
Add date and time: 2006-07-15 02:18:49.777 =
2006-07-15 00:00:00.000 + 1900-01-01 02:18:49.777
Elapsed Time: 1900-01-03 14:12:34.443 =
2006-07-18 02:18:49.777 - 2006-07-15 12:06:15.333
Elapsed Time Parts:
Days = 2
Hours = 14
Minutess = 12
Secondss = 34
Milliseconds = 443
Finding the Start of Time Periods
One of the most common questions is how to remove the time from a datetime so that you end up with just a date. In other words, change 2006/12/13 02:33:48.347 to 2006/12/13 00:00:00.000. The following links have functions that will find the start of Century, Decade, Year, Quarter, Month, Week, Day, Hour, 30 Minutes, 20 Minutes, 15 Minutes, 10 Minutes , 5 Minutes , x number of Minutes ,Minute , or Second.
Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755
Start of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307
Start of Week Function, Part Deux:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=59927
Convert DateTime to Date using Rounding UDF:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62354
Finding the End of Time Periods
Sometimes there is a need to find the last day of a time period. The following links have functions that will find the last day of Century, Decade, Year, Quarter, Month, or Week.
End Date of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64759
End of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64760
Generating Date Tables
It can be very useful to have a table with a list of dates, and various attributes of those dates, especially for complex reporting. The functions on these links can be used to load a date table with many different columns of date attributes.
Date Table Function F_TABLE_DATE:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519
Here is another approach that also includes a function for calculating Easter. I haven’t tried it myself.
Create Date Table with UK & Easter bank holidays:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49711
Getting Time Only from DateTime
By convention, a time only column is stored in SQL Server as an offset from 1900-01-01 00:00:00.000. The function on this link will get the time from a datetime value.
Time Only Function: F_TIME_FROM_DATETIME
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65358
Finding Age
Computing the age of someone is more difficult than it might seem when you take into account different month lengths, leap year, and other things.
This function returns age in format YYYY MM DD.
Age Function F_AGE_YYYY_MM_DD:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62729
This function returns age in years.
Age Function F_AGE_IN_YEARS:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74462
This link is more of a discussion of the problem of calculating age than a script you can use, but it does show the difficulties. I haven’t tried it myself.
Calculating age in years:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=11578
Finding ISO Weeks
The ISO 8601 standard for dates defines a standard way of assigning a unique number to each week starting on Monday. The following functions can be used to return ISO weeks. The date table functions mentioned in the "Generating Date Tables" subject above also have columns for ISO weeks.
ISO Year Week Day of Week Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60515
ISO Week of Year Function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60510
Converting Year, Month, and Day to DateTime
The functions on this link will take input parameters of Year, Month, and Day and return a datetime. There are several version posted.
Make Date function (like in VB):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=22339
Converting to/from UNIX Time
The functions in this script can be used to convert to/from SQL Server date time to UNIX Time.
UNIX Time Conversion Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858
Finding the midpoint between two datetimes
The function in this script finds the midpoint in time between two datetimes.
Datetime Range Midpoint Function
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68806
Generating Random Datetimes
The functions on this link can be used to generate random datetimes, random integers, and random samples.
Random Integer, Sample, and Datetime Functions
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499
Creating a Formatted Calendar
There are several methods is this link that will return a result set with a formatted calendar.
Calender In Sql Server:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=44865
Links to other Date, Time, and Calendar Resources
This post has links to other resources for date and time information, as well as many other commonly asked questions about SQL Server.
FAQ - Frequently Given Answers:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210
This blog entry has links to various date time subjects.
Fun with Dates (Date Conversion examples):
http://weblogs.sqlteam.com/brettk/archive/2005/06/02/5528.aspx
This external link has a lot of information on the SQL Server datetime datatype, writing queries with datetime, and various datetime operations.
Demystifying the SQL Server DATETIME Datatype:
http://www.sql-server-performance.com/fk_datetime.asp
These external links are a series of articles about working about working with SQL Server Date/Time.
Working with SQL Server Date/Time Variables:
http://www.databasejournal.com/features/mssql/article.php/10894_2191631_1
Part Two - Displaying Dates and Times in Different Formats:
http://www.databasejournal.com/features/mssql/article.php/10894_2197931_1
Part Three - Searching for Particular Date Values and Ranges:
http://www.databasejournal.com/features/mssql/article.php/10894_2209321_1
Part Four - Date Math and Universal Time:
http://www.databasejournal.com/features/mssql/article.php/10894_2216011_1
This external link explains how the datetime datatypes work in SQL Server, including common pitfalls and general recommendations.
Guide to the datetime datatypes:
http://www.karaszi.com/SQLServer/info_datetime.asp
These external links discuss the ISO 8601 standards of dates and times.
Numeric representation of Dates and Time:
http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html
ISO 8601:
http://en.wikipedia.org/wiki/ISO_8601
A summary of the international standard date and time notation:
http://www.cl.cam.ac.uk/~mgk25/iso-time.html
This external link explains how time is calculated on UNIX systems.
Unix Time:
http://en.wikipedia.org/wiki/Unix_time
These are external links to the U.S. Naval Observatory, an authority in the area of Precise Time.
The U.S. Naval Observatory Home:
http://www.usno.navy.mil/
The Official Standard of Time for the United States:
http://tycho.usno.navy.mil/
This external link has Clock, Calendar, Time Zone, and Holiday information for most of the world:
http://www.timeanddate.com/
This external link has a lot of information on the subject of Calendars.
Frequently Asked Questions about Calendars:
http://www.tondering.dk/claus/calendar.html
If you don't have any idea what all this is about,
You may need to Learn SQL:
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp
And finally, the primary Microsoft SQL Server References:
SQL Server 2000 Books Online
http://msdn2.microsoft.com/en-us/library/aa257103(SQL.80).aspx
SQL Server 2005 Books Online
http://msdn2.microsoft.com/en-us/library/ms130214.aspx
CODO ERGO SUM
Hello,I'm trying to create a simple back up in the SQL Maintenance Plan that willmake a single back up copy of all database every night at 10 pm. I'd likethe previous nights file to be overwritten, so there will be only a singleback up file for each database (tape back up runs every night, so each daysback up will be saved on tape).Every night the maintenance plan makes a back up of all the databases to anew file with a datetime stamp, meaning the previous nights file stillexists. Even when I check "Remove files older than 22 hours" the previousnights file still exists. Is there any way to create a back up file withoutthe date time stamp so it overwrites the previous nights file?Thanks!Rick
View 5 Replies View RelatedHow do i get the mentioned format. if today is 7th July then,2015-07-07 15:30 i.e. date with HH and MM appended in today's date?
want just one simple statement. don't want to write big query as i want to use this condition in one of the joining criteria.