September 22, 2013 at 2:57 pm
Hi,
I'm trying to insert random date in yyyy-mm format to have expiry Credit card date column.
Haw can I do it? is it possible at all?
I know that I can sore the full (yyyy-mm-dd) and then to select with: convert(char(7),column_a,121)
but this is not the way I'm looking for .
Thanks
September 22, 2013 at 3:35 pm
This might be what you are looking for:
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
Result:
2013/09
]
September 23, 2013 at 12:43 pm
If I want to select it's working but as I mentioned I'm trying to insert and because my column is a 'date type' when I'm using CONVERT(VARCHAR(7), GETDATE(), 111)-- AS [YYYY/MM]
I'm getting this error message:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Do u have any suggestions?
Thanks for trying to help
September 23, 2013 at 1:31 pm
In order to insert data into a date data type - you need a valid date or string that can be converted into a date.
If you are looking for random dates - then wouldn't you want random days also? Or are you looking for random months only? If random months only, then just pick a day - either the 1st or 15th would be my recommendation.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
September 23, 2013 at 1:47 pm
I don't have a problem to get a random month or a day I just using this :
UPDATE table_a
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')
But the column that I'm trying to populate is a 'credit card expiry date' and it need to be '2015/01' (yyyy/mm).
September 23, 2013 at 2:06 pm
golansimani (9/23/2013)
I don't have a problem to get a random month or a day I just using this :UPDATE table_a
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')
But the column that I'm trying to populate is a 'credit card expiry date' and it need to be '2015/01' (yyyy/mm).
Since your target column is not a datetime you can't use the results of DATEADD. That function returns a datetime. You would have to kludge this a bit more.
SET column_a = cast(year(DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')) as char(4))
+ '/'
+ right('0' + cast(month(DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')) as varchar(2)), 2)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 3:03 pm
Its not working , I got this message :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
September 23, 2013 at 3:10 pm
golansimani (9/23/2013)
Its not working , I got this message :Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
What is the datatype of column_a? I suspect that column is actually a datetime based on your message.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 23, 2013 at 3:16 pm
Its a Date Data Type
September 23, 2013 at 3:19 pm
golansimani (9/23/2013)
Its a Date Data Type
Then the code you posted should work fine.
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')
Remember there is not such thing as a format for date columns.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply