I'm trying to find the first Monday for any given year. I've found something
that works, but I typically find the most convoluted way to do a simple task.
Is there something simpler/cleaner than this?
SET @dtmWorkDate=CAST('01/01/' +CAST(@intYear AS CHAR(4)) AS DATETIME)
SET @dtmFirstMon=(SELECT CASE
WHEN DATEPART(dw, @dtmWorkDate)=2 THEN @dtmWorkDate
WHEN DATEPART(dw, DATEADD(D,1,@dtmWorkDate))=2 THEN DATEADD(D,1,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,2,@dtmWorkDate))=2 THEN DATEADD(D,2,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,3,@dtmWorkDate))=2 THEN DATEADD(D,3,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,4,@dtmWorkDate))=2 THEN DATEADD(D,4,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,5,@dtmWorkDate))=2 THEN DATEADD(D,5,@dtmWorkDate)
WHEN DATEPART(dw, DATEADD(D,6,@dtmWorkDate))=2 THEN DATEADD(D,6,@dtmWorkDate)
END)
PRINT(@DTMFIRSTMON)
hello I have this code Select Waste.WasteName AS [Waste Name], InvoiceDetail.Volume, Branch.BranchLocation AS [Branch Location] From InvoiceHeader INNER JOIN InvoiceDetail ON InvoiceHeader.InvoiceNo = InvoiceDetail.InvoiceNo INNER JOIN Waste ON InvoiceDetail.WasteID = Waste.WasteID INNER JOIN Branch ON InvoiceDetail.BranchID = Branch.BranchID Where WasteName = 'Sludge' AND InvoiceDate >= '" + TextBox1.Text + "' AND InvoiceDate <= '" + TextBox2.Text + "'; The problem is everytime I will search for example InvoiceDate 03/27/2008, I need to add one day for example --> 03/28/2008 for me to be able to get that Invoicedate 03/27/2008.. What's do you think is the problem with my code? Thanks!
hi, when i use the getdate() function, i will get '4/14/2004 AM 11:25:56'. how can i edit the getdate() function to get just the date only '4/14/2004'.
I can get the date to list all of the dates in the recordset, but when I use request.querystring("date") to specify all the recordsets with the date "2/26/2005" for example, the recordset comes up empty. Here is my sql statement:
SELECT eventdate=convert(varchar, eventdate, 101), eventtext, eventtime, location, eventtitle, eventID, picture FROM dbo.calendar WHERE eventdate like 'thee' and status='live' ORDER BY eventdate ASC
this will return everything when the browser url shows this: http://www.kapsi-western.org/calendar.asp?date= However, if we put a date in there, and made it like this http://www.kapsi-western.org/calendar.asp?date=2/26/2005, then nothing comes up even though there is event with that date.
Does anyone know how to return a date the sql query analyser like (Aug 2, 2004)
Right now, the following statement returns (Aug 2, 2004 8:40PM). This is now good because I need to do a specific date search that doesn't include the time.
This seems really simple so I'm suprised I'm having so much trouble. Perhaps I'm just overthinking the whole issue. I'm trying to write a query that will extract a list of Users from a table that have logged in within the last 90 days. Sounds easy huh? I'm still stumped!Here's the basics of the attempted query:SELECT UserNameFROM UsersWHERE (LastLoggedInDate<= DATEPART(dd, GETDATE() - 90))Thanks!
As Iam a very beginner to this sqlserver arena , Iam getting more and more doubts. Can anyone of you help to display a date as string type which is of format, "date/month/year hour:month:second:millisecond".
Please get rid of the huge command which Iam using now,
select rtrim(cast(datename(day ,getdate()) as char )) + '/'+ rtrim(cast(datename(month ,getdate()) as char )) + '/' + rtrim(cast(datename(year ,getdate()) as char )) + ' ' + rtrim(cast(datename(hour ,getdate()) as char )) + ':' + rtrim(cast(datename(minute ,getdate()) as char )) + ':' + rtrim(cast(datename(second ,getdate()) as char )) + ':'+rtrim(cast(datename(millisecond ,getdate()) as char )) as date
Well you would think it was pretty simple. If I create a Database on Server a last week, take a backup of that database and put it on server b today, the CRDate in sysdatabases still shows the date of last week instead of today. I need to find the actual date for all 150+ servers from sql 7 - sql 2005 in our domain. Any ideas? I had heard that maybe SQL DMO had an object that might be helpfull but I have not been able to look it up anywhere or see how to use it using sp_oa procs.
I want to create a procedure where nextvisitdate is a cloumn for date so when I passed variable @nextvisitdate then which data type I have touse with it.
CREATE PROCEDURE sp_salesman_nextvisitdate
@salesman varchar (50), @nextvisitdate date (50)
As
Select * from Entry Form
WHERE
@salesman = salesman AND @nextvisitdate = nextvisitdate
Hi, I have just started using SQL server 2005 reporting. But I am stuck on my select part of the report. I want to bring back all results from a table for todays date (whatever that would be). I have tried:
Where ColumnA = Today()
and a few other Today variations.
Can anybody please help?
Also is there a good site / tutorial which helps with SQL syntax, statements, etc.
I'm converting an ASP system from using msaccess to SQL Server as the db engine, and I'm stumped on the following query
update timecard set TcdDate = #3/18/05#
TcdDate is defined as a date/time type
It will not run with the date bracketed by # signs, and when I take them out, 1/1/1900 is stored in the dbs. Is there a different symbol to bracket the date with or should I be using a function to convert the date?
I have a field which stores date which the datetime datatype... The problem is that I am also getting time information in this stored field.
I just want to extract the date part in dd-Mmm-yy format (e.g. 07-May-04). I tried convert (char (8), MyDateField, 112) but it gives me date in yyyymmdd format. How do I get the results in dd-Mmm-yy format instead?
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday) 1 1 a b c d e m 1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using where (media.mediaTyple like 'thumbnail' or media.mediaType is null) but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
My goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select Jan 1- 23rd feb 1-23rd march 1-23rd april 1-23rd ,value from table
For this id: 0793319, my beginning date is 2011-09-06
108203492014-09-022015-06-30 208203492013-09-032014-09-01 308203492012-09-042013-09-02 408203492011-12-122012-07-03--not a continuous date range
For this id: 0793319, my beginning date is 2012-09-04
108203492014-09-022015-06-30
For this id: 0820349, my beginning date is 2014-09-02
To find continuous date, you look at the beginning date in row 1 and end date in row 2, then if no break in dates, row 2 beginning date to row 3 end date, if no break continue until last date There could multiple dates up to 12 which I have to check for "no break" in dates, if break, display beginning date of last continuous date.
I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.
Hi,I have a problem with updating a datetime column,When I try to change the Column from VB I get "Incorrect syntax near'942'" returned from [Microsoft][ODBC SQL Server Driver][SQL Server]'942' is the unique key column valueHowever if I update any other column the syntax is fineThe same blanket update query makes the changes no matter what isupdatedThe problem only happens when I set a unique key on the date field inquestionKey is a composite of an ID, and 2 date fieldsIf I allow duplicates in the index it all works perfectlyI am trying to trap 'Duplicate value in index' (which is working onother non-date columns in other tables)This is driving me nutsAny help would be appreciated
So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format
Hi, Basically the above is a very common requirement, please comment on my solution which I've arrived at by searching through the web; -
In summary I have used 3 SSIS components these are "Flat File Source", "Derived Column" and "SQL Server Destination".
1) File Connections Manager Editor 1.1) Within File Connections Manager Editor; - Name the data type e.g. "INTERCHANGE_NET_APP_DATE_SRC" and assign a type to the data type e.g. string[DT_STR]
1.2) Click on the Preview button to ensure the expected text is assigned to the expected data type.
2.4) Select "database timestamp [DT_DBTIMESTAMP] " as Data Type.
2.5) Within the Mappings tab of the SQL Destination Editor have; - Input Column as INTERCHANGE_NET_APP_DATE and Destination Column as INTERCHANGE_NET_APP_DATE.
Please comment on the above, I will then pass on my suggestion to Microsoft.
Hi there. I'm trying to extract data from my SQL server & everything in the script I've got is working (extracting correct data) except for one field - which is for the most part it's off by +2 days (on a few occasions - I see it off by just +1 day or even +3, but it's usually the +2 days).
I'm told that it's due to the conversion formula - but - since SQL is not my native language, I'm at a bit of a loss.
The DB table has the date field stored as a type: CHAR (as opposed to 'DATE') Can anyone out there help?
Hi, I really need this help urgently. I need to send an email when the dueDate(field name in database) is equal to today's date... I have come out with this code with the help of impathan(jimmy i did not use ur code cos i not very sure sry)... below is the code with no error... but it jus wun send email... Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load, Me.Load con1.Open() Dim cmd As New SqlCommand cmd.CommandText = "select * from custTransaction where convert(datetime,dueDate,101) = convert(datetime,GetDate(),101)" 'Set the connect the command object should use cmd.Connection = con1Dim da As New SqlDataAdapter(cmd)Dim ds As New DataSet da.Fill(ds) con1.Close() If Not ds.Tables(0) Is Nothing ThenIf ds.Tables(0).Rows.Count > 0 Then Dim objEmail As MailMessage = New MailMessage objEmail.From = New MailAddress("my@email.com.sg")objEmail.To.Add(New MailAddress("my@email.com.sg")) objEmail.Subject = "Due Date Reaching"objEmail.Body = Session("dueName") objEmail.Priority = MailPriority.Normal Dim SmtpMail As New SmtpClient("servername") SmtpMail.Send(objEmail) End If End If End Sub Note: I am veri sure that database has the data field dueDate with the value 11/16/2007 smalltimedate(mm/dd/yyyy) Realli veri urgent Thanks so much for ur'll help
