Converting Date To Number And Concatenating

Nov 1, 2013

I've converted a date field to a number. I'd then like to concatenate this with another number field.

(CONVERT(bigint,convert(datetime,[Date of Incident])) + ' ' + cast([Incident Number]as nvarchar(4))) as 'lookup'

Using the code above only returns the converted date to number field.

View 2 Replies


ADVERTISEMENT

Converting A Number Into A Date Type Using An Expression

Jul 2, 2007

I am loading data from an iseries into a sql server 2005 DB. Our dates are stored as a numeric value in a format of CYYMMDD where C = Century indicator 20'th is 0 and 21'st is 1, YY = Year, MM = Month and DD = Day!



Today would be 1070701. Now I want to use a derived column which which would be of type date using an expression to do the conversion.



Usually, we would add 19000000 to the number to give us 20070701 then I'd convert it to a string and then substring into a date format.



I'm just getting started with SQL 2005 so I don't know how to do this using an expression.



Any help would be greatly appreciated.



Thanks,

Gray

View 3 Replies View Related

Concatenating Numbers/Converting Dates To Integers

Feb 24, 2004

My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).

So my question is, what is the best way to do this? This is what I have so far:



SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)



However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.

Any suggestions would be greatly apprectiated. Thank you.

View 9 Replies View Related

Converting Timestamp To Varchar Or Concatenating It With A String

Sep 20, 2007

Hello,

I apologise if this question has been asked before but I have searched forums and the web and have not found a solution. I am current creating a script that has a cursor that builds a sql statement to be executed e.g.

--code within cursor

SELECT '
DECLARE @Result INT
EXEC @Result = DELETE_DOCUMENT
@DocumentID = ' + STR(DocumentID) + ',
@TimeStamp =' + CAST([Timestamp] as varchar) + ',

-- CHECK RESULT AND STATUS
-- IF OK LOG IN META_BATCH ELSE LOG ERROR' AS SQL
FROM Document



The problem I am having is trying to join the timestamp column into the sql string. I have tried to cast the time stamp to a varchar but I end up with the following output for the timestamp column values

T
T€‘
T­
xnĂž
T!
T"
T#
T$
T%
T&
T'
T(
T)
T*
T+
T,

instead of


0x0000000013540F1C
0x0000000013540F1E
0x0000000013540F1F
0x0000000013786EDE
0x0000000013540F21
0x0000000013540F22
0x0000000013540F23
0x0000000013540F24
0x0000000013540F25
0x0000000013540F26
0x0000000013540F27
0x0000000013540F28
0x0000000013540F29
0x0000000013540F2A
0x0000000013540F2B
0x0000000013540F2C


which would not allow my delete script to work correctly. So I would really appreciate some advice to a pointer to where I might find out how to convert the timestamp.

Thanks
Sam

View 3 Replies View Related

Concatenating Three Longint Fields Into A Date Field

Apr 20, 2001

Good afternoon one and all,

I have the following problem that I can use some help with :

I have a table in a linked server that has the date stored in three fields (i.e. day,month and year (I have no idea why)). I would like to concatenate these three fields together into a datetime format in a SQL statement

Something like

SELECT ([stc_dd] & '/' & [stc_mm] & '/' & [stc_yy]) AS stkdate

(the above line does not work)

Hope that is clear, thanx in advance for any and all help

Gurmi

View 1 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

Converting This Julian Date 2003182 Into Our Calendar Date

Oct 24, 2003

Hi,
I have julian date in the format of 2003182 and I need to convert into our regular calendar date.
Anybody have any SQL statement for this.
Thanks,
Ravi

View 2 Replies View Related

Converting A Number To Hexadcimal

Feb 25, 2004

Is there any function in SQL-Server which converts a number into its Hexadecimal equivalent..? if not, how should I convert a number to its hexadecimal equivalent..?

Thanks
Jake

View 2 Replies View Related

Converting Number To Text

Jul 20, 2005

Hi,I have two tables. One of them has a text field, and the other has a numeric(integer) field that serves a similar purpose. I want to connect (UNIONactually) the two tables, and store the text from the first table in thesame field as the number from the other.For example:SELECT TextID AS IDFROM W1UNIONSELECT NumericID AS IDFROM W2The above query results in a type mismatch error due to trying to store anInt in a text field. I have tried the SQL CONVERT function, but the docsseem to indicate that it is just for Dates. In any case, it hasn't workedfor me.Any ideas?Thanks!

View 3 Replies View Related

Converting Database Date To Actual Date

Mar 24, 2014

The dB dates are store in db as 20140303 when I bring this to my application to pick a date, the apps shows the date as 20140303 how can I change this to show in the format 03/03/2014 in my application?

View 3 Replies View Related

Converting A Decimal Date Value To Date Field

Feb 4, 2015

I am trying to convert a decimal date value to a date value that I can use in formulas but am having a lot of trouble.My date value currently shows in decimal format YYYYMMDD. I want to convert this to a date so I can then find the number of days between two dates.I have tried convert (datetime, convert(varchar(8),left(qhstdt,8))) with qhstdt as my decimal date field but I receive the error message below:

Error: SQL0204 - CONVERT in *LIBL type *N not found.

I have also tried converting it using (year(QHSTDT)*10000+100*month(QHSTDT)+ day(QHSTDT))) but when I convert the dates using this formula, I can get an incorrect number of days when I try to subtract one from the other.What formula can I use to convert my YYYYMMDD field to a format that will allow me to compare number of days between two dates?

View 2 Replies View Related

Converting A Text Field To Number

Jul 20, 2005

I have a table with over a million rows and one of the fields containsamounts of money in text format.What is the most efficient way of converting this field to a numberformat that I can sum on?Regards,Ciarán

View 3 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

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

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 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

Need To CalcuThe Number Of Days Between The Current Date And A Stored Date

Sep 22, 2006

I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.

View 4 Replies View Related

Converting Date/time To Just Date?

Jul 20, 2005

I have a table that's of type date/time (i.e. 01/01/1900 00:00:00).What I want is to do the following:Say you have these records:person | date-time-------+---------------------------jim | 06/02/2004 00:05:52jim | 06/02/2004 05:06:21jim | 06/02/2004 05:46:21jim | 06/15/2004 11:26:21jim | 06/15/2004 11:35:21dave | 06/04/2004 09:35:21dave | 06/04/2004 11:05:21dave | 06/06/2004 10:34:21dave | 06/08/2004 11:37:21I'd like the results to count how many days and returnperson | days-------+-------jim | 2dave | 3How would I do this?--[ Sugapablo ][ http://www.sugapablo.com <--music ][ http://www.sugapablo.net <--personal ][ Join Bytes! <--jabber IM ]

View 1 Replies View Related

Converting Date

Feb 25, 2008

I have an old table (table1) and a new table (table2).  I need to move some of the data from table1 to table2.
For my example, table1 contains 1 field that is a DateTime, we’ll call it table1_Date.
table2 also contains 1 field that is a SmallDateTime, we’ll call it table2_Date. 
I want to do something like this:
Insert into table2
     table2_Date
Select
     table1_Date
From table1
Where ….. 
I am getting the following error:
The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error. 
How can I go about converting this on the insert?
 

View 9 Replies View Related

Converting Int To Date

Mar 20, 2007

hey guys,
i need help in converting int to date. i've googled all over and most of all recommend using CAST or CONVERT. however, when i tried it to my SQL command, it didn't work. was i using it wrong? please help me figure it out.

here is the command i used:

Code:

select convert(datetime, starttime, 120) as starttime from table



one of the example of starttime contained in the db is 1170349200.

i'm almost desperate.. help please.. thanks in advance..

View 2 Replies View Related

Converting To Date Only

May 9, 2006

I have a function on a MSSQL 2000 db like the following:create function GetDateOnly (@pInputDate datetime)returns datetimeasbeginreturn cast(convert(varchar(10), @pInputDate, 111) as datetime)endwhich returns the date with the time all zeros ( '2006-05-09 00:00:00' ). I tried to implement this same function on an MSSQL 7 server and I get errors. Server: Msg 170, Level 15, State 1, Line 1Line 1: Incorrect syntax near 'function'.How can I code something similar in version 7? I'm assuming it's a version difference that is causing my problems.Thanks,Randy

View 2 Replies View Related

Converting Date

Mar 27, 2007

I am unable to convert following date format in seconds (ss). Plz provide me query for the same.

Date Available :

2007-03-27 09:55:00.000

View 8 Replies View Related

Problem With Converting To Date

Aug 20, 2007

SELECT DISTINCT                       CONVERT(varchar(20), CAST(MONTH(dbo.classgiven.classdate) AS varchar(2)) + '-01-' + CAST(YEAR(dbo.classgiven.classdate) AS varchar(4)), 1) AS ok,                       dbo.classT.discountFROM         dbo.classT INNER JOIN                      dbo.classgiven ON dbo.classT.classcode = dbo.classgiven.classcodeWHERE     (dbo.classT.coned IS NOT NULL) AND (dbo.classT.discount = '-1')
 
why wont ok come out as a date and sort as a date?
i looks like a date but sql server will not sort OK in order. my god please help

View 1 Replies View Related

Converting Date Formats

Apr 10, 2000

Hi,

I need to convert the output of a query

From:
Sep 13 1999 12:00AM

To:

1999-09-13 00:00:00.000

I need it to be in 7.0 format)

Thanks.

View 1 Replies View Related

HELP HELP Converting Date Time

May 30, 2002

I have a table that has a nvarchar field of (12) I need to convert this to a smalldatetimefield.

I get the following message

Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Insert error, column 1 ('timeid', DBTYPE_DBTIMESTAMP), status 6: Data overflow.
Error Help File:sqldts80.hlp
Error Help Context ID:30702


Can someone please tell me how to convert this field without getting this message?????

Thanks,
Dianne

View 4 Replies View Related

Converting Text To Date

Feb 6, 2006

I have a field that stores a date as text (121205). I need to convert this field to a date, but since it is text, I cannot figure out how to do it. Any ideas??? Thanks! :D

View 5 Replies View Related

Converting Date To Other Format?

Jun 24, 2013

I am using SQL server 2008 R2. I have a query where I have dates in different columns pulled from my database among other things. The client is requiring that the date be formatted as mmddyyyy. The database returns the date value as varchar (8) and formatted as yyyymmdd. I have tried several things to get this to be converted but haven't had any luck. My query is as follows.

select appointments.last_name as 'patient last name', appointments.first_name as 'patient first name', appointments.address_line_1, appointments.address_line_2, appointments.city,
appointments.state, appointments.zip, appointments.home_phone, appointments.appt_nbr, appointments.appt_date, person.date_of_birth,
person.sex, patient.med_rec_nbr, provider_mstr.national_provider_id, provider_mstr.first_name,

[code]....

View 7 Replies View Related

Need Help Converting Date Format.

Jan 23, 2004

Hi and thanx for reading my post..

I have a reg_date field in my MSSQL DB which is formatted like this :

dd.mm.yy tt:mm:ss (eks. 24.12.03 18:00:03)

What i want to do is get the 8 first chars from this string so i end up with only : 24.12.03

Have tried different variations of : convert(char(8) but not sure how i do this really..

Have already searched the net for a solution but had to post it since i didn't find anything useful..

Hope someone can help me out

Best regards
Mirador-/

View 5 Replies View Related

Converting Varchar Into Date

Aug 14, 2007

Hi
I have a table named prodwin in which i have to update a column name mfgdt which is varchar(10) to 7 months which I am able to do but the problem is that since it is only 10 character it is cutting the end of the year for eg

Before the date was
5/22/2000

UPDATE PRODWIN_MM SET MFGDT = DATEADD(MM,7,MFGDT)

Now it is this

Dec 23 200

Please tell me what to do , should i use cast or convert ?

Thanks in Adv

View 7 Replies View Related

Converting Date To Varchar

Jan 22, 2008

Hi all I would like anyone to help I want 2 convert a datetime to 2008-02-02 21:00 the output must leave the time just 2008-02-02


If (@DateCreated <> 0)
Begin
Set @varSelectSql = @varSelectSql + ' And convert(varchar(10),DateCreated,121) = '+ cast(@DateCreated as varchar(10))
End

View 4 Replies View Related

Converting String To Date

Aug 12, 2005

Hello,I try to convert a pseudo datetime string into a date. In Oracle I can doto_date( MyDate, 'yyyymmddhh24miss' ); how I can do this with MS SQL ?thanks and regardsMark

View 2 Replies View Related

Error Converting Date

Jan 31, 2008

Good Morning Forum

First time poster here, but I have gleaned much needed support using the forum in the past, so
many thanks to all.

Well, on to business.

I have a strange problem with a T-SQL stored procedure my company uses for reporting.

Around the Christmas holiday period, it just stopped working.

I managed to track the error down to the code below:

(Just the code to recreate the error.)

declare

@g datetime,

@g2 datetime

select @g2=getdate()

select @g=convert(datetime,convert(varchar(10),@g2,101))

select @g,@g2 , SERVERPROPERTY('PRODUCTVERSION'), SERVERPROPERTY ('PRODUCTLEVEL'), SERVERPROPERTY ('EDITION')

When this is run, I get this error message:

Msg 242, Level 16, State 3, Line 6

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Here is the About information from .
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.1433
Operating System 5.1.2600


I know this is not the best way to use datetime or conversions, but I did not write it, I am just supporting it.

The strange thing is, the above code has worked every week since January 2006?

The even stranger thing is, it runs on perfectly well on Server 2000 and on the version below that has not had
any recent service packs installed.

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.086.1830.00 (srv03_sp1_rtm.050324-1447)
Microsoft MSXML 2.6 3.0 6.0
Microsoft Internet Explorer 6.0.3790.1830
Microsoft .NET Framework 2.0.50727.42
Operating System 5.2.3790


Is this a bug, or have I lost the plot?

Thanks in advance for any feedback.

P.S: The style code 101, if changed to 103 allows the code to work. But, when apllied to the pre-RTM server, is
returns the same error message.

View 7 Replies View Related

COnverting Intiger Value Into Date

Apr 29, 2008

i want to display a column of my table which is interger into date part. i seems to get error. i know how to cast the individual integer part into date and time.

any syntax help.

View 8 Replies View Related

Error Converting Date Time

Apr 26, 2004

Hi i m tring to convert a date time

declare @a datetime
declare @b varchar(10)
set @b='26/04/2004'
set @a= Convert(datetime, @b)

but it gives me this error:

Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


I believe is my date format..i want to know how to make sure that i am the dd/mm/yyyy format is correct way?

View 3 Replies View Related







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