Date Conversion And Expressions

Aug 31, 2006

I have a table with about 20,000 records that have a date field, stored as a datetime in the database like '8/28/2006 8:42:14 AM'. The dates range from March 2004 to current. What I would like to do is retrieve the dates in that format (month year) and put them in a dropdown. I have this so far:
SELECT DISTINCT DATEPART(month, dte_date) AS Expr1, DATEPART(yyyy, dte_date) AS Expr2
FROM myTable
ORDER BY DATEPART(yyyy, dte_date), DATEPART(month, dte_date)
And the query returns the information that I want, but I can only bind one field to the dropdown. I was thinking that if I return the results a single expression (concantenate?) then I could bind that to the dropdown. I'm not sure as how to go about this. Also, the month returned is numeric and I would rather have the name of the month returned (like "July" instead of "7").
 Thanks in advance to anyone who helps me.

View 4 Replies


ADVERTISEMENT

Date Literals In Expressions?

Sep 12, 2007



How do I specify a date literal in an expresison? It's not covered in Books Online. None of the following worked:

mydate == '1899-12-30'
mydate == "1899-12-30"
mydate == #1899-12-30#

This did work:

mydate == (DT_DATE) 0

but it's not self-explanatory and it would be utterly stupid if that's the only way to specify a date literal. Are we once again victims of the "rushed-out-the-door" syndrome?

View 10 Replies View Related

Reporting Services :: SSRS Date Expressions

Sep 21, 2015

SSRS expressions for the following queries.

Last year last Week (15/9/2014)
Last Week ( 14/9/2015)
Before Last Week (7/9/2015)

View 2 Replies View Related

Is There Any Easy Way To Evaluate Complex Date Logic In Expressions?

Dec 7, 2006

Hello all,

I am new to SSIS, so I am hoping there is an easier way to do this...

I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is...  In Access, this was written as something like:

 

IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)

 

Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult.  Here is what I came up with:

 (DUE_DATE >= (DT_DATE)( (DT_WSTR,2)MONTH(GETDATE())+"/01/"+ (DT_WSTR,2)YEAR(GETDATE()))) && (DUE_DATE<(DT_DATE)( (DT_WSTR,2)MONTH( DATEADD("m",1,GETDATE()) )+"/01/"+(DT_WSTR,2)YEAR( DATEADD("m",1,GETDATE() )))) ? value1 : value2



 

Any help you all could give would be appreciated.

 

Thanks!

 

Josh

View 7 Replies View Related

Date Function - Conversion Failed When Converting Date And / Or Time From Character String

Mar 18, 2014

I have the following

Column Name : [Converted Date]
Data Type : varchar(50)

When I try and do month around the [Converted Date] I get the following error message

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

My Query is

SELECT
month([Created Date])
FROM [FDMS_PartnerReporting].[Staging].[Salesforce_MarketingReporting]

View 7 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

View 24 Replies View Related

Flat File Text Date Conversion To SQL Server Date Comments And Suggestions

Mar 12, 2008

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) Derived Column Transformation Editor
2.1) Assign Derived Column Name, e.g.
INTERCHANGE_NET_APP_DATE

2.2) Select <add as new column> within Derived Column.

2.3) Enter the conversion Expression, e.g. ; -
2.3.1)
(SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,8,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,5,2) + "/" + SUBSTRING(INTERCHANGE_NET_APP_DATE_SRC,1,4))

2.3.2)
Since the above conversion is such a common task I suggest that Service Pack 3 of SQL Server 2005 delivers the following functionality; -

STRINGTODATE ('YYYYMMDD',INTERCHANGE_NET_APP_DATE_SRC)

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.

Thanks in advance,

Kieran.

View 1 Replies View Related

Transact SQL :: GROUP Dynamically Generated Date And String Expressions

May 17, 2015

I have below SQL. When I run it I get the 'Each GROUP BY expression must contain at least one column that is not an outer reference' error. The date and string expressions are generated dynamically and need to be grouped upon if possible. What am I missing?

INSERT INTO tblStaffPayrollHistory (StaffID, FromDate, ToDate, PayrollNo, EventID)
SELECT DISTINCT tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID
FROM tblStaffBookings INNER JOIN tblEvents ON tblStaffBookings.EventID = tblEvents.ID
WHERE ...
GROUP BY tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID

View 2 Replies View Related

Valid Expressions Are Constants, Constant Expressions, And (in Some Contexts) Variables. Column Names Are Not Permitted.

Dec 11, 2007

I want to have this query insert a bunch of XML but i get this error...


Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117

The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151

The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

What am i doing wrong...Can anyone help me out!! Thanks!!

p.s I know this query looks crazy...


Code Block

IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON

-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1


--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler


--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0


INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler

-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0


INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked


-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0

-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1

go

View 1 Replies View Related

Conversion Of Oracle Date Time To Sql Server Date Time In SSIS

Jun 30, 2007

This is driving me nuts..



I'm trying to extract some data from a table in oracle. The oracle table stores date and time seperately in 2 different columns. I need to merge these two columns and import to sql server database.



I'm struggling with this for a quite a while and I'm not able to get it working.



I tried the oracle query something like this,

SELECT
(TO_CHAR(ASOFDATE,'YYYYMMDD')||' '||TO_CHAR(ASOFTIME,'HH24:MM : SS')||':000') AS ASOFDATE

FROM TBLA

this gives me an output of 20070511 23:06:30:000



the space in MM : SS is intentional here, since without that space it appread as smiley



I'm trying to map this to datetime field in sql server 2005. It keeps failing with this error

The value could not be converted because of a potential loss of data



I'm struck with error for hours now. Any pointers would be helpful.



Thanks

View 3 Replies View Related

Date Conversion

Aug 6, 2005

i do have date problem in sql server, i m using DD/MM/YYYY date format, & passing it to insert & update stat...& compairing it with data in table, which is not working properly, how to convert dd/mm/yyyy to mm/dd/yyyy or yyyy-mm-dd
hoping for solution soon, thanx
murli ......

View 7 Replies View Related

Date Conversion

Sep 21, 2005

I'm searching on a smalldatetime field in SQL Server so a typical value would be 09/21/2005 11:30:00 AM.  I have a search form which offers the user a textbox to search by date and unless they enter the exact date and time, no matching records are found.  Of course I want I all records for a given day to be returned.  This is how I'm doing it now. Thanks.
Dim dteDate_Requested As String = txtDate_Requested.Text
If dteDate_Requested <> "" Then   strSqlText += " Date_Requested='" & dteDate_Requested & "'"End If

View 5 Replies View Related

Conversion To Date

Feb 17, 2006

HI everyne,
I have a varchar field in one table, which contains data in the form '010706' and I want to convert this to date datatype to 01/07/2006 (Jan 07, 2006). When I just import the data to the other table it gets converted to 7/6/2001, how can I convert it right? Please help.

View 2 Replies View Related

Date Conversion

Mar 19, 2001

Hello All,

I need help in converting a date. What i'm looking for is date in format of mm/yyyy.

Thanks in advance.

View 1 Replies View Related

Date Conversion

Nov 27, 2001

I need to import a text file into a table by using DTS.

How to convert a text date to smalldate type ?

Thanks.

View 1 Replies View Related

Date Conversion

Aug 11, 2003

Hi all

I wonder whether any of you can help me with a bit of code that you may have already had to execute??

I have a SQL database logging activities and a load of information in a mdb file that needs to be imported.

Unfortunately the data in the SQL database is in the format yyyy-mm-dd and the data in the mdb file is in dd/mmmm/yyyy.

When i run a DTS to import the data the new rows are imported as they were YYYY-dd-mm.:mad:

example:
data logging as
2003-08-10
2003-08-11

imported data from last week arrives as
2003-01-08
2003-02-08
2003-03-08
etc

how can i manipulate the data in SQL to reverse the day and month numbers for Aug 1st to Aug 8th??

I have tried changing the mdb data format but that doesnt make a difference. I dont understand DTS enough to know whether it is possible there :confused: and my SQL skills dont rise to the challenge - yet!! :o

TIA

View 2 Replies View Related

Date Conversion

Oct 29, 2003

I have a datetime field in a table and I have to insert this datatime data into antoher table. In my insert statement I convert the datetime field into varchar and then insert it into the second table.

The date field in the original table is : 2/2002/13 3:58:12 PM
but in the destination table i get: 2/2002/13 3:58:00 PM

I lose the seconds in the conversion, i think

Whats the best way to preserve to the datetime field during transfer?

thanks

View 3 Replies View Related

Date Conversion Help

Mar 6, 2002

I have one column that is a datetime, and another that is an INT which represents seconds. i cannot figure out how to subtract the seconds from the datetime column. sorry, i'm still kind of new to this TSQL. I get this error:Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

when i try to do this
select dateColumn - IntColumn from Table

so i think there must be a way to make sql know that IntColumn is actually seconds. thanks

View 2 Replies View Related

Date Conversion ?

Jan 19, 2004

Is use this stored procedure.
This is the error mesage: "Syntax error converting datetime from character string"

Please help me !

Alter Procedure "Selectie_Date_Tabel" (@datainceput datetime, @datasfirsit datetime,@Grupa AS nvarchar(20))

As

set nocount on

DECLARE @NEWLINE AS char(1)

SET @NEWLINE = CHAR(10)

DECLARE @keyssql AS varchar(1000)


SET @keyssql = 'SELECT * FROM View2'
+ @NEWLINE + 'WHERE [Cod grupa] = ' + CHAR(39) + @Grupa + CHAR(39)
+ @NEWLINE + 'AND ([Day] BETWEEN ' + CONVERT(DATETIME, @datainceput , 120) + ' AND ' + CONVERT(DATETIME, @datasfirsit , 120) +')'

EXEC (@keyssql)

View 12 Replies View Related

Date Conversion Help

Jan 28, 2004

Hi,

I have my dates in DB2 source in two formats -

Format 1 - char(5) - Example - 10305. 1 indicates century,03 indicates year and 05 indicates month. The day is not stored. So this is 2003,May 1

Format 2 - char(7) - Example - 1030525. 1 indicates century,03 indicates year, 05 indicates month,25 indicates day. 2003,May 25

I want to convert the above two formats to SQL Server smalldatetime and I only need the DATEPART. The date needs to be in the format mm/dd/yyyy. The default day would be 01 when the day is not specified.

If the format is 00305 then the 0 indicated 19th century. So this is 1903, May 1.

Any help is appreciated.


Thanks,

Vivek

View 7 Replies View Related

Date Conversion

Apr 25, 2008

Hi am trying to convert my date from the date times stap to
this format 250408

I've tried this : select convert(varchar,getdate(),112)
but the result am getting is '20080425'

View 3 Replies View Related

Sql Date Conversion

Jun 15, 2006

priya writes "select convert(smalldatetime,'09/06/2006') is working

select convert(smalldatetime,'13/06/2006') it shows an error
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

why it shows an error"

View 1 Replies View Related

Date Conversion

Feb 6, 2008

I have a SQL database where the dates appear in the format "733433".
If you convert in the "Select" statement, it's fine. You can use the day, month, year concatenated in an excel expression, converts fine. As an expression in reporting services, I receive an error. An help is appreciated.

View 1 Replies View Related

Date Conversion

Apr 4, 2006

I need help with date conversion from character data. In SQL 2000 we used a Date Time Conversion task

I do not see how to do this in SQL 2005 SSIS. I tried a data conversion task to a database timestamp and this is what I got:

[Data Conversion [383]] Error: Data conversion failed while converting column "date_time_stamp" (47) to column "Copy of date_time_stamp" (396). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

Here is a sample of the input data I'm trying to convert.

input data example - 2006-03-07-14.42.34

Any ideas? .

View 6 Replies View Related

