Working With Date Stamp
Apr 17, 2007
Hello Everyone and thanks fo ryour help in advance. I am working on a blog site that stores each blog entry in a SQL Server table. Each entry is date stamped with the time it is created in a datetime field called "TimeCreated". I want to be able to create some type of ability to sort the older blogs as an archive and display links on a pages such as "Archive - January 2007, Archive - December 2006" etc. I'm not exactly sure on how to go about doing this. any help on this topic would be greatly appreciated.
View 8 Replies
ADVERTISEMENT
Jul 26, 2005
Hi All,
I would like a field in my SQL Server DB, that autogenertates the date in it when a new record is created
Can anyone tell me what the datatype should be and what the expression int eh default value field property should be....
much appreciated
cheers
Tony
View 2 Replies
View Related
Jan 8, 2007
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
View 14 Replies
View Related
Jan 16, 2007
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.
View 4 Replies
View Related
Jul 23, 2005
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
Oct 30, 2006
Hi,
I have a date and time variable like dateVariable = '30/10/2006 12:45:36 AM'.(DD/MM/YYYY HH:MM:SS)
From this variable how to fetch only the date part in the format of MM/DD/YYYY
How to write sql query
I mean the result should be like 10/30/2006.
thnx
shaji
View 6 Replies
View Related
Dec 6, 2006
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
View 2 Replies
View Related
Mar 7, 2007
Hi,
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,
View 4 Replies
View Related
Dec 13, 2001
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!
View 1 Replies
View Related
Jan 11, 2007
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 Related
Aug 5, 2015
I am attempting to create a subscription that will send a report whose name would have been formatted to the following: ReportName_MMDDYYYY.
I know this can be done by creating a Windows File Share subscription type, but in this case, I would need to use E-Mail.
In this scenario, the user will receive the email and included, the file formatted as described above.
Is there a way to get it done ?
View 3 Replies
View Related
Feb 26, 2008
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,
View 3 Replies
View Related
Jul 8, 2015
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"
View 5 Replies
View Related
Oct 28, 2015
i have written a sql function which returns only number of working days (excludes holidays and Weekends) between given StartDate and EndDate.
USE [XXX]
GO
/****** Object: UserDefinedFunction [dbo].[CalculateNumberOFWorkDays] Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO
[code]...
I need a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date. Example scenario: If i give date as 12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table tblMasHolidayList where all the 2015 year holidays dates and info are stored.
View 18 Replies
View Related
Jul 2, 2006
Hi;I'm here for many hours trying to do this but i couldn't find a way.I have a table whith a field called [DOB], where i have people's date of birth.
Now, i need a SQL query to get people who's birthdays are in between two dates "BUT", what about the year on the date?
I use to do this on Access:
SELECT * FROM Members WHERE DATESERIAL(YEAR(NOW()), MONTH(DOB), DAY(DOB)) BETWEEN @startDate AND @endDate
In the query above the year is not a problem because the DateSerial() function add the current year for all birthdates making it easyer to user parameters like: 06/01/2006 to 06/30/2006
Unfortunately, SQL Server does not support DateSerial() function.
I appreciate any help on this.
Thanks a lot.
View 5 Replies
View Related
Jul 20, 2004
Hi,
I have a problem with a SQL that check if a date column IS NULL.
On one server the check work ok but on another (the same data is on both - restored copy) the check does not find any NULL values.
If check where datecolumn =convert(datetime,'9999-12-31 23:59:59.000',121) I get the same result as when checking for NULL in the other.
Is there any parameter set somewhere that tell the server to return a value even if NULL is stored in the database?
thank you for reading,
YakoBay
View 3 Replies
View Related
May 23, 2008
Hey Guys, I can’t get this date conversion stuff to work. I have a variable that holds the current date -1 day (@Yesterday). The date that is on the table (DTE) is in the format yyyymmdd (i.e. 20021213), but I cannot get the date to convert.
I have tried:
BEGIN
DECLARE @Yesterday as nvarchar (8);
set @Yesterday = (replace(CONVERT(VarChar(10), DATEADD(d, -1, getdate()), 101), '/', ''))
PRINT @Yesterday
select * from tblxxx
where Replace(Convert(varchar (10), DTE, 101), '/', '') = @Yesterday
END
@Yesterday = 05222008
DTE = 20080522 (after conversion)
Why can’t I get DTE to convert to 05222008
View 6 Replies
View Related
Jan 29, 2015
I need to calculate a date.example it needs to be 20 working days ago compared to today so that means it needs to not include any Saturday or Sunday in between
declare @start_date datetime
declare @end_date datetime
declare @working_days int
set @working_days = 20
[code]...
So I need to calculate @start_date but it needs to exclude any weekend days.@working_days is the number of working day I'm interested in.
View 1 Replies
View Related
Jul 23, 2005
Hello,I am importing data that lists rates for particular coverages for aparticular period of time. Unfortunately, the data source isn't veryclean. I've come up with some rules that I think will work to clean thedata, but I'm having trouble putting those rules into efficient SQL.The table that I'm dealing with has just under 9M rows and I may needto use similar logic on an even larger table, so I'd like somethingthat can be made efficient to some degree using indexes if necessary.Here is some sample (simplified) code:CREATE TABLE Coverage_Rates (rate_id INT IDENTITY NOT NULL,coverage_id INT NOT NULL,start_date SMALLDATETIME NOT NULL,end_date SMALLDATETIME NOT NULL,rate MONEY NOT NULL )GOINSERT INTO Coverage_Rates VALUES (1, '2004-01-01', '2004-06-01',40.00)INSERT INTO Coverage_Rates VALUES (1, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (1, '2004-06-01', '2004-08-01',30.00)INSERT INTO Coverage_Rates VALUES (2, '2004-01-01', '9999-12-31',90.00)INSERT INTO Coverage_Rates VALUES (2, '2004-03-01', '2004-08-01',20.00)INSERT INTO Coverage_Rates VALUES (2, '2004-08-01', '2004-08-01',30.00)GOThe rule is basically this... for any given period of time, for aparticular coverage, always use the coverage with the highest rate. So,given the rows above, I would want the results to be:coverage_id start_dt end_dt rate----------- ---------- ---------- --------1 2004-01-01 2004-06-01 40.001 2004-06-01 2004-08-01 30.002 2004-01-01 9999-12-31 90.00There can be any combination of start and end dates in the source, butin my final results I would like to be able to have only one distinctrow for any given time and coverage ID. So, given any date @my_date,SELECT coverage_id, COUNT(*)FROM <results>WHERE @my_date >= start_dtAND @my_date < end_dtGROUP BY coverage_idHAVING COUNT(*) > 1the above query should return 0 rows.Thanks for any help!-Tom.
View 9 Replies
View Related
Oct 23, 2007
Hello,
My SQL Server 2005 database table data definition is as follows:
ID int
LST_DATE DateTime
SUBJECT varchar(100)
An example of a record in the database table has a date format like this: 23/10/2007 16:56:00. My VB code as below:
Dim Command As New SqlClient.SqlCommand("SELECT ID,LST_DATE,SUBJECT FROM dbo.LISTS WHERE LST_TIME = GETDATE(), 100)))", conn)
Dim dataReader As SqlClient.SqlDataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
While dataReader.Read()
Dim NWAlert As New Alert(dataReader("SUBJECT").ToString(),DateTime.Parse(dataReader("LST_DATE").ToString())))
:
LST_TIME should be equals the CURRENT DATE & TIME in the format above to activate.
I have tried using the following:
WHERE LST_DATE = CONVERT(GETDATE(), 103) + ' ' + CONVERT(GETDATE(), 108)", conn)
WHERE LST_DATE = CONVERT(GETDATE(), 107) + ' ' + CONVERT(GETDATE(), 108)", conn)
But for some reason it is not working. Please help!
View 22 Replies
View Related
Apr 16, 2006
Hi,
The following INSERT query works in all aspects apart from the date value:
String InsertCmd = string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text);
The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900").
In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT.
What am I doing wrong? Thanks very much.
Regards
Gary
View 3 Replies
View Related
Oct 3, 2006
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2
oct 3
sep 13
sep 21
sep 22
sep 30
aug 3
aug 5
aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
View 7 Replies
View Related
May 20, 2008
I have a table with a single effective date, rather than both a start and stop date. I have to be able to match up this table to another one with service information in it and am not sure how to get the correct record selected.
So in table one I have a personID, effective date, and lots of other fields. There are also multiple records for each personID, so say personID 1 has records with effective dates of 1/1/2007, 6/1/2007, and 1/1/2008.
Table two has personID, Service Date, and lots of other fields.
So if I am looking to match up the effective row from table one to a record in table two with a service date of 8/1/2007, how do I get the db to locate and return the record with an effective date of 6/1/2007, and only this record?
View 6 Replies
View Related
Dec 5, 2007
Hi,
We are using [DATE] [TIME] functions in SQL Server 2000 agent jobs and SQL Server use to translate it to current data and time functions but in
SS2005 it is not replacing the functions and we are getting filename as "test_DATE_TIME" whereas we expect "test_20071204_130000"
Do we have any new functions as replacement?
Thanks
--rubs
Following is the code we are using:
declare @name nvarchar(100)
declare @name1 nvarchar(100)
set @name1 = 'test_[DATE]_[TIME]'
set @name = 'c:ackup' + @name1 + '.bak'
backup database test to disk = @name
View 8 Replies
View Related
Jun 2, 2007
Hi all,
I'm looking to return certain rows from our db into an SSRS based on a user selected date range using the parameters calendar.
My query/analyzer returns all required fields/rows, but how do I
pull the specific rows, (that are based on a date range that the user enters),into the report? I've tried expressions, and vb functions to no avail. The users will be using the calendar parameter to select date ranges .So far the reports pull in all rows from my query.
Thanks,
Scott
View 1 Replies
View Related
Mar 4, 2004
Hi everyone.
I know, I know, it should have been a datetime from the start...but here's the problem.
I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:
ORDER BY [Date]
Are there any work arounds for this? Is there some way of doing:
ORDER BY covert(datetime, [Date], 103) or something?
Cheers
Andrew
View 3 Replies
View Related
Apr 30, 2014
I have a table with 5000 rows history about working and retired time.
It is the several rows from table(User, StartDate, EndDate):
User1 2011-05-09 00:00 2014-01-17 00:00
User2 2012-07-01 00:00 2012-08-20 00:00
User2 2013-08-26 00:00 2013-09-02 00:00
User2 2013-10-07 00:00 NULL
User3 2013-09-01 00:00 2014-04-07 00:00
User3 2014-04-08 00:00 NULL
How many users have worked and have retired by years and months,
Example:
2011-01 working 2000
2011-02 retired -50
2011-02 working 1950
2011-02 retired -27
and etc,,
Does it need to join to a time dimension table?
View 7 Replies
View Related
Sep 4, 2014
I need to be able to add minutes to a datetime value, which only cover working hours.
I have a holiday table as below:
Examples: (dd/MM/yyyy hh:mm)
Date..........Description
01/01/2015 New Years Day
26/12/2014 Boxing Day
25/12/2014 Christmas Day
25/08/2014 August Bank Holiday
Our Business hours are 08:00-18:00 Mon-Fri (unless the day is in the holiday table)
Start Date............Minute to Add......Expected outcome
01/09/2014 10:00........30...................01/09/2014 10:30
01/09/2014 17:00........65...................02/09/2014 08:05
29/08/2014 17:00........65...................01/09/2014 08:05
22/08/2014 17:00........65...................26/08/2014 08:05
31/08/2014 02:30........65...................01/08/2014 09:05
01/09/2014 19:00........65...................02/08/2014 09:05
01/09/2014 10:00........3005...............08/09/2014 10:05
22/08/2014 17:00........3005...............01/09/2014 17:05
I have tried to create a function to do this (fn_pp_AddMinutesWithinWorkingHours(@StartDate,@Minutes)) but I am unable to come up with a solution which factors in everything correctly.
View 1 Replies
View Related
Aug 22, 2007
Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?
Thanks...
View 2 Replies
View Related
Dec 11, 2007
I'm a SQL newbie, and I'm trying to write a report that returns records based on a beginning and end date that the user supplies. But when I run the query and supply the dates (begin 11/29/2007 / end 11/30/2007, for example), I'm only getting back one record, when there should be at least 3. It appears that my query is ignoring the =, and only returning those records that have a date > or <, not <= or >=.
I was told that it's possible that the time needs to be converted to UTC as well, but I'm not sure how to do that ( I tried, but I really don't know what I'm doing so I commented it out)... Can someone offer some guidance on how to get all the records to show up? I've pasted my query below, thanks in advance!
SELECT FilteredContact.fullname, FilteredContact.new_memberleadsourcename, FilteredContact.new_prospectforclubname, FilteredContact.owneridname,
FilteredContact.new_referred_by, convert(varchar(10),FilteredContact.createdon,101) as Date--, SELECT GETDATE(FilteredContact.createdon) AS CurrentTime, GETUTCDATE(FilteredContact.createdon) AS UTCTime
FROM FilteredContact JOIN filteredOpportunity on
FilteredOpportunity.contactid = Filteredcontact.contactid
where filteredopportunity.new_opportunitytype = 1 and (FilteredContact.createdon >= '11/29/2007') and (FilteredContact.createdon <= '11/30/2007')
View 3 Replies
View Related
May 6, 2008
Hi,
I need to calculate the number of working days from a date backwards. For example 2 working days before Thursday would be the Tuesday (as a basic example)
I use the following code and a Calendar table to calculate the working days from a date but can anyone help with reworking this query to do the reverse
declare @WorkingDate as datetime
SELECT @WorkingDate=dt
FROM tblCalendar AS c
WHERE (@WorkingDays =
(SELECT COUNT(*) AS Expr1
FROM tblCalendar AS c2
WHERE (dt >= @StartDate) AND (dt <= c.dt) AND (IsWeekday = 1) AND (IsHoliday = 0))) AND (IsWeekday = 1)
AND (IsHoliday = 0)
-- Return the result of the function
RETURN convert(varchar(12),@WorkingDate,106)
Hope someone can help
Thanks
View 3 Replies
View Related
Oct 22, 2007
Hi, I've got this ssrs 2005 report that works great passing a few
security related parameters from asp.net codebehind. However, there
are two date related parameters that won't be coming from my web form,
but rather from the report form itself. When I test the report's date
parameters from visual studio it work fine, but when I attempt the
same report from a reportviewer no matter what input I place on the
report's date fields or even if I select the date picker, the report
simply resets to default and reloads. And actually the date picker
from the reportviewer does not not even pop up.
Here's my aspx code:
<%@ Page Language="VB" AutoEventWireup="false"
Inherits="_ReportViewer" MasterPageFile="~/Main/MasterPage.master"
CodeFile="~/Reports/CashSales.aspx.vb" Title="Cash Sales" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms,
Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<asp:Content ID="Content1" ContentPlaceHolderID="Content1"
runat="Server">
<rsweb:ReportViewer BackColor="Transparent" ZoomMode="PageWidth"
Width="100%" ProcessingMode="Remote" ID="ReportViewer1"
runat="server">
<ServerReport ReportPath="/Retailer/CashSales"
ReportServerUrl="http://myserver/reportserver" />
</rsweb:ReportViewer>
</asp:Content>
Note: I'm only passing the parameters that are not coming from the
reportviewer form.
the code behind:
Imports system.web.security.membership
Imports system.web.security.Roles
Imports Microsoft.Reporting.Webforms
Partial Class _ReportViewer
Inherits System.Web.UI.Page
Private Users As New Retailer.Core()
Protected Overrides Sub OnLoad(ByVal e As EventArgs)
Dim Roles() As String =
GetRolesForUser(Page.User.Identity.Name.ToString)
Dim cred As New Retailer.ReportServerCredentials("myuser",
"mypassword", "mydomain")
ReportViewer1.ServerReport.ReportServerCredentials = cred
Dim param As New ReportParameter("r_user",
Page.User.Identity.Name.ToString)
Dim param2 As New ReportParameter("r_role", Roles(0))
Dim p() As ReportParameter = {param, param2}
ReportViewer1.ServerReport.SetParameters(p)
ReportViewer1.ServerReport.Refresh()
End Sub
End Class
on my reportviewer form, at the top I have parameters for startdate
and end date, they are not set to internal or hidden.
If I select the calendar icon, i get a javascript error
Line: 606
Object Required
When I attempt to debug I get a Just-In-time failed : Unspecified
error. Check the documentation index for 'Just-in-time debugging,
errors' for more information. So pretty much I can't see the error or javascript in question.
If i enter anything in the date fields, it disregards them setting them to the defaults.
Again, if i run the report from my vs.net client (not using reportviewer), i can select the calendar and enter dates and it respects them.
Any chance I need to patch sql server or reporting services? maybe ie. I'm on IE 7.0.5730.11
Could my problem be that selecting items on the report itself fails to send my credential information from my codebehind?
Thanks for any help or information!
View 26 Replies
View Related
Aug 24, 2015
SSRS Date Picker is not working in Google Chrome, it works in both IE and Firefox.
View 3 Replies
View Related
