Converting a date with a derived column

I'm having trouble converting a date in the format of:

Jan 11 2006 12:00:00:000AM

to a smalldatetime in my new SSIS package. I'm trying the derived column transformation, but I'm at a wall now, especially with the conversion of 'Jan' string to integer/month/value of 1. Anyone have experience/advice on this transformation?

What happened to the DateTime String transformation in SQL 2000? Was that too unpopular to move to integration services? That transformation saved my butt many times - every banks' data feed we import uses a different date format.

Thanks in advance for any advice/help!

-Erik

Have you attempted to chop the seconds and milliseconds off the date? And then stuff it into a smalldatetime column.|||

Seems like it does not like those 000 at the end.

The only way I see at this point - to cat the 000 out of it and them cast it with DT_DBDATE or something

|||I can use SUBSTRING to skip the time at the end, but what about converting Jan to the value 1?|||After you do SUBSTRING, use type casting to cast to needed date format. If this is not enough, you can use YEAR,MONTH abd DAY after you converted to ANY date format

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?
What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER COLUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegro ups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER COLUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

Converted data types

I'm using SQL Servers Import function to import an entire MS Access
database.
Date/time types from Access are defaulting to smalldatetime in SQL
Server, but the actual data is too large and I get an error, or rather
many errors since this happens in many tables.
I can edit the SQL to make these datetime types but, since there are
so many, is there any way to globally change the default data mapping?What do you mean "the actual data is too large"? What "error" do you get?
Do you really have a lot of dates that fall outside the range supported by
smalldatetime?
Anyway, there is an easy way to generate a script to alter all smalldatetime
columns => datetime.
SELECT 'ALTER TABLE ['+TABLE_SCHEMA+'].[' + TABLE_NAME + '] ALTER CO
LUMN ['
+ COLUMN_NAME + '] DATETIME'
+ CASE WHEN IS_NULLABLE = 'NO' THEN
' NOT NULL ' ELSE '' END
+ CASE WHEN COLUMN_DEFAULT IS NOT NULL THEN
' DEFAULT ' + COLUMN_DEFAULT ELSE '' END + ';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'smalldatetime';
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"blindsey" <blindsey@.dsicdi.com> wrote in message
news:1183407383.413284.218870@.n2g2000hse.googlegroups.com...
> I'm using SQL Servers Import function to import an entire MS Access
> database.
> Date/time types from Access are defaulting to smalldatetime in SQL
> Server, but the actual data is too large and I get an error, or rather
> many errors since this happens in many tables.
> I can edit the SQL to make these datetime types but, since there are
> so many, is there any way to globally change the default data mapping?
>

convert varchar to smalldatetime

A table contains a field of type smalldatetime
The feed has this field as string i.e. '060830'
Would like to insert this string date into the field of the table which has a datatype of smalldatetime.

Is this the correct way to convert a string to smalldatetime?

declare @.DateFeed varchar(6)
declare @.TradeDate smalldatetime

set @.DateFeed = '060830'

set @.TradeDate = convert(smalldatetime, 6 + '-' + 8 + '-' + 30)
print @.TradeDate

Thanks

Use the following query...

Prefixing the century value on your data & casting the result as datetime

Code Snippet

Select Cast('20' + '060830' as smalldatetime)

|||

Use function CONVERT, with style 12. Let SQL Server decides the century (0 - 49 --> 2000 / 50 - 99 --> 1900).

select convert(smalldatetime, '060830', 12)

go

AMB

Convert to smalldatetime

I have a varchar field containing dates that I am trying to convert to a
smalldatetime but I keep getting the following error message:
Syntax error converting datetime from character string.
Here is the line of code I am using:
select convert(smalldatetime, DateReported, 101)
from tblName
I checked to make sure there were only valid dates in the field using IsDate
so I am not sure why I can't convert the data to a smalldatetime datatype.
Thanks!any sample data? can you locate the record that causes problem? or all of
them are problematic? maybe the 101 format is wrong?
peter|||It would help if you post a sample of the data, but it appears there is at
least one row that has some extra special character which is the one that
causing the error at the time of the conversion.
You can try executing
select top 1 convert(smalldatetime, DateReported, 101)
from tblName
to verify that not all the values have an issue.
Let me know
"Anonymous" wrote:

> I have a varchar field containing dates that I am trying to convert to a
> smalldatetime but I keep getting the following error message:
> Syntax error converting datetime from character string.
> Here is the line of code I am using:
> select convert(smalldatetime, DateReported, 101)
> from tblName
> I checked to make sure there were only valid dates in the field using IsDa
te
> so I am not sure why I can't convert the data to a smalldatetime datatype.
> Thanks!
>
>|||You guys are right. The data imported with an extra character.
I changed my insert statement to:
Insert tblNew
Select LTrim(RTrim([DateReported]))
From tblOld
However, the extra space is still being inserted into the field. How do I
insert the data with no trailing spaces?
"Edgardo Valdez, MCSD, MCDBA" wrote:
> It would help if you post a sample of the data, but it appears there is at
> least one row that has some extra special character which is the one that
> causing the error at the time of the conversion.
> You can try executing
> select top 1 convert(smalldatetime, DateReported, 101)
> from tblName
> to verify that not all the values have an issue.
> Let me know
> "Anonymous" wrote:
>|||You can use the T-SQL function RTRIM and or LTRIM
Let me know if that helps
"Anonymous" wrote:
> You guys are right. The data imported with an extra character.
> I changed my insert statement to:
> Insert tblNew
> Select LTrim(RTrim([DateReported]))
> From tblOld
> However, the extra space is still being inserted into the field. How do I
> insert the data with no trailing spaces?
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||I tried that but it is still inserting the extra space. This is the code I
used to insert the data into the new table:
Insert tblNew
Select LTrim(RTrim([DateReported]))
From tblOld
"Edgardo Valdez, MCSD, MCDBA" wrote:
> You can use the T-SQL function RTRIM and or LTRIM
> Let me know if that helps
> "Anonymous" wrote:
>|||You can try the REPLACE function
Insert tblNew
select replace(DateReported, ' ', '')
From tblOld
"Anonymous" wrote:
> I tried that but it is still inserting the extra space. This is the code
I
> used to insert the data into the new table:
> Insert tblNew
> Select LTrim(RTrim([DateReported]))
> From tblOld
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Nope, that doesn't work either. Even tried a LTrim RTrim and replace update
on the data once it was in the table.
Is there a line of code to see what the ascii character is that is being
used. Maybe it isn't a space.
"Edgardo Valdez, MCSD, MCDBA" wrote:
> You can try the REPLACE function
> Insert tblNew
> select replace(DateReported, ' ', '')
> From tblOld
> "Anonymous" wrote:
>|||Well you can use ASCII to inspect a character at a time:
eg
SELECT ASCII('a') -- Returns 97
Write a loop to look at your string. I myself had big problems when
importing some Null Characters ( ascii code 0 )
SELECT ASCII(CHAR(0))
You can't use REPLACE on these and I had to use STUFF! Whoever uses STUFF
Kev!? You could use a low-level text editor to look at these. But why worr
y
about that? Just use SUBSTRING to extract the bit of string you want. ie i
f
you know your funny character is at the start of your string use SUBSTRING (
your_date, 2, 10 ) for example
Let me know how you get on.
Damien
"Anonymous" wrote:
> Nope, that doesn't work either. Even tried a LTrim RTrim and replace upda
te
> on the data once it was in the table.
> Is there a line of code to see what the ascii character is that is being
> used. Maybe it isn't a space.
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>|||Can you show use the definition of tblNew as well as tblOld?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Anonymous" <Anonymous@.discussions.microsoft.com> wrote in message
news:2157F90B-2B55-4AB6-AD14-0F64D506F792@.microsoft.com...
>I tried that but it is still inserting the extra space. This is the code I
> used to insert the data into the new table:
> Insert tblNew
> Select LTrim(RTrim([DateReported]))
> From tblOld
>
> "Edgardo Valdez, MCSD, MCDBA" wrote:
>

Convert String to smalldatetime

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Noel Fernandez wrote:

Hi,

I am new to SSIS and have been trying to load a date from a Table1.ColumnA (varchar(50)) to a Table2.ColumnB(smalldatetime).

I tried using the Derived Column and Data Conversion controls but I get the below error.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

What do I do to load this data.

Thanks,

Noel

Try changing Table2.ColumnB to datetime rather than smalldatetime and see if it works.

-Jamie

|||

What type of database are you writing to? I can't replicate this against a SQL Server 2005 db.

If you can't change the datatype of the destination, you might try casting the column to a smalldatetime when you read it from the source.

|||

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Hi Subhash,

Well now I'm confused. First of all you said the error was:

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Then you said it was:

error while converting DB_STR type to DB_DATATIMESTAMP type

Which is it?

-Jamie

|||

Subhash512525 wrote:

Hi Jamie,

In my case, I have a flatfile that has a string type column (value: 20070610) which when mapped to datetime column type of a table in SQL Server 2005 database. It displays "error while converting DB_STR type to DB_DATATIMESTAMP type". As the client wants to run packages in BC mode, we donot know how to cast this in DTS as well wonder how to write a transformation mapping..

Thanks

Subhash Subramanyam

Use this expression in a Derived Column transform:

Code Snippet

(DT_DBTIMESTAMP)(SUBSTRING(DateValue,1,4) + "-" + SUBSTRING(DateValue,5,2) + "-" + SUBSTRING(DateValue,7,2))

The date needs to have the dashes inserted for the cast to work.

|||

Jwelch,

Thanks for your support. That resolved our issue. I was unable to mark this as an answer, however I have marked that as a helpful post.

Regards

Subhash Subramanyam

convert sql smalldatetime column into numbers

HI all;

I have a simple question to ask; I need to create a column with the data from my DOB column (which has the smalldatetime type attached to it). I know how to do that but I am not too sure how to convert the data from that column int normal character for example when I copy it into my newly created column and change the type to varchar I get this jan 16 1979 from this date 1979/01/16. But I actually want the data to look like this 19790116, so in effect I just want to take out the slashes.

Any help would be highly appreciated, thanks all.select convert(varchar,getdate(),112) ??

Convert smalldatetime to mmm yy format in query

I am trying to write a simple query that retrieves the data field from a table (stored in the smalldatetime format) and converts the date to mmm yy format. The closest I can get is retrieving the date in the dd mmm yy format using the query below.

select convert(varchar(10),DATA_DATE,06) As DATA_DATE

If there an easy way to parse out the information I want? I also attempted to use the SUBSTR functions, but they always returned error messages.So you want to exclude the dd out of the datetime ?|||Try the following:

select right(convert(varchar(20),getdate(),06), len(convert(varchar(20),getdate(),06)) -3)|||That is exactly what I have been attempting to do. Thank you for your help.

Convert Smalldatetime

Hello everyone. I am running into some small problems converting a smalldatetime field. I currently have 2005-10-17 00:00:00
in the field but what it to have forward slashes instead of th dash. I tried a few convert methods but not successful.

Does anyone have any ideas on how to make this work?

All help is appreciated.Hello everyone. I am running into some small problems converting a smalldatetime field. I currently have 2005-10-17 00:00:00
in the field but what it to have forward slashes instead of th dash. I tried a few convert methods but not successful.

Does anyone have any ideas on how to make this work?

All help is appreciated.

Try convert(varchar(10), getdate(), 111)

Regards,

hmscott|||Try convert(varchar(10), getdate(), 111)

Regards,

hmscott

Thanks Again. I worked just fine.|||WHY...do you want to conver the datetime value in the first place? Or are you just trying to truncate off the time portion, in which case this method is faster if less intuitive:
select dateadd(day, datediff(day, 0, getdate()), 0)

CONVERT IN SELECT

Is it possible to covert a smalldatetime in a select statement using a
regular expression of some kind?
My dates are stored as:
7/30/2005 6:58:00 PM
I need to sort and group the dates by hour but I need to continue to store
the minute. Can I envoke the use of a regular expression in a convert
function? If that's possible, could someone post an example? I don't know
much about regular expressions in SQL.
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
See http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>
|||Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field>)
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Ian O'Betz" wrote:

> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>
|||I forgot to mention this in my first posting, but I need the date as:
7/30/2005 6:00 PM if the actual date is 7/30/2005 6:58 PM
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:6E3E1C6E-06F1-4D5E-9EFB-2D926D0CB459@.microsoft.com...
Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field>)
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Ian O'Betz" wrote:

> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>
|||Perfect, using DateAdd() was my solution. thanks for your help.
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxxjwr$mFHA.3380@.TK2MSFTNGP12.phx.gbl...
See http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>

CONVERT IN SELECT

Is it possible to covert a smalldatetime in a select statement using a
regular expression of some kind?
My dates are stored as:
7/30/2005 6:58:00 PM
I need to sort and group the dates by hour but I need to continue to store
the minute. Can I envoke the use of a regular expression in a convert
function? If that's possible, could someone post an example? I don't know
much about regular expressions in SQL.
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.netSee http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field> )
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ian O'Betz" wrote:

> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||I forgot to mention this in my first posting, but I need the date as:
7/30/2005 6:00 PM if the actual date is 7/30/2005 6:58 PM
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:6E3E1C6E-06F1-4D5E-9EFB-2D926D0CB459@.microsoft.com...
Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field> )
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ian O'Betz" wrote:

> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||Perfect, using DateAdd() was my solution. thanks for your help.
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxxjwr$mFHA.3380@.TK2MSFTNGP12.phx.gbl...
See http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>

CONVERT IN SELECT

Is it possible to covert a smalldatetime in a select statement using a
regular expression of some kind?
My dates are stored as:
7/30/2005 6:58:00 PM
I need to sort and group the dates by hour but I need to continue to store
the minute. Can I envoke the use of a regular expression in a convert
function? If that's possible, could someone post an example? I don't know
much about regular expressions in SQL.
--
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.netSee http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field>)
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ian O'Betz" wrote:
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||I forgot to mention this in my first posting, but I need the date as:
7/30/2005 6:00 PM if the actual date is 7/30/2005 6:58 PM
--
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:6E3E1C6E-06F1-4D5E-9EFB-2D926D0CB459@.microsoft.com...
Hi Ian
Probably you are looking for:
SELECT <Firlds>, CONVERT( varchar(8),<date_field>,108 )
FROM <table>
ORDER BY datepart(hh,<date_field>)
Please let me know if you would like to know anything else.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Ian O'Betz" wrote:
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>|||Perfect, using DateAdd() was my solution. thanks for your help.
--
Ian O'Betz
Clear Results Sytem Operator
www.clearresults.net
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uxxjwr$mFHA.3380@.TK2MSFTNGP12.phx.gbl...
See http://www.karaszi.com/SQLServer/info_datetime.asp
"Ian O'Betz" <sysopNUNYO@.SPAMclearresults.net> wrote in message
news:uWonvk$mFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Is it possible to covert a smalldatetime in a select statement using a
> regular expression of some kind?
> My dates are stored as:
> 7/30/2005 6:58:00 PM
> I need to sort and group the dates by hour but I need to continue to store
> the minute. Can I envoke the use of a regular expression in a convert
> function? If that's possible, could someone post an example? I don't know
> much about regular expressions in SQL.
> --
> Ian O'Betz
> Clear Results Sytem Operator
> www.clearresults.net
>
>

Convert Error

I am trying to convert a varchar(10) into a smalldatetime field in a view.

The table holds the date information in the format dd/mm/yyyy in a
varchar(10).

I can convert most fields in this format in my view using;

CONVERT (smalldatetime, ActualInDate, 103)

However one field fails, I have some dates as 02/01/0000, this is because it is a blank system date which is output from my mainframe job to extract data from another server.

Can I convert these blank dates to sql blank dates in my convert statement, ie replace these dates from 02/01/0000 to 01/01/1900.

The error I get is the error below;

Database Server: Microsoft SQL Server
Version 08.00.0760
Runtime Error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of char data type to smalldatetime datatype resulted in an out-of-range small datetime value.

Cheers,

JonMy guess would be that you are having problems with different data rows than you think are causing the problems. Use the IsDate() function to show you the "problem children" in your data. I suspect that your server is interpreting the dates as MM/DD/YYYY instead of dd/mm/yyyy as you expect. If that is the case, I'd convert the whole bunch to ISO standard format of YYYY-MM-DD (like 1900-01-01), which is a lot harder to misinterpret!

-PatP|||This appears to work:

CREATE TABLE MyTable
(
ActualInDate varchar(20)
)

INSERT INTO MyTable(ActualInDate)
VALUES ('02/01/0000')
INSERT INTO MyTable(ActualInDate)
VALUES ('13/02/1975')

SELECT CONVERT (smalldatetime, (CASE WHEN ActualInDate = '02/01/0000' THEN '01/01/1900' ELSE ActualInDate END), 103)
FROM MyTable|||Yup, it appears, but appearances are misleading...

SELECT CONVERT (smalldatetime, (
case
when charindex('0000', ActualInDate) > 0 then '01/01/1900'
else case
when isdate(ActualInDate) = 0 then '01/01/1900'
else ActualInDate end
end), 103)
FROM MyTable|||Thanks for the ideas but I just used this to do it and it seems fine:

CONVERT (smalldatetime, REPLACE(ActualIn, '02/01/0000', '01/01/1900'), 103)

Cheers|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.

He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.|||rdjabarov,
do you hang out to bust my balls or is it just my imagination. i was solving the stated problem not writing his application for him.

He did'nt ask me to validate his date or I would have suggested isDate as well. He asked to get rid of the null data value coming from his main frame and that's what I did.I don't think that's his sole purpose in life, but rdjabarov often comes across that way. I've just had to develop thicker skin where his posts are concerned.

He really does know what he's doing (at least as often as most of us do anyway :)), but he comes across pretty abrasively at time.

-PatP|||Well there's the other side of the coin as well...you have to take everything with a grain of salt...because

IT JUST DOESN'T MATTER

Speaking of which...where's Rudy?|||I did not say he did'nt know what he was talking about. I was objecting to the snide remarks

I am sorry.
I am cranky ass.
Quitting smoking.
Unrealistic deadline.
Little sleep.
Stressed from a couple job interviews.
Too easily offended from my hot southern celtic blood.
I apologize.|||Speaking of which...where's Rudy?speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood

;)|||i was actually enjoying the intellectual dialogue in this thread

I'm still looking...|||speaking of abrasive, i was actually enjoying the intellectual dialogue in this thread until your honking great red font spoiled the mood

;)Yeah, but it does help to put things into context!

-PatP|||Pat does contexts ;)

Convert datetime columns to smalldatetime in whole database

Hi,

I need to convert all datetime columns to smalldatetime in the whole database. I really don't want to do it by hand and It would probably take me a whole day to figure out how to write such a procedure. If someone could help me out that would be great.
My database is divided into schemas just like AdventureWorks.
Also, no need to worry about date conversion, value could be set to current date.

Thanksmay i ask a stupid question?

why?|||That is a stupid question.

Choose my answer:

Why not?
Because
Cause I feel like it
All of the above|||1. because changing it is pointless
2. ok
3. good for you
4. all of the above

have fun stormin' the castle...|||That is a stupid question.

Choose my answer:

Why not?
Because
Cause I feel like it
All of the above

Fill in the blank: Kiss my ____________.|||Fill in the blank: Kiss my ____________.

Asphalt .|||That is a stupid question.

Oh, foolesh young jedi|||I really don't want to do it by hand

Then you should look into better grooming and more social skills

I'm sure you can find a date if you try hard enough

Lobster and champange work pretty well if you can master the other stuff|||Everyone seems really mean these days. Or am I just noticing it having come back?|||Everyone seems really mean these days. Or am I just noticing it having come back?

That is a stupid question

(So how does that feel?)|||That is a stupid question

(So how does that feel?)Waaaaaaaahhhhhhhhhhhhhhhh!!!!!!!!! :eek:|||That is a stupid question.

Choose my answer:

Why not?
Because
Cause I feel like it
All of the above

The list is not complete

You left off ignorance|||i am not mean

i would like to go back and try to re-start this friendly dialogue

dear polarbear2k, why do you want to do it?|||i am not meanI didn't mean you. it was a perfectly valid question to ask - the parameters of the solution were included in the original post but not the parameters of the problem.|||If you must know its a legacy software issue. A program we have craps out when you pass it seconds. Formatting the output is an option but preventing the problem in the first place is a better solution.