Date Conversion

Mar 16, 2006

Hi,

My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.

In my source file i got a date column which is in ISO standards ex: 20050131

I have taken source flat file data type as database date [DT_DBDATE] and in

destination table i declared data type as datetime.

When i start debugging i am getting an error saying that data conversion is not possible.

Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.

If, so please tell me how to do.



With Regards



Satish D











View 1 Replies View Related

Trouble With Date Conversion

Jan 8, 2008

I'm trying to insert a date, I don't want the current time. It would be ok if it was 12:00:00. I have tried using the below bold area, but I get an error that the conversion is not correct.
Any ideas?
INSERT INTO tblpayments (rec_ID,client_ID,tranDate,tranAmount,DateEntered,EnteredBy)
SELECT rec_ID,client_ID,Convert(varchar(5), GETDATE(), 10) AS TranDate,Tranamount,DateEntered,EnteredBy

View 1 Replies View Related

Conversion: Time To Date

Apr 11, 2008

I'm setting up a website for a new employer and their existing database.
The table I'm using has a Time field in it that captures the date and time of the record.
I'm trying to tie that field into some label controls and can't seem to figure out how to convert the time to just a date. I tried Format(TimeColumn, "MM/DD/YYYY") which does nothing but put my intended formatting as the label. What do I need to do to convert the time to just a date?

View 5 Replies View Related

Need Help In Sql(Varchar To Date Conversion)

Jan 10, 2002

Hi:
here i am giving one example with Emp table and fields ssn,empjoindate


I have a table with field empjoindate as varchar(10).I need number of employees joined between 12/23/01 and 01/04/02.I ran this query using select ssn from EMP where convert(varchar(10),empjoindate,101) between '12/23/01' and '01/04/02'.it is not fetching any data.

Please help me
Thanks
Tej

View 8 Replies View Related

Date Time Conversion

Aug 2, 2000

Hi,

I just had problem with Date Time conversion. Here is the example:

Select GetDate()

Result: 2000-08-02 23:50:15.280

Then I use Convert function:

Select Convert(DateTime,Cast(GetDate() as varchar), 101)

Result: 2000-08-02 23:50:00.000

What I expected to see is: 08/02/2000 and it was what I used to get.


Can anybody helpe to solve this problem? I am running sql server 7.0



Thanks,


Jim

View 1 Replies View Related

Date Conversion Help Needed

Aug 10, 2000

View 2 Replies View Related

Date Conversion Help Needed

Aug 10, 2000

I have some tables in which we store integer values for the week and year(by using the datepart function on datetimes). My problem arises because I need to let users be able to query this table based on dates they enter. However, I am having no luck in being able to convert these integers back into valid dates. I would like users to be able to enter something like '6/1/2000' as a start date, and '7/1/2000' as an end date. Then the query would pull all weeks in between. No luck, and I'm thoroughly confused. Is this method of storing dates as ints just plain stupid? thanks.
jason

View 1 Replies View Related

BCP Date Conversion Problem

May 22, 2000

I am having a problem bulk copying in a text files date data into a MS SQL Server 7 database.

The text file information comes from our mainframe which uses the date "01/01/0001" to represent a NULL date value. I cannot change the front end process that generates the date. I need to correct the problem before inserting it into the MS SQL Server 7 database.

When the BCP bulk copy program processes this date, it turns it into the date
"01/01/2001".

What I need to do is change the process so that when the date value "01/01/0001" is processed, it changes the value to NULL on the import into the MS SQL Server 7 database date field. Using the approach that I search the database after the BCP has completed will not work because there are true dates that exist that have the value "01/01/2001".

Does anyone have any ideas on the most efficient way I can do this?

View 2 Replies View Related

Text To Date Conversion

Dec 13, 2000

I have a very large table with about 15 "date" fields that were imported as text from a flatfile. I need to convert these fields to dates. The format that they are currently in are like this "12/31/99".

What is the easiest way to convert these fields to dates?

Thanks for any help
Jason Fitch

View 1 Replies View Related







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