convert adDBtimeStamp to datetime

Hello,
I use a SQL Server with ODBC driver and >NET C#.
I want to convert a date field with adDBtimeStamp format to a DATETIME
format, but no solution could be found till now.
My query is as follows:
SELECT received_at as EXPR1
FROM TTable
WHERE datepart(w(datetime(received_at)))>10
Has anybody any solution for conversion?
*** Sent via Developersdex http://www.examnotes.net ***What is the SQL datatype of "received_at" and what are you attepting to
do with it? adDBtimeStamp maps to a DATETIME type in SQL Server so no
conversion should be necessary. However your example code isn't valid
in SQL Server - there is no WEEK or DATETIME function and your syntax
for DATEPART is wrong.
Maybe the following was what you intended, assuming you are in fact
dealing with a DATETIME column:
SELECT received_at AS expr1
FROM TTable
WHERE DATEPART(WEEK,received_at)>10 ;
David Portas
SQL Server MVP
--|||
It seems that no conversion is made and no explicit conversion could be
applied.
for the sequence:
SELECT received_at AS expr1
FROM TTable
WHERE DATEPART(WEEK,received_at)>10
the outcome is:
Driver]Expected lexical element not found: )
*** Sent via Developersdex http://www.examnotes.net ***|||Lucian,
If the adDBtimeStamp values are seen as strings of the
form 'yyyymmddhhmmss', try this:
declare @.t table (
adDBtimeStamp char(14)
)
insert into @.t values ('20051012171534')
select
convert(datetime,
substring(adDBtimeStamp,1,8) + space(1) +
substring(adDBtimeStamp,9,2) + ':' +
substring(adDBtimeStamp,11,2) + ':' +
substring(adDBtimeStamp,12,2),
112) as SQLdt
from @.t
Steve Kass
Drew University
Lucian Baltes wrote:

>Hello,
>I use a SQL Server with ODBC driver and >NET C#.
>I want to convert a date field with adDBtimeStamp format to a DATETIME
>format, but no solution could be found till now.
>My query is as follows:
>SELECT received_at as EXPR1
>FROM TTable
>WHERE datepart(w(datetime(received_at)))>10
>Has anybody any solution for conversion?
>
>
>*** Sent via Developersdex http://www.examnotes.net ***
>

CONVERT a text column to date format

I have a column nvarchar(8) that I need to update to a date format, MM/DD/YYYY

Some of the values have 7 characters. the rest have 8 characters as shown below:

Col1

6051998

12061999

In both rows the format is M/DD/YYYY and MM/DD/YYYY respectively. I have tried using CONVERT and CAST but receive the error:

Conversion failed when converting datetime from character string.

I've manage to generate the correct format in a select statement using CASE:

SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4)))

END

FROM Table1

How can perform an update of this column using the UPDATE statement? I've tried the following with no success:

UPDATE dbo.Table1

SET date_updted =(SELECT date_updted =

CASE

WHEN(selectLEN(date_updted))= 7 THEN(SELECTLEFT((RIGHT(date_updted, 7)), 1)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

ELSE(SELECTLEFT((RIGHT(date_updted, 8)), 2)+'/'+(SELECTLEFT((RIGHT(date_updted, 6)), 2))+'/'+(selectRIGHT(date_updted, 4))As date_updted)

END

FROM Table1)

FROM Table1

If your data is as strongly formated as you say, you should be able to convert to datetime with something like:

Code Snippet

select aDate,
convert(datetime, right(aDate, 4) + left( right('0'+aDate, 8), 4))
as convertedDT
from ( select '6051998' as aDate union all
SELECT '12061999'
) a

/*
aDate convertedDT
--
6051998 1998-06-05 00:00:00.000
12061999 1999-12-06 00:00:00.000
*/

|||

Try something like this:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
DateCol varchar(8)
)

INSERT INTO @.MyTable VALUES ( '6051998' )
INSERT INTO @.MyTable VALUES ( '12061999' )

SELECT convert( datetime, ( stuff( stuff( right( '0' + DateCol, 8 ), 3, 0, '/' ), 6, 0, '/' )), 101 )
FROM @.MyTable

-
1998-06-05 00:00:00.000
1999-12-06 00:00:00.000

|||

Thanks for your responses and I'm sure these methods will work fine, however, my question really was how to use the UPDATE Statement to update the column in my initial post without creating a temp table or column. Maybe I missed something in your responses?

|||

Copy the expression from those select statement...

Code Snippet

UPDATE

dbo.Table1

SET

date_updted =convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4))

FROM

Table1

|||

Maybe:

Code Snippet

UPDATE dbo.Table1
SET date_updted
= convert(varchar(10),convert(datetime, right(date_updted, 4) + left( right('0'+date_updted, 8), 4)),101)

|||Very Nice! Thanks.

convert a gregorian date in mm/dd/yyyy

hello,
I want to convert a gregorian date in mm/dd/yyyy format in MS SQL 2000.
:)Are you lookig to convert from one of the Gregorian calendars to the Julian calendar, or just present an existing DATETIME value in the format you specified? The conversion is an interesting problem, the presentation issue ought to be solved on the client rather than the server, although you can make yourself a LOT of trouble later on by just using:SELECT Convert(VARCHAR(30), GetDate(), 101)-PatPsqlsql

Convert a Date to a Day of the Week.

Hey, Im pretty sure this is possible, but let me know if it isnt.

I have a table of dates in the format "DD/MM/YYYY", is there a way of converting it to output the day of the week? for example 16/11/2007 would display Friday insted.

Thanks in advance John

selectdatepart(dw,GETDATE())

selectdatename(dw,GETDATE())

|||

Hi u can check out this

select datepart(dw,date_created) from users

select datename(dw,date_created) from users

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

Thanks to the both of you, both methods worked amazingly faultlessly. Thanks John

convert a date stored as a string into a datetime

Hello forum,

Is it possible to convert a date stored as a string into a datetime with integration services 2005? My attempts with the “data conversion” fail. The string type form of the date is ‘yyyy-mm-dd’ and the desired result for use in a Union All is ‘dd/mm/yyyy 12:00:00AM.’This outcome is needs so that match on the date can populate a fact table, as the results are coming from two different databases.

All advice/help welcomed.

Ian

Use the Derived Column transform, and add this expression:

Code Snippet

(DT_DATE)((SUBSTRING(StringDate,6,2) + "-" + SUBSTRING(StringDate,9,2) + "-" + SUBSTRING(StringDate,1,4)))

Tip: Because there is no domain integrity inherent in the string date format, be certain to include an error output on your Derived Column transform.

|||

Use a dervide column with substring to re-order the date format; at the end cast it as date:

Code Snippet

(DT_DATE)(SUBSTRING(StrDAte,9,2) + "/" + SUBSTRING(StrDAte,6,2) + "/" + SUBSTRING(StrDAte,1,4))

|||Since this is a common topic today, I blogged on it, with a little more detail than what is posted here: http://bi-polar23.blogspot.com/2007/05/having-trouble-getting-date.htmlsqlsql

Convert a date function in SQL Server

I have the date in the SQL server column as 2002-06-16 00:00:00.000 and I will be needing this to convert to 2003-06-16 00:00:00.000. Just the year from 2002 to 2003. I used an update statement like
UPDATE dbo.tableA
SET Datepart (yyyy,TranDt) = Datepart (2003,TranDt)

This did not work - is there anything I need to change in the update statement to make this query do what I want to -

Thanks!run this for an example
select DATEADD(yyyy, 1, getdate())

update x
set x.timefield = DATEADD(yyyy, 1, x.timefield)
from table x|||mkkmg,
Thanks a million it worked perfect!

convert / group by date

Hi,
I have a datetime column named dtDateTime.
its format is "Oct 27 2006 12:00:00 "
I want to group by only date part of it and count

my code is

$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))

FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId

WHERE (J1708Data.iPidId = 303) AND
(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescription = $VehicleID)

GROUP BY convert(varchar,J1708Data.dtDateTime,120)";

However, convert part, group by part doesnt' work at all.
(i couldn't check count part)

can you find where's the problem?
Thx.kirke wrote:

Quote:

Originally Posted by

Hi,
I have a datetime column named dtDateTime.
its format is "Oct 27 2006 12:00:00 "
I want to group by only date part of it and count
>
my code is
>
>
$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
>
FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
WHERE (J1708Data.iPidId = 303) AND
(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
AND (Vehicle.sDescription = $VehicleID)
>
GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
However, convert part, group by part doesnt' work at all.
(i couldn't check count part)
>
can you find where's the problem?

--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

I'd have done it like this (use VARCHAR(10) or CHAR(10) for the date
instead of an unspecified size):

SELECT CONVERT(VARCHAR(10),J.dtDateTime,120) As theDate,
COUNT(*) As theCount
FROM Vehicle As V INNER JOIN J1708Data As J
ON V.iID = J.iVehicleId
WHERE J.iPidId = 303
AND J.dtDateTime BETWEEN '2006-10-25' AND '2006-10-28 23:23:59'
AND V.sDescription = @.VehicleID
GROUP BY CONVERT(VARCHAR(10),J.dtDateTime,120)
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRUrBwIechKqOuFEgEQKUTQCg1zGcAeAViDrJQWxENdcn2t xbhxYAoO4o
1Mks6W+FiXviMMrZi/lt4e3z
=vWR9
--END PGP SIGNATURE--|||On 2 Nov 2006 10:53:08 -0800, kirke wrote:

Quote:

Originally Posted by

>Hi,
>I have a datetime column named dtDateTime.
>its format is "Oct 27 2006 12:00:00 "
>I want to group by only date part of it and count
>
>my code is
>
>
>$sql1="SELECT convert(varchar,J1708Data.dtDateTime,120),
>count(convert(varchar,J1708Data.dtDateTime,120))
>
>FROM Vehicle INNER JOIN J1708Data ON Vehicle.iID = J1708Data.iVehicleId
>
>WHERE (J1708Data.iPidId = 303) AND
>(J1708Date.dtDateTime between '2006-10-25' AND '2006-10-28')
>AND (Vehicle.sDescription = $VehicleID)
>
>GROUP BY convert(varchar,J1708Data.dtDateTime,120)";
>
>
>However, convert part, group by part doesnt' work at all.
>(i couldn't check count part)
>
>can you find where's the problem?
>Thx.

Hi kirke,

Have you tried to run the query? If so, what were the results? Were they
incoorrect, or did you get an error message. If the latter, then what
was that message?

I don't see any real problems with your data, thoough I would change a
few things:

* The date format. yyyy-mm-dd is not safe, becuase it can be interpreted
as yyyy-dd-mm for some country settings. Remve the dashes to get the
unambiguous yyyymmdd format.

* The use of BETWEEN means that rows with a startdate of 28th oct 2006
at exactly midnight will be included, but startdates on the same day
with a later time are excluded. The solution MGFoster proposes for this
(to include a time portion of 23:59:59) is not good enough - for
smalldatetime, this will be rounded up to the next minute, which is
midnight of the 29th of october; for datetime, you'll still miss rows
with a startdate in the last second of the day. You should replace
BETWEEN with a >= and a < condition:
AND J1708Date.dtDateTime >= '20061025'
AND J1708Date.dtDateTime < '20061029' -- Note the increased end day!
If you store all dates with the default time component of midnight, then
this is not necessary - but since it doesn't hurt either, I'd advice you
to accustom yourself to always using this techniques when comparing
datetimes.

The expression GROUP BY convert(varchar,J1708Data.dtDateTime,120) won't
group by daym, since the conversion doesn't chop off the time portion.
The result of select convert(varchar, current_timestamp, 120) for
instance is "2006-11-03 23:18:22", so you end up grouping by second.

Here's what I would try:

SELECT convert(varchar,J1708Data.dtDateTime,120),
count(convert(varchar,J1708Data.dtDateTime,120))
SELECT DATEADD(day, DATEDIFF(day, 0, d.DateTime), 0) AS TheDate,
COUNT(*) AS TheCount
FROM Vehicle AS v
INNER JOIN J1708Data AS d
ON v.VehicleID = d.VehicleId
WHERE d.PidId = 303
AND d.DateTime >= '20061025'
AND d.DateTime < '20061029'
AND v.Description = $VehicleID
GROUP BY DATEDIFF(day, 0, d.DateTime);

--
Hugo Kornelis, SQL Server MVPsqlsql

Convert

I am using the following snippet of code to help me convert the date and tim
e
in a query I am writing.
SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
dbo.Subjects.Subject, Convert
(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
The date converts fine to the format that I need. The time does not. It is
being displayed as military times and I want it to appear as a standard 12
format. i.e 9:00 AM
I tried all of the codes I found in BOL and none gave me what I wanted. Is
it possible to do what I want to do?
ThanksI think you can use the following:
Convert
(Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
HTH
Barry|||Thanks. But it still shows up as military time when I use 100.
"Barry" wrote:

> I think you can use the following:
> Convert
> (Char(8),dbo.TrainingSchedules.RequestedTime,100) AS Time,
> HTH
> Barry
>|||Umm not sure why - I have just checked the BOL and it confirms my
suggestion in the CAST and CONVERT section.
What are storing the Time as? Datetime?
Barry|||right(convert(varchar,dbo.TrainingSchedule.RequestedTime,100),7) as Time
Brennan wrote:

>I am using the following snippet of code to help me convert the date and ti
me
>in a query I am writing.
>SELECT dbo.Users.FirstName + ' ' + dbo.Users.LastName AS Student,
>dbo.Subjects.Subject, Convert
>(Char(15),dbo.TrainingSchedules.RequestedDate,101) AS Date,
> Convert
>(Char(8),dbo.TrainingSchedules.RequestedTime,108) AS Time,
>The date converts fine to the format that I need. The time does not. It i
s
>being displayed as military times and I want it to appear as a standard 12
>format. i.e 9:00 AM
>I tried all of the codes I found in BOL and none gave me what I wanted. Is
>it possible to do what I want to do?
>Thanks
>|||Usually, formatting is best left to the client since most client languages
have far better capabilities in this area. It isn't particularly clear what
datatypes you are using for the columns in question - the assumption is that
they are both datetime (or smalldatetime). If this assumption is not valid,
then you should clarify what the datatypes are and the expected formats of
the data (if applicable). One can question the wisdom of separating these
two intimately related bits of information into two separate columns -
especially given the dbms support.
If you must persist in this quest, you will most likely need to "generate"
the appropriate information in some convoluted and complex expression (and
possibly multiple queries). For the convert function, none of the available
formats has a space between the time and the AM/PM characters. If this can
be ignored, the 100 format is the closest - convert to this format and take
the last 7 characters (or all the characters from the last space to the end
of the string). You could also use the datepart functions to strip off and
convert the bits that are of interest. Experiment a bit - I think you will
understand the reason for the my first statement.|||Can you post a repro? Is the datatype really datetime? Also, I agree that fo
rmatting is best
performed in the client application.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Brennan" <Brennan@.discussions.microsoft.com> wrote in message
news:753F4CC7-DC7F-496E-8834-51667F2BFD0B@.microsoft.com...
> Thanks. But it still shows up as military time when I use 100.
> "Barry" wrote:
>|||Thanks I agree with you about the client. I am using smalldatetime.
My problem is that my client is a DNN portal. I am using an add in module
that let's me dynamically display the results of an SQL statement in a grid
on any selected page.
Unfortunately, it does not give me the opportunity to adjust any formatting
which was why I was trying to approach it from a Convert perspective. And I
know nothing about asp so I can't approach the problem from the client side.
I'll try some of the solutions mentions here, but I think I'm going to end u
p
writing an RS report to provide this information to our end users.
Thanks
"Scott Morris" wrote:

> Usually, formatting is best left to the client since most client languages
> have far better capabilities in this area. It isn't particularly clear wh
at
> datatypes you are using for the columns in question - the assumption is th
at
> they are both datetime (or smalldatetime). If this assumption is not vali
d,
> then you should clarify what the datatypes are and the expected formats of
> the data (if applicable). One can question the wisdom of separating these
> two intimately related bits of information into two separate columns -
> especially given the dbms support.
> If you must persist in this quest, you will most likely need to "generate"
> the appropriate information in some convoluted and complex expression (and
> possibly multiple queries). For the convert function, none of the availab
le
> formats has a space between the time and the AM/PM characters. If this ca
n
> be ignored, the 100 format is the closest - convert to this format and tak
e
> the last 7 characters (or all the characters from the last space to the en
d
> of the string). You could also use the datepart functions to strip off an
d
> convert the bits that are of interest. Experiment a bit - I think you wil
l
> understand the reason for the my first statement.
>
>

convert

can someone give me the command to conver varchar to date please.
In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
but need to update the colum with 2006-11-21 00:00:06.
Thanks,
Stoneystoney wrote:
> can someone give me the command to conver varchar to date please.
> In my table I have varchar(50) Tue Nov 21 00:00:06 EST 2006 for the column
> but need to update the colum with 2006-11-21 00:00:06.
> Thanks,
> Stoney
Try to look up the CONVERT command in BOL. Here you can see the various
styles you can use. I think in your case you can use 20/120.
Regards
Steen Schlüter Persson
Database Administrator / System Administratorsqlsql

convert

Hi,
I need to change the format of a date column in one of the tables to reflect
a date that looks like mm/dd/yyyy. I used the following syntax that I know
it's wrong -it works in a select statement-:
alter table tbl_Reservation
convert(char(20),[from date],101) as [From Date]
Is there anyway to change the format on the table itself?
TSWhat datatype do you wish to have for that column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TS" <TS@.discussions.microsoft.com> wrote in message
news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
> Hi,
> I need to change the format of a date column in one of the tables to refle
ct
> a date that looks like mm/dd/yyyy. I used the following syntax that I know
> it's wrong -it works in a select statement-:
> alter table tbl_Reservation
> convert(char(20),[from date],101) as [From Date]
> Is there anyway to change the format on the table itself?
>
> --
> TS|||I just need to change the format of that column from for example 2005-05-16
00:00:00 to a format of mm/dd/yyyy
TS
"Tibor Karaszi" wrote:

> What datatype do you wish to have for that column?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TS" <TS@.discussions.microsoft.com> wrote in message
> news:CB5714C6-AC38-4C2B-B5F8-396CE43DAAA1@.microsoft.com...
>|||Tibor asked the data type of the field because if it's a datetime then
there's no way to "change the format" -- datetimes aren't stored in a
particular format, that's a display issue. If it's stored as a character typ
e
(which would probably be less than optimal), we would probably be able to
recommend some options.
"TS" wrote:
> I just need to change the format of that column from for example 2005-05-1
6
> 00:00:00 to a format of mm/dd/yyyy
> --
> TS
>
> "Tibor Karaszi" wrote:
>|||> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format"
Exactly. :-)
For more information, TS, I suggest you check out
http://www.karaszi.com/SQLServer/info_datetime.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"KH" <KH@.discussions.microsoft.com> wrote in message
news:E84225DE-A465-40C2-A343-246FF6551D42@.microsoft.com...
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Yes, the data type of the field is datetime. In the front-end, it appears in
the format mm/dd/yyyy which is the way I want it even though in the query I
ran from SQL it appears in the datetime format that there is no way to
change. I didn't know this is the case!!
Thanks a lot for your help.
--
TS
"KH" wrote:
> Tibor asked the data type of the field because if it's a datetime then
> there's no way to "change the format" -- datetimes aren't stored in a
> particular format, that's a display issue. If it's stored as a character t
ype
> (which would probably be less than optimal), we would probably be able to
> recommend some options.
>
> "TS" wrote:
>|||Hi,
You cant change the storage format if you use datetime data type. Only way
is during extraction you could use CONVERT function to format the date.
Alternatevely you could use the VARCHAR datatype to store the date format as
you need. Wile inserting you could format the field with CONVERT function.
But I recommend you to use datetime data type and while extraction you can
format it using CONVERT function.
Thanks
Hari
SQL Server MVP
"TS" <TS@.discussions.microsoft.com> wrote in message
news:FDF45283-3516-46A5-92FA-8AE1EF27AAE1@.microsoft.com...
> Yes, the data type of the field is datetime. In the front-end, it appears
> in
> the format mm/dd/yyyy which is the way I want it even though in the query
> I
> ran from SQL it appears in the datetime format that there is no way to
> change. I didn't know this is the case!!
> Thanks a lot for your help.
> --
> TS
>
> "KH" wrote:
>

conversion to date

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.

CAST(RIGHT(datafield,2)+LEFT(datafield,4) AS datetime)

|||Thanks Motley. It works!

Conversion query

I have 2 date variables passed into my store procedure as follows
@.StartDate datetime,
@.EndDate datetime
however I am getting a conversion error when running the command below which
says "Syntax error converting datetime from character string"
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
') AND (DateEntered <= ' + @.EndDate + ')')
Any suggestions would be welcome.
ThanksYou have to explictly cast the datetime values to characters like so
PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
SELECT DISTINCT SubRef
FROM Subscriptions
WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= '
+ Cast(@.StartDate As VarChar(20))
+ ') AND (DateEntered <= ' + Cast(@.EndDate As VarChar(20)) + ')')
Thomas
"Pete" <Pete@.discussions.microsoft.com> wrote in message
news:E78EE524-27E2-47AF-96E4-60B250AFA884@.microsoft.com...
>I have 2 date variables passed into my store procedure as follows
> @.StartDate datetime,
> @.EndDate datetime
> however I am getting a conversion error when running the command below whi
ch
> says "Syntax error converting datetime from character string"
> PRINT ('INSERT INTO ' + @.NewSubsList + '(SubRef)
> SELECT DISTINCT SubRef
> FROM Subscriptions
> WHERE (PubCode = ' + @.PubCode + ') AND DateEntered >= ' + @.StartDate +
> ') AND (DateEntered <= ' + @.EndDate + ')')
> Any suggestions would be welcome.
> Thanks

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

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

Any idea why this simple straight forward string to date time conversion keeps failing with the error message, conversion failed due to potential loss of data?

The input values looks like this 20070511 23:06:30, what is that I'm missing here for the conversion to fail?

Thanks

|||

As much as it sounds ridiculous, looks like SSIS does not understand YYYYMMDD format...

Thanks to Jamie Thomson' s post here, which solved the problem.

http://blogs.conchango.com/jamiethomson/archive/2006/06/26/SSIS_3A00_-Parsing-datetime-values.aspx

Conversion of DTS to SSIS command Line

I am trying to convert a command line using the dtexecui utility. I need to pass three parameters ; account number ,begin and end date to project.

What am i doing wrong ?

DTEXEC /DTS "\File System\Archive Data" /SERVER SRV2 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW \package /SET "Account_Number";"'00001'" /SET "File_Name";"'C:\Inetpub\wwwroot\output\Archive\'" /SET "Begin_Date";"'04/03/2006'" /SET "End_Date";"'04/04/2006'"

Error I get

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 9:52:49 AM
Warning: 2006-04-05 09:52:51.58
Code: 0x80012018
Source: Archive Data
Description: The configuration entry, "Account_Number", has an incorrect form
at because it does not begin with the package delimiter. Prepend "\package" to t
he package path.
End Warning
Warning: 2006-04-05 09:52:51.58
Code: 0x80012017
Source: Archive Data
Description: The package path referenced an object that cannot be found: "Acc
ount_Number". This occurs when an attempt is made to resolve a package path to a
n object that cannot be found.
End Warning
DTExec: Could not set Account_Number value to '00001'.
Started: 9:52:49 AM
Finished: 9:52:51 AM
Elapsed: 2.172 seconds

Your command line is not correct as each set command needs a package path starting with \package just as the error message indicates. As I don't know tasks these properties belong to I can't give you the exact path but in general the set option should look something like "\Package.rest_of_path_to_property". You can use the configurations on the package to identify what the package path should look like. You should also remove the \package from the command line outside of the set because that is invalid.

HTH,

Matt

Conversion of Char to SmallDateTime

I have a column that has date information in it in the following
format: yyyymmdd I want to convert this information into and actual
SmallDateTime column so I can use it for comparison etc. Some of the
dates that are stored in this data are either blank or invalid dates
because of bad user input.
select
cast(left(inv_dt1,4) + '-' + right(left(inv_dt1,6),2)+'-'+
right(inv_dt1,2) as smalldatetime) as inv_dt
into newtable
from origtable
If I run that code, it errors out on the invalid fields. Is there a
way to tell SQL to just NULL the field if it is invalid in any way and
continue on?
Thanks,use the isdate function
create table WasabiTable (inv_dt1 varchar(23))
insert WasabiTable values ('ababababa')
insert WasabiTable values ('20060101')
insert WasabiTable values ('20060299')
insert WasabiTable values (NULL)
insert WasabiTable values ('20060401')
insert WasabiTable values ('20050331')
select
case
when isdate(inv_dt1) = 1 then convert(smalldatetime,inv_dt1)
else null
end onsetdate
from WasabiTable
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||On 30 May 2006 12:10:08 -0700, "SQL Menace" <denis.gobo@.gmail.com>
wrote:

>use the isdate function
Just to add to that, be aware the ISDATE tests if the string will
convert to a valid DATETIME datatype. Valid dates for DATETIME are
from January 1, 1753 through December 31, 9999. Valid dates for
SMALLDATETIME are from January 1, 1900, through June 6, 2079. So,
while it may be unlikely, it is possible for a string to satisfy the
ISDATE test yet still fail the conversion to SMALLDATETIME.
Roy Harvey
Beacon Falls, CT

Conversion for Time

I can get my DB to accept my date by doing the following: row.Item("RequestDate") =Me.fullDate.Date --I have fulldate dimensioned as date above. However if I try to do the follwing for a Time it gives me an error when it trys to update the DB the column is set to datetime & when I check the value of the row Item in my command window it says

?row.Item("BeginTime")
#6:00:00 AM# {Date}
[Date]: #6:00:00 AM#

row.Item("BeginTime") =CDate(ddlBegin.SelectedValue & beginAMPM)
row.Item("EndTime") =CDate(ddlEnd.SelectedValue & endAMPM)
The SQL Error I get is the following:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Source Error:

Line 335: row.Item("EndTime") = CDate(ddlEnd.SelectedValue & endAMPM)Line 336: DsVacationData1.RequestData.AddRequestDataRow(row)Line 337: SqlDataAdapter2.Update(DsVacationData1)Line 338: DsVacationData1.AcceptChanges()Line 339: End Sub

Thanks for any help.Ok i figured out what I was doing wrong, to a point. I got it to store my time but it also put in today's date along w/the time is there a way to just enter in the time w/no date?|||There's no Time data type. There's only datetime andsmalldatetime. So the answer to your question, only way is toutilize char, var, nchar, nvar or add in a universal date that will beignored.
|||

The cause of the problem is ANSI SQL NULL is an unknown while .NET NULL is an empty string so the difference is causing the overflow. I found two VB code and a C# link with code. Hope this helps.
http://www.inq.net/WebLog/dbalzer/archive/2005/06/16/125145.aspx

(If SomeDate = DateTime.MinValue Then
cmd.Parameters("@.SomeDate").Value = DBNull.Value
Else
cmd.Parameters("@.SomeDate").Value = SomeDate
End If)
First codeblock

(Public Function chkDateParam(ByVal d As Date, ByRef sqlParam As
SqlParameter)
Try
If d = System.DateTime.MinValue Then
sqlParam.Value = DBNull.Value
Else
sqlParam.Value = d
End If

Catch ex As NullReferenceException

'if the field is blank, str will = null. so set the param to
null too!
sqlParam.Value = DBNull.Value

End Try
End Function )
Second codeblock

sqlsql

Conversion errors on date field from Access to SQL server7

Hi
1st time trying to migrate Access 2000 tables to SQLServer7.
The tables transport but I'm getting errors on the data transfer.

The error is based on the date/time field in Access...ex: DOB (DateofBirth) field is formatted as shortdate.

When the error occurs in transport it reads:
Error at Destination for Row number 310...
Insert error, column 16('DOB', DBTYPE_DBTIMESTAMP), status 6. Data overflow. Invalid character value for cast specification.

**What I have found so far is that this error occurs on the rows in the DOB field where the year of birth is before 1900 (ie:1897)...or in some instances if the year is mistakenly in as...example: 9194 (as opposed to 1994) it will not except the transfer.

I have created a mock table with a date/time field of this format (with all the years being in 2002) and it transfers fine!

Any ideas on how I get the SQL Server to accept these records??use datetime rather than smalldatetime.

valid datetime range is 1-Jan-1753 to 31-Dec-9999 23:59:59.9999|||I did convert the SQL field to datetime...but it still gives conversion errors on date fields that are in the 1800's!!??!!

If I change those to 01/01/1900...they will transfer.|||I must be missing the point... if the date is '01-Jan-1897' it would go into a datetime field with out problems. can you provide an example of a trouble maker?|||Here goes...there were some records that had dates like this:
01/01/9194
01/01/1897
01/01/1583 etc...

You had mentioned that if I changed the SQL field to datetime from smalldatetime (which I had already done)...then it would tranfer data from 1-Jan-1753 to 31-Dec-9999

Well...after I changed the field to datetime the records, such as, 01/01/1897 wouldn't transfer...even though they were in the valid range for datetime.

[And if I changed all the records that were before the year 1900 to a date after 1900 it would transfer].

Hope this clears it up.|||thanks!

You may have other problems here, consider the following code:

declare @.dt datetime, @.vc varchar(100)
set @.dt = '01/01/9194'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1897'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc
set @.dt = '01/01/1583'
set @.vc = cast(@.dt as varchar)
select @.dt, @.vc

As one would expect the last date is a problem. Could you import the date data into a varchar field and then selectivly convert the data?

Conversion Error...nvarchar to Datetime

Hi Group,
I am new with SQL Server..I am working with SQL Server 2000.
I am storing the date in a nvarchar column of atable... Now I want to
show the data of Weekends..Everything is OK...But the problem is
arising with Conversion of nvarchar to date...to identify the
weekends...Like..Here DATEVALUE is a nvarchar column...But getting the
error..Value of DATEVALUE like dd-mm-yyyy...04-08-2004

------------------
Server: Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.
------------------
------Actual Query----------
Select DATEVALUE,<Other Column Names> from Result where
Datepart(dw,convert(Datetime,DATEVALUE))<>1 and
Datepart(dw,convert(Datetime,DATEVALUE))<>7
------------------
Thanks in advance..
Regards
Arijit Chatterjee(arijitchatterjee123@.yahoo.co.in) writes:
> I am new with SQL Server..I am working with SQL Server 2000.
> I am storing the date in a nvarchar column of atable... Now I want to
> show the data of Weekends..Everything is OK...But the problem is
> arising with Conversion of nvarchar to date...to identify the
> weekends...Like..Here DATEVALUE is a nvarchar column...But getting the
> error..Value of DATEVALUE like dd-mm-yyyy...04-08-2004

Best is to store date values in datetime columns. If you use character
format, you should use char (the n just doubles the space with no gain
for it, and the var is pointless since size is fixed), and you should use
the format YYYYMMDD. Furthermore, you should attach a constraint to the
columns

datecol char(8) CONSTRAINT ck_tbl_datecol CHECK (isdate(datecol) = 1)

to ascertain that you don't get illegal values.

Storing dates in a format like DD-MM-YYYY is going to give all sorts of
headache. 04-08-2004 could be interpreted as Aug 4th or April 8th, depending
on language and datefromat settings. (And, in case of humans, of the
perceptions of the user.) You can't sort on this format (unless you really
want 3 Aug to come before 4 June).

The format YYYYMMDD sorts well, and is always interpreted in the same way.

See also http://www.karaszi.com/SQLServer/info_datetime.asp.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||See this

declare @.t table (d varchar(20))
insert into @.t values('10-apr-2005')
insert into @.t values('10-MAy-2005')
insert into @.t values('10-Jun-2005')
insert into @.t values('10-Jul-2005')
Select * from @.t where Datepart(dw,convert(Datetime,d))<>1 and
Datepart(dw,convert(Datetime,d))<>7

Madhivanan|||Thanks for your great help..
Regards
Arijit Chatterjee

Conversion between Date Formats

Hi. I have a DB in which we store dates in yyyy/mm/dd. However when we
want to display this date via a web frontend, it needs to be in
dd/mm/yyyy. I've declared a function (shown below) which converts
between these date formats and returns a varchar(20). This works fine
however now I need to have the ability to sort on this date field in
the frontend. This requires my function to return a datetime in the
required format. Can this be done?

DECLARE @.InputDate nvarchar(20)
DECLARE @.OutputDate nvarchar(20)
DECLARE @.Day nvarchar(2)
DECLARE @.Month nvarchar(2)
DECLARE @.Year nvarchar(4)
DECLARE @.Time nvarchar(12)

SET @.InputDate = '2005/03/01 14:30:00'
SET @.Day = cast(datepart(day,@.InputDate) as nvarchar(2))
SET @.Month = cast(datepart(month,@.InputDate) as nvarchar(2))
SET @.Year = cast(datepart(year,@.InputDate) as nvarchar(4))
SET @.Time = substring(cast(@.InputDate as nvarchar(23)),12,12)

SET @.OutputDate = replicate('0',2-len(@.Day)) + @.Day + '/' +
replicate('0',2-len(@.Month)) + @.Month + '/' +
@.Year + ' ' + @.Time

SELECT @.OutputDate AS OutputDate

Thx
VilenReturn dates as dates and format them for display in the front end or
middle tier. Some users may prefer to format them differently to the
way you do.

In the database dates should be stored as DATETIME or SMALLDATETIME
datatypes. These DO NOT have any fixed format and will always sort
chronologically. It isn't a good idea to sort on a function or
expression if you can avoid it.

--
David Portas
SQL Server MVP
--

Conver String to Date

Hi all,
I am trying to convert the string value of '12.01.50' to a propert date
value of 12/01/1950, I have tried various things but cant seem to get it int
o
the right format although I can convert it to a date type. Can anyone help?
Thanks PhilYou either have to do the conversion going directly from string to string. O
r you have to go from
string to datetime and then string again. You can't just go from string to d
atetime, since datetime
doesn't have any format (the client application does the formatting). So, so
mething like:
CONVERT(varchar(zz), CONVERT(datetime, '12.01.50' , xxx), yyy)
Where xxx and yyy are the appropriate formatting codes (documented in Books
Online, CONVERT). I
prefer to do formatting in the client app, though. Also see
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:24C094A5-2B67-49B3-A40D-BECF53AB5530@.microsoft.com...
> Hi all,
> I am trying to convert the string value of '12.01.50' to a propert date
> value of 12/01/1950, I have tried various things but cant seem to get it i
nto
> the right format although I can convert it to a date type. Can anyone hel
p?
> Thanks Phil