We really need the extra 4 bytes freed up. :p

Everybody is so mean. lol|||seems to me it would be easier to fix the program that's crapping out :D

you said "no need to worry about date conversion, value could be set to current date" -- how does wiping out all existing datetime values solve the problem?|||That's fair enough. So you need to round the datetimes off to the nearest minute, then convert the data type. The latter is not *strictly* required but cleaner and more thorough right?|||Also, you'll need use of system tables. 2000 or 2K5? If 2K5 then SP2 (this has a handy schema function you see)?|||And you were mean - Rudy is a very sensitive soul.|||Nobody who wears leather pants can be that sensitive.|||Hi,
I need to convert all datetime columns to smalldatetime in the whole database.
I'd suggest scripting out the entire database, including tables, sprocs, functions, constraints, everything, into a single DDL file. Search and replace on the DDL file to change your datatype. Execute the script to create a new database. Use DTS/SSIS/Export Data Wizard to transfer data from old database to new database.|||WARNING, WARNING, WARNING!!!

Using this script without having a current resume, multiple good database backups, and a good understanding of what this script is going to do is DANGEROUS!-- ptp 20071108 See http://www.dbforums.com/showthread.php?t=1624169

DECLARE
@.cCmd NVARCHAR(2000)
, @.cColumn sysname
, @.cNull NVARCHAR(10)
, @.cTable sysname

DECLARE zFullDates CURSOR FOR SELECT
TABLE_NAME, COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 'datetime' = DATA_TYPE

OPEN zFullDates

FETCH zFullDates INTO @.cTable, @.cColumn, @.cNull

WHILE 0 = @.@.fetch_status
BEGIN
SET @.cCmd = 'ALTER TABLE ' + QuoteName(@.cTable)
+ ' ALTER COLUMN ' + QuoteName(@.cColumn)
+ ' SMALLDATETIME '
+ CASE WHEN N'No' = @.cNull THEN 'NOT NULL' ELSE 'NULL' END

EXECUTE (@.cCmd)
FETCH zFullDates INTO @.cTable, @.cColumn, @.cNull
END

CLOSE zFullDates

DEALLOCATE zFullDates-PatP|||No need to worry about the values because we're setting up a new db on SQL Server 2005.
Changing the program is not possible.

Thanks for the script. I added the support for schemas, but unfortunately the server won't let me change the column when another object depends on it, like a constraint.

I guess the easiest way would be to do what blindman suggested. Script the whole db to a DDL file, find & replace, recreate the db.

Thanks|||RI Is goining to be a beach

Script the Table DDL From the constraints, do the ddl, the load, the apply the constraints|||Wow, that turned out pretty well, even without the group hug I would have thought necessary.|||he really should fix the code...if it's a third party vendor, then ask for your money back|||the third party vendor went out of business in 1977|||oh, sorry. The "Value add strategic partner", then|||Sounds like a new BE for a legacy FE. "Rewrite the FE" isn't really all that useful advice for the fella is it?
Blindman's solution is soup herb, particulalry with there being no data.|||Sounds like a new BE for a legacy FE. "Rewrite the FE" isn't really all that useful advice for the fella is it?
Blindman's solution is soup herb, particulalry with there being no data.

smoking crack again?|||smoking crack again?Heroin - it has a mellower taste.|||it haz a flavr (http://icanhascheezburger.com/?s=flavr)

:cool:

Convert char(24) to smalldatetime

I have a table that has a field with sample time that is set up as char(24).
I would like to write a SELECT statement converts char(24) to smalldatetime
format.
Table A
Counterdatetime char(24)
Please help me resolve this issue.
Thank You,What is the format of the data in that Counterdatetime column? There are
dozens of possible representations of a smalldatetime that fit in a char(24)
column...
Jacco Schalkwijk
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:67FB2AE9-F312-4274-B954-265098E50177@.microsoft.com...
> I have a table that has a field with sample time that is set up as
> char(24).
> I would like to write a SELECT statement converts char(24) to
> smalldatetime
> format.
> Table A
> Counterdatetime char(24)
> Please help me resolve this issue.
> Thank You,