Can I Easily SELECT A Date As October 12, 2012 (instead Of Oct 12 2012)?

Jan 29, 2008

 Presently using CONVERT(VARCHAR(11), [ExpiryDate], 100) to get close to the correct format.
Only stumbled across this by accident so am assuming there might be better hidden 'treasures' .
Formatting dates seems to be my biggest time-consuming activity and I just don't seem to get better at it!
 

View 4 Replies


ADVERTISEMENT

SQL 2012 :: Use Date Trunc Or Date Function To Select Date Range For Month On Month View

Jul 29, 2015

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

View 9 Replies View Related

SQL 2012 :: Select Statement For Date Formatting

Apr 6, 2015

Select '"' + CAST( GETDATE() as varchar(100) ) + '"' as Obs_dt_1

I get this output---> "Apr 6 2015 4:07PM"

But what I really need is for it to show in this format---> "2015-04-06 16:08:05.317" .... How do I do the select ?

View 3 Replies View Related

SQL 2012 :: Select Record With Most Recent Order Date?

Jun 16, 2014

I am working on a query that needs to return the record order number with the most recent requested delivery date.

It seems to work most of the time, but I have found some glitches for some of the items.

example is my item 10702, it is showing 2 records (both valid ones)
Record 1 is order # 10450-0, requested delivery date 03/21/2014
Record 2 is order # 10510-0, requested delivery date 04/29/2014

I need to only get the records with the most recent delivery date, in this example that would be 04/29/2014

This query is what I have so far:

SELECT
s.PriorQuoteNumber
,s.PriorItemNumber
,s.PriorQuoteDate
FROM
(
SELECT
s.SalesQuoteNumberAS 'PriorQuoteNumber'

[code]....

WHERE s.rn = 1What am I missing in my query> how can I change it so it only returns the most recent date?

View 9 Replies View Related

SQL Server 2012 :: Select Row Having Latest Backup Date

Nov 29, 2014

I have a select query that results all the dbs and their several backup dates×. Howover I only need the latest backup dates for each of the db.How can I write that in tsql?

View 5 Replies View Related

SQL 2012 :: IsDate In SELECT To Validate / Correct Date

Dec 6, 2014

I am importing data from a legacy system. The date column is a varchar and in the destination DB it is a proper DateTime.

That said I have invalid data and need to clean / accommodate for it as I go. What I need is if the date is valid then insert it, if not then ue a default of '01/01/1900' I am doing the following and it is not working.

CASE
WHEN isDATE(RatifiedDate) = 1 THEN RatifiedDate
WHEN isDate(RatifiedDate) = 0 THEN '01/01/1900'
END as RatDate

<hr noshade size='1' width='250' color='#BBC8E5'>

View 6 Replies View Related

SQL Server 2012 :: Using Select Query To Get 2014-08-09 11:13:03 From Original Date Records?

Aug 18, 2014

I have date field in table and data contain 2014-08-09 11:13:03.340

when I use smalldatefield I am getting 2014-08-09 11:13:00 I should have got 2014-08-09 11:13:03

Why the 03 is trimming in smalldatefield.

how do I use select query to get 2014-08-09 11:13:03 from original Date records

View 1 Replies View Related

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

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.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

SQL Server 2012 :: Calculate Number Of Days Based On Computer System Date And Due Date Row

Mar 18, 2014

I have a query to run a report where the results has a column named “Due Date” which holds a date value based on the project submission date.Now, I need to add 4 columns named, “45 Days Expectant”, “30 Days Overdue”, “60 Days Overdue” and “90 Days Overdue”.I need to do a calculation based on the “Due Date” and “System (I mean default computer date) Date” that if “System Date” is 45 days+ to “Due Date” than put “Yes” in “45 Days Expectant” row.

Also, if “Due Date” is less than or equal to system date by 30 days, put “Yes” in “30 Days Overdue” and same for the 60 and 90 days.how to write this Case Statement? I have some answers how to do it in SSRS (Report Designer) but I want to get the results using T-SQl.

View 2 Replies View Related

SQL Server 2012 :: Script To Specify Date Field To Use For Activity Date

Jun 2, 2014

I'm writing a view to check record counts in a table that has numerous datasets and therefore various "Activity Dates". Is it possible as part of the SQL statement to have a CASE statement for example so that it can identify the field to use as the activity date?

The field to use is being identified using a seperate table so at the moment I have CASE WHEN FieldToUse = '2' THEN MapCol ELSE '[Activity_Date]' END, where FieldToUse = '2' identifies the date field to use and the MapCol data is the field name to be used as the activity date.

Is this even possible?

View 3 Replies View Related

SQL 2012 :: UK Date In Text Field As True Date

Jul 14, 2014

I have a date held in a varchar field in a temporary sql table and I want to convert it into a sql date and it doesn't work. I can replicate this as below -

So I run

select
cast ('14/02/2014' as date)

and I get a conversion failed error.

View 2 Replies View Related

SQL Server 2012 :: Date Between In Procedure

Dec 19, 2013

I need procedure query for two date between fromdate and todate

alter procdure date
declare @Fromdate varchar(10)
declare @todate varchar(10)
as begin
select * from master
where convert(varchar(10),@Fromdate,121) between CONVERT(varchar(10),@Todate,121) and CONVERT(varchar(10),rr.Original_Date,121)
end

View 3 Replies View Related

SQL 2012 :: Extracting Week-day Name From Date?

Mar 7, 2014

I want to extract day of the week from given date.

If the given date is 07-03-2014(dd-mm-yyyy).IF i run the query the output should be =Friday

View 2 Replies View Related

SQL 2012 :: Convert Date To Float

Apr 2, 2014

What actually happens when I convert date to float? Select convert(float,getdate(),112)

View 1 Replies View Related

SQL Server 2012 :: Add Month To A Date

Jun 3, 2014

I am trying to add month to a date. Here is my code

declare @CollectionDate date='10-28-2014'
select @CollectionDate
;WITH CTemp AS (
SELECT TransactionDate=CAST(@CollectionDate AS DATE) ,RemainingTransaction=1
UNION all

[Code] ....

It is working fine. But when I am giving date '10-30-2014' it shows me the error

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

I can understand the problem that it is for the month of February. But How do I overcome the situation?

View 3 Replies View Related

SQL 2012 :: Using Wildcard With Date Column

Jul 28, 2015

I was looking for a way to use a wild card on a date column, but could only convert the file first then use the wildcard Is there another way of conducting the wild card search on a date column without conducting a conversion on the specified column? Sybase has the ability to use a wildcard on the datetime column so I would assume SQL SERVER does too.... Right? I can see that there are some workaround to get the information I need . I have conducted multiple searches and I still cannot find a suitable answer. Anyway, below is some links where I received some of the information:

[URL] ....

View 9 Replies View Related

SQL 2012 :: How To Get Last Run Date On All Databases On One Server

Oct 23, 2015

I know how to get the last run date on all the databases on one serveri have a lot of servers and i was wanting a way of getting the last run date across all servers.

View 3 Replies View Related

SQL 2012 :: SET CMD Value Based On Select Name

Oct 21, 2015

I am trying to move files to directories based on the file status. If the file contains the Flag='E' then I want to move it to an "Error" folder, otherwise it will go to a "Processed" folder.

Here is my current code:

DECLARE @cmd varchar(500)
SELECT
CASE
WHEN Processed_Flag = 'E'
THEN
SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesError'
ELSE
SET @cmd='MOVE /Y C: empmyfiles*.dat C: empmyfilesProcessed'
END
FROM#TEMP

PRINT @Cmd

View 2 Replies View Related

Integration Services :: 2012 Data Tools Crashing On Windows 2012 R2?

May 26, 2015

SQL Server 2012 Data Tools was working fine for me but something must've changed, now every time I try to create a new SSIS project I get:

The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).

When I try to open an existing project I get:

exception has been thrown by the target of an invocation

external component has thrown an exception (SSISUpgrade)

The issue seems to only arise with SSIS projects.I have already uninstalled SQL Server 2012 and reinstalled it and that didn't work.I tried to install Visual Studio 2012 Data Tools with BI and that also crashes when I try to create an SSIS project.Output of SQL Server SELECT @@VERSION is:

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
    Feb 10 2012 19:39:15
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

SQL Data Tools page info:

Microsoft SQL Server Integration Services Designer
Version 11.0.2100.60
Microsoft Visual Studio 2010
Version 10.0.40219.1 SP1Rel
Microsoft .NET Framework
Version 4.5.51641 SP1Rel

View 5 Replies View Related

SQL 2012 :: SSMS 2012 Database Connection Dialog Hangs

Feb 13, 2015

I have several users with an unusual problem with SSMS 2012. When they attempt to connect to a database using the "Connect to Server" dialog box, the connection just hangs. Sometimes after about 15 minutes the connection will be successful. Other attempts simply spin seemingly endlessly. Users experiencing this issue are both running SSMS 2012 on Win 7 Pro (64 bit). The following troubleshooting steps have been tried:

1. When the user runs SSMS "As Administrator" the connections work almost instantly. (Elevating privileges is not a solution in our environment)
2. Wireshark shows that SSMS does not try to hit the SQL server when the user experiencing this issue clicks connect.
3. I can create a new virgin user on the PC and that login experiences the same problem.
4. A complete rip and re-install of SSMS 2012 does not resolve the issue.

View 3 Replies View Related

SQL 2012 :: Table Property For Retention Date?

Feb 21, 2014

I want to store data warehouse source tables and files in an Archive schema and then delete / drop them after a specified period of time.

Is there a table property that I can set (can't find one) or some other mechanism so that I can easily identify these tables with a script.

If there is no such property or feature within the database engine I will define a metadata table and record it there, but a property or similar that I can set at archive time would be very handy.

View 0 Replies View Related

SQL Server 2012 :: Format Output As Date

Feb 28, 2014

I have the following code and the result set is coming out as nvarchar. So, when I create a report in SSRS, it is not formatting as a date.

, CASE
WHEN isnull(cv2.Accepted,0)='True' AND cv2.Visit ='V2' AND cv2.StepNo='3' THEN 'Y'
ELSE CONVERT(varchar,[dbo].[fn_Get_WorkingDays] (co.PlannedGoLiveDt, -10))
END AS 'System Verified'

View 3 Replies View Related

SQL 2012 :: Find All Index And Creation Date

Mar 27, 2014

I need to find all the index and the creation date. I did cross apply of sys.objects & Sys.indexes on name column. I am getting some but the team is saying they created so many. Any other option to find Indexes and their creation date?

View 2 Replies View Related

SQL 2012 :: Find All Rows WHERE DATETIME Within DATE

May 19, 2014

What would be the most economy solution for this WHERE, I see the code where we have covnert to 101 type on both sides of equation, which I tnink is not right.

So I'm thinking to put it on the left like this, just curiouse is this the best solution, I also heard that TSQL interpret between even better , here I'm really care abour performance.

declare @DATE DATE = '01/14/2014'
--A:
SELECT * FROM TT
WHERE CAST( TT.DATETIME AS DATE) = @DATE

--B:
SELECT * FROM TT
WHERE TT.DATETIME >= @DATE and TT.DATETIME < DATEADD(D,1,@DATE)

View 3 Replies View Related

SQL 2012 :: Remove Date Constraint From A Query

Jun 13, 2014

I am trying to remove the dates from a query. my goal is to load it in ssas and add a time dimension. Right now i have to change the dates evrytime i run reports (monthly). Here is the query

drop table #tmptmp

SELECT *, (DATEDIFF(day, enrollmentsDate, ShipmentDate))
- ((DATEDIFF(WEEK, enrollmentsenttDate, InitialShipmentDate) * 2)
+(CASE WHEN DATENAME(DW, enrollmentsentDate) = 'Sunday' THEN 1 ELSE 0 END)
+(CASE WHEN DATENAME(DW, ShipmentDate) = 'Saturday' THEN 1 ELSE 0 END)

[Code] .....

View 1 Replies View Related

SQL Server 2012 :: Modifying Date Range

Jun 27, 2014

I would like changing the date range to be -1 year from today until today

This is the current WHERE, AND

WHERE [entry].[Posting Date] >= DATEADD(YEAR, YEAR(GETDATE()) - 1900, 0)
AND [entry].[Posting Date] < DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)

View 2 Replies View Related

SQL Server 2012 :: Convert Integer To Date

Oct 16, 2014

In VBA, CLng(Now) will return the integer portion of a date CLng(Now) returns 41928, CDate(41928) then returns 10/16/2014. Is there something equivalent in SQL Server that will allow me to convert an integer value to a date?

In short, how can I convert a 100 year date to Gregorian (any format)?

View 3 Replies View Related

SQL Server 2012 :: Serial Date Conversion

Nov 4, 2014

I am in need of converting serial date to regular date ie...735510.40461 and only need the hours, minutes and seconds, I have used the examples I've seen on different forums,

DATEADD(d, -1, DATEADD(m, DATEDIFF(m, '1900-1-1', getdate()) + 1, '1900-1-1'))

View 9 Replies View Related

SQL 2012 :: Convert Datetime Into Date In SSIS

Dec 7, 2014

I have define two variable of Datetime type @Sdate and @Edate.

1. @Sdate = DATEADD ("DD", -5, GETDATE())
2. @Edate = GETDATE()
3. Using Forloopcontainer for pulling the data into batches
( @sdate = dateadd ( "HH" , 1, @sdate))

Now since i am using getdate() to define @Sdate. my variable gets data as ( 2014/12/08 11:43:00AM)

Converting GETDATE( 2014/12/08 11:43:00AM) to only date 2014/12/08 00:00:00AM.

I tired using DT_WSTR which works fine (converting datetime int String).

Problem occurs when i am going through For Loop container . Since i have used assignment of @Sdate is says its not allowed.

View 9 Replies View Related

SQL 2012 :: Checking If A Date Exists In Particular Daterange

Jan 6, 2015

I have a table called as Events and below are its columns

ID int
EventFromDate datetime
EventToDate datetime
EventDesc nvarchar
IsHoliday bit

This is a master table where the admin would enter the Events/Holidays for the entire year.

The data would be as below:

IDEventFromdateEventTodateEventDesc isHoliday
126-01-201526-01-2015RepublicdayYes
230-01-201531-01-2015TeamOutingNo
301-05-201501-05-2015Labour day Yes

Now, suppose a employee applies leave on 26/01/2015 to 26/01/2015 then it should not insert into table and return a value "Not updated"

How to handle the scenario if a employee applies leave between the range 23/01/2015 to 27/01/2015, since 26/01/2015 is a holiday in between. how the data can still be inserted excluding 26/01/2015

Can we exclude a non-working day or a sunday.

Leavedetails table to insert leaves applied by employee is as follows

LeaveDetailID int
LeaveTypeId int
FromDate datetime
EndDate datetime
Remarks nvarchar

View 0 Replies View Related

SQL Server 2012 :: DATE As Dynamic Table Name

Jan 13, 2015

I am trying to select data from table that have YYMM as table names, they are formatted table1410,table1411, table1412. I am trying to format it like this

declare @tablename60 varchar(50) = 'table' + SUBSTRING(CAST(DATEPART(YY,dateadd(yy, -1, getdate())) as varchar(4)),3,4) + SUBSTRING(CAST(DATEPART(MM,dateadd(mm, -1, getdate())) as varchar(2)),1,2)

But this is hard coding the YYMM, and I would like to have it pull 30,60,90 days fromthe first of the current month. I am having a bit of trouble formatting, how to accomplish this.

View 1 Replies View Related

SQL 2012 :: How To Calculate Date Difference In Months

Jan 23, 2015

I would like to calculate difference between end_date and current date in Months.And also how we can calculate the sum of difference in months between start_date and end_date for each ID?

CREATE TABLE datedifference (
id INT
,start_date INT
,end_date INT
)
INSERT INTO datedifference VALUES (10,20091202,20100629)
INSERT INTO datedifference VALUES (20,20071202,20090330)
INSERT INTO datedifference VALUES (30,20051202,20101031)

View 6 Replies View Related

SQL Server 2012 :: Date Range Calculation

Feb 3, 2015

I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.

E.g.
Account 1 stayed in USA for 31 days in January, and 15 days in February.
Account 1 stayed in UK for 13 days in February and 26 days in March.
Etc.

create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime)
insert into #temp select 1,'USA','2014-01-01','2014-02-15'
insert into #temp select 1,'UK','2014-02-16','2014-03-26'
insert into #temp select 1,'AU','2014-03-27','2014-06-07'
insert into #temp select 2,'UK','2014-08-15','2014-09-01'
insert into #temp select 2,'AU','2014-09-02','2014-10-17'
select * from #temp
drop table #temp

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved